By Adrian Zanescu


2009-03-25 16:46:24 8 Comments

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

14 comments

@divega 2010-11-24 01:08:41

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.

@John Meyer 2016-05-04 17:17:15

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;

@drzaus 2015-01-15 22:34:56

Pointing out that all of the Entity Framework < 6.0 suggestions generate some awkward SQL. See second example for "clean" fix.

Ridiculous Workaround

// comparing against this...
Foo item = ...

return DataModel.Foos.FirstOrDefault(o =>
    o.ProductID == item.ProductID
    // ridiculous < EF 4.5 nullable comparison workaround http://stackoverflow.com/a/2541042/1037948
    && item.ProductStyleID.HasValue ? o.ProductStyleID == item.ProductStyleID : o.ProductStyleID == null
    && item.MountingID.HasValue ? o.MountingID == item.MountingID : o.MountingID == null
    && item.FrameID.HasValue ? o.FrameID == item.FrameID : o.FrameID == null
    && o.Width == w
    && o.Height == h
    );

results in SQL like:

SELECT TOP (1) [Extent1].[ID]                 AS [ID],
       [Extent1].[Name]               AS [Name],
       [Extent1].[DisplayName]        AS [DisplayName],
       [Extent1].[ProductID]          AS [ProductID],
       [Extent1].[ProductStyleID]     AS [ProductStyleID],
       [Extent1].[MountingID]         AS [MountingID],
       [Extent1].[Width]              AS [Width],
       [Extent1].[Height]             AS [Height],
       [Extent1].[FrameID]            AS [FrameID],
FROM   [dbo].[Foos] AS [Extent1]
WHERE  (CASE
  WHEN (([Extent1].[ProductID] = 1 /* @p__linq__0 */)
        AND (NULL /* @p__linq__1 */ IS NOT NULL)) THEN
    CASE
      WHEN ([Extent1].[ProductStyleID] = NULL /* @p__linq__2 */) THEN cast(1 as bit)
      WHEN ([Extent1].[ProductStyleID] <> NULL /* @p__linq__2 */) THEN cast(0 as bit)
    END
  WHEN (([Extent1].[ProductStyleID] IS NULL)
        AND (2 /* @p__linq__3 */ IS NOT NULL)) THEN
    CASE
      WHEN ([Extent1].[MountingID] = 2 /* @p__linq__4 */) THEN cast(1 as bit)
      WHEN ([Extent1].[MountingID] <> 2 /* @p__linq__4 */) THEN cast(0 as bit)
    END
  WHEN (([Extent1].[MountingID] IS NULL)
        AND (NULL /* @p__linq__5 */ IS NOT NULL)) THEN
    CASE
      WHEN ([Extent1].[FrameID] = NULL /* @p__linq__6 */) THEN cast(1 as bit)
      WHEN ([Extent1].[FrameID] <> NULL /* @p__linq__6 */) THEN cast(0 as bit)
    END
  WHEN (([Extent1].[FrameID] IS NULL)
        AND ([Extent1].[Width] = 20 /* @p__linq__7 */)
        AND ([Extent1].[Height] = 16 /* @p__linq__8 */)) THEN cast(1 as bit)
  WHEN (NOT (([Extent1].[FrameID] IS NULL)
             AND ([Extent1].[Width] = 20 /* @p__linq__7 */)
             AND ([Extent1].[Height] = 16 /* @p__linq__8 */))) THEN cast(0 as bit)
END) = 1

Outrageous Workaround

If you want to generate cleaner SQL, something like:

// outrageous < EF 4.5 nullable comparison workaround http://stackoverflow.com/a/2541042/1037948
Expression<Func<Foo, bool>> filterProductStyle, filterMounting, filterFrame;
if(item.ProductStyleID.HasValue) filterProductStyle = o => o.ProductStyleID == item.ProductStyleID;
else filterProductStyle = o => o.ProductStyleID == null;

if (item.MountingID.HasValue) filterMounting = o => o.MountingID == item.MountingID;
else filterMounting = o => o.MountingID == null;

if (item.FrameID.HasValue) filterFrame = o => o.FrameID == item.FrameID;
else filterFrame = o => o.FrameID == null;

return DataModel.Foos.Where(o =>
    o.ProductID == item.ProductID
    && o.Width == w
    && o.Height == h
    )
    // continue the outrageous workaround for proper sql
    .Where(filterProductStyle)
    .Where(filterMounting)
    .Where(filterFrame)
    .FirstOrDefault()
    ;

results in what you wanted in the first place:

SELECT TOP (1) [Extent1].[ID]                 AS [ID],
           [Extent1].[Name]               AS [Name],
           [Extent1].[DisplayName]        AS [DisplayName],
           [Extent1].[ProductID]          AS [ProductID],
           [Extent1].[ProductStyleID]     AS [ProductStyleID],
           [Extent1].[MountingID]         AS [MountingID],
           [Extent1].[Width]              AS [Width],
           [Extent1].[Height]             AS [Height],
           [Extent1].[FrameID]            AS [FrameID],
FROM   [dbo].[Foos] AS [Extent1]
WHERE  ([Extent1].[ProductID] = 1 /* @p__linq__0 */)
   AND ([Extent1].[Width] = 16 /* @p__linq__1 */)
   AND ([Extent1].[Height] = 20 /* @p__linq__2 */)
   AND ([Extent1].[ProductStyleID] IS NULL)
   AND ([Extent1].[MountingID] = 2 /* @p__linq__3 */)
   AND ([Extent1].[FrameID] IS NULL)

@Burak Tamtürk 2016-06-22 03:20:46

