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

Question

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!

Answer 1

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)