LINQ到SQL时where子句对比较NULL值查询不返回行 [英] LINQ-to-SQL query not returning row when where clause compares against NULL value

问题描述

让我们考虑与2列的表：ID（int）和角色（字符串）。 。两者都是可为空

Lets consider a table with 2 columns: ID (int) and Role(string). Both are nullable.

现在假设在两列中的数据是：

Now assume that the data in the two columns is:

ID     Role
--     ----
 1     NULL
 2     Admin

查询看起来是这样的：

List<types> t1 = (
    from a in datacontext.RoleTable 
    where a.Role != "Admin"
    select a
).ToList();

我想它的角色列不等于上述查询应该返回表的第一个记录。到管理，但查询将返回一个空列表

I thought the above query should be returning the first record of the table as its Role column is not equal to 'Admin' but the query returns an empty list.

现在，当我使用这个查询：

Now when I use this query:

List<types> t2 = (
    from a in datacontext.RoleType 
    where a.Role != "Admin" && a.Role == DBNull.Value.ToString() 
    select a
).ToList();

我得到正确的答案。

I get the correct answer.

谁能告诉我，为什么第一个查询不工作，请。

Can anybody tell me why the first query is not working please.

FYI：如果在表中的第一行中的角色列更改为用户而不是 NULL 那么第一个查询工作正常。

FYI: If the Role column in the first row in the table is changed to User instead of NULL then the first query works fine.

我使用SQL Express和LINQ到SQL。

I am using SQL Express and LINQ to SQL.

推荐答案

第一个查询并不像预期的那样，因为它转换成SQL，它等效于以下内容：

The first query doesn't behave as expected, because it is translated into SQL that is equivalent to the following:

select * from RoleTable where Role != 'Admin'

现在，在SQL NULL！=管理员是的不的 TRUE （也不是 FALSE - 这是不确定的）。结果
这就是众多案例之一，其中的抽象，LINQ 。到SQL提供的是漏水的，你还需要知道SQL

Now, in SQL NULL != 'Admin' is not TRUE (nor is it FALSE - it is undefined).
That's one of the many cases where the abstraction that LINQ to SQL provides is leaky and you still need to know SQL.

BTW：你的第二个查询也是不正确的，这将仅选择空。它不会选择与角色的行用户

BTW: Your second query is also incorrect, it will select only those rows that are null. It wouldn't select a row with the role 'User'.

正确的查询应该是这样的：

The correct query would look like this:

List<types> t2 = 
    (from a in datacontext.RoleTable 
     where a.Role != "Admin" || a.Role == null 
     select a).ToList();

这篇关于LINQ到SQL时where子句对比较NULL值查询不返回行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！