The code running on the SQL will be cleaner and faster but EF will generate & cache new query plan for every combination before send it to sql server, which makes it slower than other workarounds.

@BlueRaja - Danny Pflughoeft 2010-03-29 20:28:41

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.

@noobish 2010-10-06 19:36:13

I've voted up the bug report. Hopefully they fix this. I can't say that I really remember this bug being present in vs2010 beta...

@David 2011-08-01 18:00:54

oh come on microsoft... really?!?!? In version 4.1?!?! +1

@Michael Stum 2011-10-31 01:37:16

That Linq-To-SQL workaround doesn't seem to work (trying with a Guid?). Using the Entities-Workaround works in L2S, but generates horrendous SQL. I had to do an if-statement in code (var result = from ...; if(value.HasValue) result = result.Where(e => e.something == value) else result = result.Where(e => e.something == null);

@Michael Stum 2011-10-31 01:38:56

Object.Equals works actually (where Object.Equals(entry.something,value))

@leen3o 2012-07-12 18:47:56

I'm using EF v5.0RC and its not been fixed!! stackoverflow.com/questions/11458514/…

@BlueRaja - Danny Pflughoeft 2012-07-12 19:28:47

@leen3o: I haven't tried it yet, but there is supposedly supposed to be an option to enable C#-like null comparisons. Search around, and if you find the option (in the entity-context maybe?), please post back here and I will update my answer :) If not, I will update it when I get time to look into it.

@Steven Pena 2012-09-12 08:38:19

This doesn't always work in EF. You will get an Unable to cast non-edm primitive types error. It will work if the constant value you are comparing against is null. But if that constant has a value, it will error on the attempt to cast the data to an Object.

@BlueRaja - Danny Pflughoeft 2012-09-12 08:49:57

@Steven: Do you mean the "Workaround for Linq-to-Entities," above, or the Object.Equals method? I wasn't even aware that had been edited into the answer...

@Steven Pena 2012-09-21 21:38:46

Yes, the workaround described in the edited answer for Linq-to-Entities doesn't always work.

@BlueRaja - Danny Pflughoeft 2012-09-21 22:39:58

@StevenPena: Thank you, I've removed it.

@Oskar Sjöberg 2012-11-14 21:31:41

I don't see this fixed in EF5, also I cannot find any sign of any option to enable this 'feature'.

@BlueRaja - Danny Pflughoeft 2012-11-14 21:50:46

@OskarSjöberg: I haven't used EF 4.5 yet, but is this it?

@Oskar Sjöberg 2012-11-15 22:51:05

@BlueRaja-DannyPflughoeft: I have downloaded EF5 with nuget in VS2010, but I cannot access that property from my DbContext. It is simply not there. Any clue?

@Shaul says I Support Monica 2013-01-27 14:53:20

@leen3o (or anyone else) - Has anyone yet found where this alleged fix is in EF 4.5/5.0? I'm using 5.0 and it's still misbehaving.

@ITmeze 2013-03-19 15:38:55

Take a look at answer stackoverflow.com/a/15503871/57728

@David Hammond 2015-05-01 18:13:01

I'm not sure when this was fixed, but it happened for me in EntityFramework 6.0.2. I just updated to 6.1.3 and it is fixed.

@ITmeze 2013-03-19 15:37:39

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.

@Knaģis 2012-11-28 14:25:03

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.

@Triynko 2015-10-05 19:30:21

This will immediately cease to function once ANSI NULLS is permanently set to ON in a future version of SQL Server, in case the warning wasn't clear.

@Vincent Courcelle 2011-09-23 15:07:20

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.

@Andrew 2011-02-04 08:30:42

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

use that

@Michael Stum 2011-10-31 01:54:13

That's VERY wrong because it will select all entries where the value matches AND all entries where something is null, even if you ask for a value.

@Buginator 2010-12-07 19:06:04

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").

@Adrian Zanescu 2009-12-24 10:24:03

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.

@JasonCoder 2009-05-20 05:33:03

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.

@Oscar Cabrero 2009-03-25 16:58:52

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

check this reference

@Adrian Zanescu 2009-03-26 11:05:11

thanks but i use EF not LINQ2SQL.

@Michael Stum 2011-10-31 01:54:52

This works perfectly in Linq-To-Sql and also generates the proper SQL (some other answers here generate horrendous SQL or wrong results).

@Svish 2009-03-26 11:33:32

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

@Dave Cousineau 2013-04-19 22:31:41

Well... this only works if you're just looking for nulls, but then using == null doesn't get hit by the bug anyway. The point is to be filtering by the value of a variable, whose value might be null, and have the null value find the null records.

@Reuel Ribeiro 2017-12-11 16:43:23

Your answer saved me. I forgot to use a nullable type on my entity model class and couldn't make (x => x.Column == null) to work. :)

@Konstantin Tarkus 2009-03-25 16:52:44

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

Related Questions

Sponsored Content

28 Answered Questions

[SOLVED] Fastest Way of Inserting in Entity Framework

10 Answered Questions

[SOLVED] Using MySQL with Entity Framework

5 Answered Questions

11 Answered Questions

[SOLVED] How can I get Id of inserted entity in Entity framework?

8 Answered Questions

[SOLVED] How to loop through all enum values in C#?

18 Answered Questions

[SOLVED] How do I view the SQL generated by the Entity Framework?

16 Answered Questions

[SOLVED] Entity Framework vs LINQ to SQL

33 Answered Questions

4 Answered Questions

[SOLVED] Entity Framework and Connection Pooling

1 Answered Questions

Sponsored Content