Linq where column == (null reference) not the same as column == null

Question

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

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

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?

Answer 1

Change where (s.crmc_Retail_Trade_Id == tradeId) to

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

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

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

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.

Answer 2

Another option to solve this, as I ran across this problem as well.

where (tradeId == null ? s.crmc_Retail_Trade_Id == null : s.crmc_Retail_Trade_Id == tradeId)

Answer 3

Not sure on this one, but I suspect when linq-to-sql translates that to an sql query string you get a slightly different expression specifying null directly such that at some point you end up comparing NULL to itself, and NULL=NULL is defined to be false.