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

Well I had an issue with this. Using Sql 08 i had two table functions that returned an int and a string in both cases. I created a complex object and used linq to attempt a UNION. Had an IEqualityComparer to do the comparision. All compiled fine, but crashed with a unsupported overload. Ok, i realised the problem discussed seemed to smack of defered execution. So i get the collections, and place ToList(), then do the UNION and it is all good. Not sure if this is helpful, but it works for me

Answer 2

jpierson has the problem summarised correctly.
I also had the problem, this time caused by some literals in the select statement:
Dim results = (From t in TestDataContext.Table1 _
Where t.ID = WantedID _
Select t.name, SpecialField = 0, AnotherSpecialField = 0, t.Address).Union _
From t in TestDataContext.Table1 _
Where t.SecondID = WantedSecondID _
Select t.name, SpecialField = 1, AnotherSpecialField = 0, t.Address)

The first sub-query of "SpecialField = 0" and the "AnotherSpecialField = 0" were optimised, resulting in one field instead of two being used in the union, which will obviously fail.
I had to change the first query so that the SpecialField & AnotherSpecialField had different values, much like in the second sub-query.

Answer 3

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();

Answer 4

Since this looks like a problem with the generated SQL, you should try to use either an SQL Profiler, or use this code for DebuggerWritter class to write the SQL to your Output Window in Visual Studio.

The SQL error is normally caused by the fields retrieved for UNION is not the same for the 2 queries. For example, if the first query might have 3 fields, but the second query has 4 fields, this error will occur. So, seeing the generated SQL will definitely help in this case.

Answer 5

Are you by any chance passing in a value to the 'select' side in a variable, or are you returning the same field more than once? SP1 introduced a bug where it tries to 'optimize' out such things and that can cause union queries to break (due to the query parts 'optimizing' out different passed-in params).

If you post your actual query rather than pseudo code it makes it easier to identify if this is the case.

(And a workaround if this is the case is to materialize the individual parts first and then do a client-side (L2O) union).

Answer 6

I would call data.GetCommand(query) and analyze the resulting DbCommand (especially the generated SQL string). That should give you a clue to what goes wrong.

There is no projection going on anywhere so I would expect both target lists to be the same.

You could try to reduce your query to a smaller one that still doesn't work. Start with query.Union(query) (this should at least work). Than add your Where calls one by one to see when it stops working.

It must be one of your Where calls that adds extra columns to your select list.

Answer 7

query = a.Union(b);

Not a good idea to mutate captured variables... Likely the cause of the error.

UPDATE: ok not

Here is another idea. The hint is in the error message.

var a = query
         .Where(some foreign key is null)
         .Select(x => x);

Or play by adding another 'fake' Where till they do become equal :)

Answer 8

Can you perhaps write it in a single query?

.Where(row => row.ForeignKey == null || row.ForeignKey.SomeCondition);

There are also ways of merging expressions (OrElse), but that isn't trivial.

Not sure where the error comes from, though!

edit: haven't tested it, but this should be logically equivalent to a UNION:

public static IQueryable<T> WhereAnyOf<T>(
    this IQueryable<T> source,
    params Expression<Func<T, bool>>[] predicates)
{
    if (source == null) throw new ArgumentNullException("source");
    if (predicates == null) throw new ArgumentNullException("predicates");
    if (predicates.Length == 0) return source.Where(row => false);
    if (predicates.Length == 1) return source.Where(predicates[0]);

    var param = Expression.Parameter(typeof(T), "row");
    Expression body = Expression.Invoke(predicates[0], param);
    for (int i = 1; i < predicates.Length; i++)
    {
        body = Expression.OrElse(body,
            Expression.Invoke(predicates[i], param));
    }
    return source.Where(Expression.Lambda<Func<T, bool>>(body, param));
}