SqlException about UNION, INTERSECT and EXCEPT

Question

Can someone help me with this exception? I do not understand what this means or how to fix it ... This is a SqlException with the following message:

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

I get it when I run a query in pseudocode that looks 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 parts of a.Union(b) , it starts without exception. Therefore, I know that there is a mistake. But why do I understand that? And how can I fix this? Am I something too crazy here? Didn't I understand how to use Union thing?

Basically, I have some objects that have a foreign key for some other object. And I need to get all entities that either have this foreign key set to null , or where this foreign object fulfills some requirements.

Answer 1

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

The SQL error is usually caused by the fact that the fields received for UNION do not match for the two queries. For example, if the first request can have 3 fields, but the second request has 4 fields, this error will occur. Thus, looking at the generated SQL will definitely help in this case.

Answer 2

Judging by the SQL error you quoted, you might run into the same problem as me. Basically, when Linq to SQL queries using the Concat or Union extension method in two different queries, an error is detected in Linq to SQL that optimizes each projection separately, regardless of the fact that the projection must remain unchanged to execute SQL Union.

Literature:

LINQ to SQL creates invalid TSQL when using UNION or CONCAT

Linq to SQL Union Same file_name generating error

If this is also your problem, I found a solution that works 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 3

Perhaps you can write it in one query?

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

There are also ways to merge expressions ( OrElse ), but this is not trivial.

Not sure where the error comes from!

edit: did not check it, but this should be logically equivalent to 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)); } 

Answer 4

query = a.Union (b);

It's a bad idea to mutate captured variables ... This is probably 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” wherever they become equal :)

Answer 5

I would call data.GetCommand(query) and DbCommand resulting DbCommand (especially the generated SQL string). This should make you understand what went wrong.

Projection is not happening anywhere, so I expect both goal lists to be the same.

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

This should be one of your Where calls that adds additional columns to your selection list.

Answer 6

Do you accidentally pass the value in the direction of the choice in the variable or do you return the same field more than once? An error appeared in SP1 in which it tries to "optimize" such things and can cause the merge request to break (due to the optimization of parts of the request) from different pass-in parameters.

If you publish your actual request, not a pseudo-code, this makes identification easier if that is the case.

(And the workaround, if so, is to materialize the individual parts first, and then make the connection on the client side (L2O)).

Answer 7

jpierson correctly posed a problem.
I also had a 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 subquery "SpecialField = 0" and "AnotherSpecialField = 0" was optimized, as a result, one field was used instead of two in the union, which obviously will not work. I had to modify the first query so that SpecialField and AnotherSpecialField had different values, as in the second subquery.

Answer 8

Ok, I had a problem 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 try UNION. If you compare the comparison with IEqualityComparer. Everything compiled fine, but crashed with unsupported overload. Well, I realized that the issue being discussed seemed to bring delayed execution. So I get the collections and put ToList (), then execute UNION, and all this is good. Not sure if this is useful, but it works for me.