LINQ查询适用于空，但不是int？在where子句 [英] Linq query works with null but not int? in where clause

问题描述

我有这样一个LINQ查询功能（简体）：

I have a linq query function like (simplified):

public IList<Document> ListDocuments(int? parentID)
{
    return (
        from doc in dbContext.Documents
        where doc.ParentID == parentID
        select new Document
        {
            ID = doc.ID,
            ParentID = doc.ParentID,
            Name = doc.SomeOtherVar
        }).ToList();
}

现在由于某些原因，当我在空通为的parentID（目前仅有与空parentIDs数据）和我没有得到任何结果。

Now for some reason when I pass in null for the parentID (currently only have data with null parentIDs) and I get no results.

我复制并粘贴此查询到LinqPad和运行以下命令：

I copy and paste this query into LinqPad and run the following:

from doc in dbContext.Documents
where doc.ParentID == null
select doc

我回来的结果集如预期...

I get back a result set as expected...

实际上查询左连接的等加入但我已删除他们，对其进行了测试，并得到同样的结果使联接不影响任何东西。该应用程序和LinqPad都连接到同一数据库，以及

The actually query has left join's and other joins but I have removed them and tested it and get the same result so the joins are not affecting anything. The app and LinqPad are both connected to the same DB as well.

编辑：在机应用查询测试只用'空'，它返回结果如预期这样的问题是使用空VS INT？我已经更新的问题，使之更加有用的其他有同样问题，找到这个主题。

Tested with just 'null' in the appliction query and it returns the result as expected so the issue is using null vs int?. I have updated question to make it more useful to others with the same issue to find this thread.

推荐答案

文字无效值比可能为空参数的处理不同。当你明确地测试反对空，生成的SQL会使用 IS NULL 运营商，但是当你使用的是参数将使用标准的 = 运营商，这意味着没有行会在SQL，因为匹配空不等于任何东西。这是在LINQ to SQL更恼人的.NET / SQL语义不匹配之一。要解决它，你可以像使用一个条款：

Literal null values are handled differently than parameters which could be null. When you explicitly test against null, the generated SQL will use the IS NULL operator, but when you're using a parameter it will use the standard = operator, meaning that no row will match because in SQL null isn't equal to anything. This is one of the more annoying .NET/SQL semantic mismatches in LINQ to SQL. To work around it, you can use a clause like:

where doc.ParentID == parentID || (doc.ParentID == null && parentID == null)

这篇关于LINQ查询适用于空，但不是int？在where子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！