All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists
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 开发者_如何学JAVAerror.
How should I form it
Thanks in advance
Regards Hema
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
SqlException about UNION, INTERSECT and EXCEPT
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...
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.
精彩评论