current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 11 down vote favorite

I have a table called "test", which only has 1 column, "NullableInt" (nullable int type)

The records are: 1, 2, null

int? nullableInt = null;
var t = db.tests.Where(x => x.NullableInt == null).ToList(); // returns 1 record
var t2 = db.tests.Where(x => x.NullableInt == nullableInt).ToList(); // returns 0 records

For some reason, t2 returns 0 records, even tho it's using "nullableInt" variable, which has a value of null, just like t, which is comparing against "null"

Any help would be greatly appreciated!

share|improve this question

5 Answers 5

up vote 2 down vote accepted

Queries could be built in this way:

var q = db.tests;
if(nullableInt.HasValue)
{
   q = q.Where(x => x.NullableInt == nullableInt.Value);
}
else
{
   q = q.Where(x => x.NullableInt == null);
}
var t2 = q.ToList();
share|improve this answer
    
+1 It sucks, but it's the only way it seems =( –  Francisco Dec 14 '11 at 15:45
    
See my answer below, this is fixed in EF6 and you can opt in to a fix in EF5. –  Rowan Miller Jun 12 '13 at 18:15
up vote 6 down vote

Yep - it's a bug in LINQ-to-SQL / Entity Framework. IS NULL queries will only be generated if you hardcode null into the query, instead of a variable that happens to currently be null.

The second query will generate 

SELECT .......
WHERE NullableInt == @someParam
WHERE @someParam is null.

Where the first will generate the appropriate IS NULL in the WHERE clause.

If you're using LINQ-to-SQL, you can log your queries to Console.Out to see for yourself, and if you're using EF, then ToTraceString() should show you the same info (or SQL Server profiler)

share|improve this answer
2  
Actually I don't think it's a bug because in the second expression nullableInt is not null even you assign its value with nullableInt = null(since it's a struct,whose value can't be null). Thus the framework treats it like other structs(such as an int). –  Danny Chen Feb 10 '11 at 5:20
2  
@Danny, that's splitting hairs. The bug could be in the specification, even if the code does exactly what the specification says it should do. In other words, the bug here might not be that the code happens to do something wrong, but that someone didn't consider this scenario. –  Lasse V. Karlsen Feb 10 '11 at 9:39
1  
Splitting hairs indeed. There's no justifiable reason why the EF parser should generate the second query to be anything other than IS NULL. And this has been logged as a bug with MS (with a very high vote count) though I don't have that link at hand. –  Adam Rackis Feb 10 '11 at 14:41
up vote 2 down vote

tl;dr

If you use DbContext in EF6 this is fixed.

If you're using EF5 (or ObjectContext in EF6) you need to set ObjectContext.ContextOptions.UseCSharpNullComparisonBehavior to true. To do that on DbContext use this:

((IObjectContextAdapter)db).ObjectContext.ContextOptions.UseCSharpNullComparisonBehavior = true;

.

More details

The root cause of this issue is a difference in how the database compares null values and how C# compares null values. Because you write your query in C# you want to use the semantics of C#.

In EF5 we introduced ObjectContext.ContextOptions.UseCSharpNullComparisonBehavior, which allowed you to opt in to using C# semantics instead of database semantics. The default is false (so that existing queries don't magically start returning different results when you upgrade to EF5). But you can set it to true and both your queries will return rows.

If you are using DbContext in EF5 you need to drop down to the ObjectContext to set it:

((IObjectContextAdapter)db).ObjectContext.ContextOptions.UseCSharpNullComparisonBehavior = true;

If you are using EF6, then it's already set to true on DbContext so you are good to go. We decided this causes so much confusion it was worth taking the potential impact on existing queries.

share|improve this answer
    
Thanks Rowan, this was very helpful. –  Eric Sep 13 '13 at 21:57
up vote 0 down vote

There is another solution that will always work, albeit with a small caveat:

int? nullableInt = null;
var t2 = db.tests.Where(x => object.Equals(x.NullableInt, nullableInt)).ToList();

When the value is null you will get the proper IS NULL query, however when its not null you will get something like:

SELECT ...
WHERE ([t0].[NullableInt] IS NOT NULL) AND ([t0].[NullableInt] = @p0)

Obviously it has a condition extra (the source of which is kind of puzzling). That being said, SQL Server's query optimizer should detect that, since @p0 is a non-null value, the first condition is a superset and will cut the where clause.

share|improve this answer
    
That may work in LINQ-to-SQL (I haven't tested it) but it definitely does not work in EF4. It still generates the same old = @param where @param is set to null –  Adam Rackis Feb 10 '11 at 14:37
    
Ah, so they "fixed" it ;). Yes, in L2S it works. –  mmix Feb 10 '11 at 15:51
up vote 0 down vote

Would doing:

var t2 = db.tests.Where(x => x.NullableInt == nullableInt ?? null).ToList();

Work?

It seems like utter madness though.

share|improve this answer
    
Madness indeed, and no, it doesn't work. I tried putting in the generated SQl, but it keeps failing - I'm guessing it's protecting against some sort of injection attack –  Adam Rackis Feb 10 '11 at 14:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.