Linq where column ==(空引用)与 column == null 不同

问题描述

我在 linq-to-sql 中遇到了一个相当奇怪的问题.在下面的例子中，

I came across a rather strange problem with linq-to-sql. In the following example,

var survey = (from s in dbContext.crmc_Surveys
                                   where (s.crmc_Retail_Trade_Id == tradeId) && (s.State_.Equals(state))
                                   select s).First();

如果 tradeId 为 null，则它不会表现得好像我已经像这样专门指定了 null，

If tradeId is null, it doesn't behave as if I had specified null specifically like this instead,

var survey = (from s in dbContext.crmc_Surveys
                                   where (s.crmc_Retail_Trade_Id == null) && (s.State_.Equals(state))
                                   select s).First();

这是我想要的行为.事实上，除非两个值都不为空，否则它不会返回任何内容.我不知道如何完成这几个不同的 linq 查询.有什么想法吗?

Which is my desired behavior. In fact it doesn't return anything unless both values are non-null. I can't figure out how to accomplish this short of several different linq queries. Any ideas?

推荐答案

Change where (s.crmc_Retail_Trade_Id == tradeId)

where (s.crmc_Retail_Trade_Id == tradeId || 
      (tradeId == null && s.crmc_Retail_Trade_Id == null))

编辑 - 基于 这篇文章 由 Brant Lamborn 撰写，看起来以下内容可以满足您的需求:

Edit - based on this post by Brant Lamborn, it looks like the following would do what you want:

where (object.Equals(s.crmc_Retail_Trade_Id, tradeId))

空语义(LINQ to SQL) MSDN 页面链接到一些有趣的信息:

The Null Semantics (LINQ to SQL) MSDN page links to some interesting info:

LINQ to SQL 不会强加 C# null 或Visual Basic 没什么比较SQL 上的语义.比较运算符在句法上被翻译成他们的SQL 等效项.语义反映由服务器定义的 SQL 语义或连接设置.两个空值在默认情况下被认为是不平等的SQL Server 设置(虽然您可以更改设置以更改语义).无论如何，LINQ to SQL不考虑服务器设置查询翻译.

LINQ to SQL does not impose C# null or Visual Basic nothing comparison semantics on SQL. Comparison operators are syntactically translated to their SQL equivalents. The semantics reflect SQL semantics as defined by server or connection settings. Two null values are considered unequal under default SQL Server settings (although you can change the settings to change the semantics). Regardless, LINQ to SQL does not consider server settings in query translation.

与文字 null 的比较(nothing) 被翻译成适当的 SQL 版本(为 null 或为不为空).

A comparison with the literal null (nothing) is translated to the appropriate SQL version (is null or is not null).

null(无)中的值排序规则由 SQL Server 定义；LINQ to SQL 不会改变整理.

The value of null (nothing) in collation is defined by SQL Server; LINQ to SQL does not change the collation.