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 you prefer using method (lambda) syntax as I do, you could do the same thing like this:

var result = new TableName();

using(var db = new EFObjectContext)
{
    var query = db.TableName;

    query = value1 == null 
        ? query.Where(tbl => tbl.entry1 == null) 
        : query.Where(tbl => tbl.entry1 == value1);

    query = value2 == null 
        ? query.Where(tbl => tbl.entry2 == null) 
        : query.Where(tbl => tbl.entry2 == value2);

    result = query
        .Select(tbl => tbl)
        .FirstOrDefault();

   // Inspect the value of the trace variable below to see the sql generated by EF
   var trace = ((ObjectQuery<REF_EQUIPMENT>) query).ToTraceString();

}

return result;

Answer 2

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

use that

Answer 3

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.

Answer 4

Unfortunately in Entity Framework 5 DbContext the issue is still not fixed.

I used this workaround (works with MSSQL 2012 but ANSI NULLS setting might be deprecated in any future MSSQL version).

public class Context : DbContext
{

    public Context()
        : base("name=Context")
    {
        this.Database.Connection.StateChange += Connection_StateChange;
    }

    void Connection_StateChange(object sender, System.Data.StateChangeEventArgs e)
    {
        // Set ANSI_NULLS OFF when any connection is opened. This is needed because of a bug in Entity Framework
        // that is not fixed in EF 5 when using DbContext.
        if (e.CurrentState == System.Data.ConnectionState.Open)
        {
            var connection = (System.Data.Common.DbConnection)sender;
            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = "SET ANSI_NULLS OFF";
                cmd.ExecuteNonQuery();
            }
        }
    }
}

It should be noted that it is a dirty workaround but it is one that can be implemented very quickly and works for all queries.

Answer 5

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

The above query works as intended. It correctly generates IS NULL. My production code however was

var 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.

Answer 6

to deal with Null Comparisons use Object.Equals() instead of ==

check this reference