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

Up vote 9 down vote favorite share g+ share fb share tw.

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! C# .net linq-to-sql linq-to-entities link|improve this question edited Feb 10 '11 at 12:03DoctaJonez6,47911936 asked Feb 10 '11 at 4:30Ian Davis1,0011517 84% accept rate.

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

1 It sucks, but it's the only way it seems =( – Francisco Dec 14 '11 at 15:45.

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

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.

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.

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.

Would doing: var t2 = db.tests. Where(x => x. NullableInt == nullableInt?

Null).ToList(); Work? It seems like utter madness though.

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.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.