LINQ returns 0 results if using nullable int variable, accurate results if using "null"

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 "null开发者_如何学运维ableInt" variable, which has a value of null, just like t, which is comparing against "null"

Any help would be greatly appreciated!

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)

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.

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();

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.

Would doing:

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

Work?

It seems like utter madness though.