How can I query for null values in 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 will close this question

User · Answer

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

User · Answer

Pointing out that all of the Entity Framework  lt  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   gt      o ProductID    item ProductID        ridiculous  lt  EF 4 5 nullable comparison workaround http   stackoverflow com a 2541042 1037948      amp  amp  item ProductStyleID HasValue   o ProductStyleID    item ProductStyleID   o ProductStyleID    null      amp  amp  item MountingID HasValue   o MountingID    item MountingID   o MountingID    null      amp  amp  item FrameID HasValue   o FrameID    item FrameID   o FrameID    null      amp  amp  o Width    w      amp  amp  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   lt  gt  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   lt  gt  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   lt  gt  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  lt  EF 4 5 nullable comparison workaround http   stackoverflow com a 2541042 1037948 Expression lt Func lt Foo  bool gt  gt  filterProductStyle  filterMounting  filterFrame  if item ProductStyleID HasValue  filterProductStyle   o   gt  o ProductStyleID    item ProductStyleID  else filterProductStyle   o   gt  o ProductStyleID    null   if  item MountingID HasValue  filterMounting   o   gt  o MountingID    item MountingID  else filterMounting   o   gt  o MountingID    null   if  item FrameID HasValue  filterFrame   o   gt  o FrameID    item FrameID  else filterFrame   o   gt  o FrameID    null   return DataModel Foos Where o   gt      o ProductID    item ProductID      amp  amp  o Width    w      amp  amp  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

User · 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

User · Answer

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

User · Answer

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

User · Answer

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   gt  tbl entry1    null             query Where tbl   gt  tbl entry1    value1        query   value2    null            query Where tbl   gt  tbl entry2    null             query Where tbl   gt  tbl entry2    value2        result   query          Select tbl   gt  tbl           FirstOrDefault           Inspect the value of the trace variable below to see the sql generated by EF    var trace     ObjectQuery lt REF EQUIPMENT gt   query  ToTraceString        return result

User · Answer

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

User · 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

User · Answer

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

User · Answer

to deal with Null Comparisons use Object Equals   instead of     check this reference

User · Answer

var result   from entry in table                  where entry something    value  entry something    null                                  select entry    use that

User · Answer

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

User · Answer

There is a slightly simpler workaround that works with LINQ to Entities   var result   from entry in table          where entry something    value     value    null  amp  amp  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

User · Answer

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

[.net] How can I query for null values in entity framework?

Examples related to .net

Examples related to entity-framework

Examples related to ado.net