关于 UNION、INTERSECT 和 EXCEPT 的 SqlException [英] SqlException about UNION, INTERSECT and EXCEPT

问题描述

有人可以帮我解决这个例外吗?我不明白这意味着什么或如何修复它......这是一个带有以下消息的 SqlException:

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:

使用 UNION、INTERSECT 或 EXCEPT 运算符组合的所有查询在其目标列表中必须具有相同数量的表达式.

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

如果我删除 a.Union(b) 部分，它会毫无例外地运行.所以我知道错误在那里.但为什么我会得到它?我该如何解决?我在这里做的事情太疯狂了吗?我是否误解了如何使用 Union 的东西?

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?

基本上我所拥有的是一些实体，它们具有指向其他实体的外键.我需要获取所有实体，这些实体要么将该外键设置为 null，要么该外键满足某些要求.

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.

推荐答案

由于这看起来像是生成的 SQL 的问题，您应该尝试使用 SQL Profiler，或者使用 DebuggerWritter 类的此代码 将 SQL 写入 Visual Studio 中的输出窗口.

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.

SQL 错误通常是由为 UNION 检索的字段与 2 个查询不同造成的.例如，如果第一个查询可能有 3 个字段，但第二个查询有 4 个字段，则会出现此错误.因此，在这种情况下，查看生成的 SQL 肯定会有所帮助.

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.

这篇关于关于 UNION、INTERSECT 和 EXCEPT 的 SqlException的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！