How can i query for null values in entity framewor

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

Answer 1

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

Answer 2

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:

1. This could potentially break code that already depends on the existing behavior.
2. 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.

Answer 3

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.

Answer 4

It appears that Linq2Sql has this "problem" as well. It appears that there is a valid reason for this behavior due to whether ANSI NULLs are ON or OFF but it boggles the mind why a straight "== null" will in fact work as you'd expect.

Answer 5

Personnally, I prefer:

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

over

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

because it prevents repetition -- though that's not mathematically exact, but it fits well most cases.

Answer 6

I'm not able to comment divega's post, but among the different solutions presented here, divega's solution produces the best SQL. Both performance wise and length wise. I just checked with SQL Server Profiler and by looking at the execution plan (with "SET STATISTICS PROFILE ON").