In entity framework, how can I query for null values?

.net ado.net entity-framework

Question

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'll close this question

1
109
11/29/2012 12:33:33 PM

Accepted Answer

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.

125
6/17/2013 6:59:28 AM

Popular Answer

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.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow