SqlException about UNION, INTERSECT and EXCEPT

Question

Could someone help me with this exception? I don't understand what it means or how to fix it... It is an SqlException with the following message:

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

I get it when running a query in pseudo code looking like this:

// Some filtering of data
var query = data.Subjects
            .Where(has value)
            .Where(has other value among some set of values);

// More filtering, where I need to have two different options
var a = query
            .Where(some foreign key is null);
var b = query
            .Where(some foreign key is not null)
            .Where(and that foreign key has a property which is what I want);
query = a.Union(b);

// Final filter and then get result as a list
var list = query
            .Where(last requirement)
            .ToList();

If I remove the a.Union(b) parts, it runs without the exception. So I know the error is there. But why do I get it? And how can I fix it? Am I doing something too crazy here? Have I misunderstood how to use the Union thing?

Basically what I have is some entities which have a foreign key to some other entity. And I need to get all the entities which either have that foreign key set to null or where that foreign entity fulfills some requirements.

Answer 1

Judging from the SQL error you listed you may be experiencing the same issue I was. Basically when Linq to SQL queries that use the Concat or Union extension method on two different queries it appears that there is a bug in Linq to SQL which optimizes each projection separately without regard to the fact that the projection must stay the same in order to accomplish the SQL Union.

References:

LINQ to SQL produces incorrect TSQL when using UNION or CONCAT

Linq to SQL Union Same Fieldname generating Error

If this happens to be your problem as well I've found a solution that is working for me as shown below.

var queryA = 
    from a in context.TableA
    select new 
    {
        id,
        name,
        onlyInTableA,
    }

var queryB = 
    from b in context.TableB
    let onlyInTableA = default(string)
    select new 
    {
        id,
        name,
        onlyInTableA,
    }

var results = queryA.Union(queryB).ToList();