Union (or Concat, etc..) with Constant values and projection

I've discovered a very nasty gotcha with Linq-to-sql, and i'm not sure what the best solution is.

If you take a simple L2S Union statement, and include L2S code in one side, and constants in the other, then the constants do not get included in the SQL Union and are only projected into the output after the SQL, resulting in SQL errors about the number of columns not mathching for the union.

As an example:

(from d in dc.mytable where foo == "bar" select new {First = d.Foo, Second = d.Roo})
.Union(from e in dc.mytable where foo == "roo" select new {First= "", Second = e.Roo})

This will generate an error "All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

This is particularly insidious (and maddening) because there are obviously the same number of expressions in the list, but when you look at the SQL, you will notice that it does not generate a column for "First" in the second half of the Union. This is because "First" is inserted into the projection AFTER the query.

Ok, the easy solution is to just convert each part into Enumerables or Lists or something and then do the union in memory rather than SQL, and that's fine if you're dealing with a small amount of data. However, if you're working with a large set of data, which you then plan to further filter (in sql) before returning it this is not ideal.

I guess what i'm looking for is a way to force L2S to include the column in the SQL. Is that possible?

开发者_如何学编程UPDATE:

While not an exact duplicate, this error is similar to This Question and has similar solutions. So i'm closing, but not deleting this question because it may help someone else come to posible solutions from a different way.

Unfortunately, L2S is too smart of it's own good sometimes.

I've decided that the only real solution is to use a stored proc. Hope this helps.

This is a bug in the Linq2SQL provider. In LinqPad you can clearly see the bug.

(from d in dc.mytable where foo == "bar" select new {First = d.Foo, Second = d.Roo})  
.Union(from e in dc.mytable where foo == "roo" select new {First= "", Second = e.Roo}) 

Will server side produce something like this:

SELECT [t2].[Foo], [t2].[Roo]
FROM (
    SELECT [t0].[Foo], @p0 AS [value]
    FROM [dc].[Mytable] AS [t0]
    UNION ALL
    SELECT [t1].[Foo], [t1].[Roo]
    FROM [dc].[Mytable] AS [t1]
    ) AS [t2]

This will be a problem because the union will name the second column "value" instead of "Roo", which will cause the outer query to fail.

If you, however, switch the order of the two tables

(from e in dc.mytable where foo == "roo" select new {First= "", Second = e.Roo})  
.Union(from d in dc.mytable where foo == "bar" select new {First = d.Foo, Second = d.Roo}) 

So that the constant assignment within the generated T-SQL comes in the non-first table, then things may work because T-SQL ignores the column names of subsequent tables.

Note: The first table in a union decides both column name and type. So would be smart to get LinqPad anyway.