All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists

Question

Hi, I have a linq to sql query where I have to perform union two set of records. And I do not have equal number of fields, so added the null

eg my psuedo code is

var Values=( from c in containers

                   some joins
                   select new PValues

{
 regionid=r.regionid,

  roomid=r.roomid,

  floorid=r.floorid,

  maxarea=r1.maxarea,

  minarea=r1.minarea,

  avgarea=r1.avgarea,

  maxheight=r1.maxheight,

  minheight=r1.minheight

})
.union

( from c in containers

                   some joins

                   select new PValues

{ regionid=j.regionid,

  roomid=j.roomid,

  floorid=j.floorid,

  maxarea=null,

  minarea=null,

  avgarea=null,

  maxheight=j1.maxheight,

  minheight=j1.minheight

})

after googling some hours I came to understand that it is bug in 3.5 framework.

Now I want to retrieve the result. How do I do that I tried framing into two seperate iqueryable

var a= first query

var b =second query

ilist result =a.union b

This too results in the same error.

How should I form it

Thanks in advance

Regards Hema

Answer 1

In SQL, this typically means that you need to cast those nulls as the appropriate data type of the first part of the union, so make them the default values for the types you need, like "" or String.Empty for string values, or 0 for ints, etc...

Answer 2

Cast the nulls as mentioned by @GalacticJello, but also cast the first query's parameters that will have nulls in the other query to a nullable version of the type. For instance, if minarea is decimal, cast it to a decimal?; you can do this via: new Nullable(i.minarea) to ensure it infers the correct type.

It's probably inferring a different anonymous signature for both queries. Also, to mitigate the problems, make a class with these properties and change the query from:

select new { .. }

to

select new MyObj { .. }

And that will resolve it too.

Answer 3

This is most likely the result of a known issue with LINQ to SQL. When a column value is referenced twice, it gets optimized out of the result set (even though you may need it to make unions line up).

The most general-purpose work-around is to use let statements:

var Values=(
    from c in containers
    some joins
    //You'll need one let statement for each COLUMN, even if they share a value.
    let maxAreaValue = null
    let minAreaValue = null
    let avgAreaValue = null
    select new PValues
    {
        regionid=j.regionid,
        roomid=j.roomid,
        floorid=j.floorid,
        maxarea=maxAreaValue,
        minarea=minAreaValue,
        avgarea=avgAreaValue,
        maxheight=j1.maxheight,
        minheight=j1.minheight
    });

More information:
https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=355734
http://slodge.blogspot.com/2009/01/linq-to-sql-and-union-all-queries.html
http://stackoverflow.com/questions/650847/linq2sql-help-with-sqlexception-about-union-intersect-and-except/1171407#1171407