How can I query for null values in entity framework?

.net, ado.net, entity-framework

Solution

Workaround for Linq-to-SQL:

var result = from entry in table
             where entry.something.Equals(value)
             select entry;

Workaround for Linq-to-Entities (ouch!):

var result = from entry in table
             where (value == null ? entry.something == null : entry.something == value)
             select entry;

This is a nasty bug which has bitten me several times. If this bug has affected you too, please visit the bug report on UserVoice and let Microsoft know that this bug has affected you as well.

Edit: This bug is being fixed in EF 4.5! Thanks everyone for upvoting this bug!

For backwards compatibility, it will be opt-in - you need manually enable a setting to make `entry == value` work. No word yet on what this setting is. Stay tuned!

Edit 2: According to this post by the EF team, this issue has been fixed in EF6! Woohoo!

We changed the default behavior of EF6 to compensate for three-valued logic.

This means that existing code that relies on the old behavior (`null != null`, but only when comparing to a variable) will either need to be changed to not rely on that behavior, or set `UseCSharpNullComparisonBehavior` to false to use the old broken behavior.

Problem

I want to execute a query like this ``` var result = from entry in table where entry.something == null select entry; ``` and get an `IS NULL` generated. Edited: After the first two answers i feel the need to clarify that I'm using Entity Framework and not Linq to SQL. The object.Equals() method does not seem to work in EF. Edit no.2: The above query works as intended. It correctly generates `IS NULL`. My production code however was ``` value = null; var result = from entry in table where entry.something == value select entry; ``` and the generated SQL was `something = @p; @p = NULL`. It seems that EF correctly translates the constant expression but if a variable is involved it treats it just like a normal comparison. Makes sense actually. I will close this question.

Original source