How can I query for null values in entity framework?

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.

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.

Since Entity Framework 5.0 you can use following code in order to solve your issue:

public abstract class YourContext : DbContext
{
  public YourContext()
  {
    (this as IObjectContextAdapter).ObjectContext.ContextOptions.UseCSharpNullComparisonBehavior = true;
  }
}

This should solve your problems as Entity Framerwork will use 'C# like' null comparison.

There is a slightly simpler workaround that works with LINQ to Entities:

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

This works becasuse, as AZ noticed, LINQ to Entities special cases x == null (i.e. an equality comparison against the null constant) and translates it to x IS NULL.

We are currently considering changing this behavior to introduce the compensating comparisons automatically if both sides of the equality are nullable. There are a couple of challenges though:

This could potentially break code that already depends on the existing behavior.
The new translation could affect the performance of existing queries even when a null parameter is seldom used.

In any case, whether we get to work on this is going to depend greatly on the relative priority our customers assign to it. If you care about the issue, I encourage you to vote for it in our new Feature Suggestion site: https://data.uservoice.com.

If it is a nullable type, maybe try use the HasValue property?

var result = from entry in table
                 where !entry.something.HasValue
                 select entry;

Don't have any EF to test on here though... just a suggestion =)

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

MSDN Reference: LINQ to SQL: .NET Language-Integrated Query for Relational Data

How can I query for null values in entity framework?

Related

Recent Posts