c# - Linq where column == (null reference) 与 column == null 不同

Question

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

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，

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

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

Answer 1

更改 其中 (s.crmc_Retail_Trade_Id == tradeId)到

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

编辑 - 基于 this post由 Brant Lamborn 撰写，看起来以下内容可以满足您的要求:

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

Null Semantics (LINQ to SQL) MSDN 页面链接到一些有趣的信息:

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.

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

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