How can I query for null values in entity framework?

AZ_35128 0
Viewed 135899

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.

ado.net entity-framework .net
14 Answers

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

Svish_393210
user_826860

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

check this reference

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

AZ_351280

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_1097360 
var result = from entry in table    
             where entry.something == value||entry.something == null                   
              select entry;

use that

user_6028450
Related