Boog Boog - 10 months ago 44
C# Question

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

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 Source

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.