I just got the following error when trying to UNION two LINQ to
SQL queries:
Could not translate expression
'Table(EventMembership).......blah blah blah...' into SQL and could
not treat it as a local expression.
To see what was going on I ToList() the two queries individually
and pasted the generated output into SQL Server Management Studio
and then tried a UNION ALL on the two queries.
Guess what? The SQL blew up!
All queries combined using a UNION, INTERSECT
or EXCEPT operator must have an equal number of expressions in
their target lists.
But the shapes of my LINQ classes are identical...whats
going on? Why is the translated SQL dropping some of the columns
and causing the shapes of the two queries to be different?
I then noticed that the second query was referencing the same
column twice...something like:
select new { FirstName = e.EventName, LastName
= e.EventName }
Now, when LINQ to SQL translates to TSQL it spots the identical
columns and thinks to itself "ok, I could be smart here" and
decides to only pull back one of the columns and fixes it
clientside. This is good for performance but NOT when you want to
UNION the result sets!!! Presumably, LINQ to SQL should
check first if a UNION operator is be used and if so then makes
sure the shape of the data for all queries involved is the
same...but it looks as if we are going to have to live with this
bug for now.
So, to get round the problem you are going to have to ensure
that each property of your select object is referencing a
different column or atleast do something that will force LINQ to
SQL to include it in its translated SELECT list.