LINQ to SQL - nullable types in where clause

Question

I have a table with a column that has null values... when I try to query for records where that column IS NULL:

THIS WORKS:



        var list = from mt in db.MY_TABLE
                   where mt.PARENT_KEY == null
                   select new { mt.NAME };

THIS DOES NOT:



        int? id = null;
        var list = from mt in db.MY_TABLE
                   where mt.PARENT_KEY == id
                   select new { mt.NAME };

Why?

Have you tried looking at the SQL generated in each case? What's the type of mt.PARENT_KEY? — Jon Skeet, Jul 14 '09 at 15:24

score 20 · Accepted Answer · edited Aug 13 '14 at 14:51

after some more googling, I found the answer:

ref #1

ref #2

int? id = null;
var list = from mt in db.MY_TABLE
           where object.Equals(mt.PARENT_KEY, id)  //use object.Equals for nullable field
           select new { mt.NAME };

This LINQ renders to SQL as follows:

((mt.PARENT_KEY IS NULL) AND (@id IS NULL)) 
OR ((mt.PARENT_KEY IS NOT NULL) AND (@id IS NOT NULL) AND (mt.PARENT_KEY = @id))

score 5 · Answer 2 · answered Jul 14 '09 at 15:25

One possibility - if mt.PARENT_KEY is of some other type (e.g. long?) then there will be conversions involved.

It would help if you could show the types involved and the query generated in each case.

EDIT: I think I have an idea...

It could be because SQL and C# have different ideas of what equality means when it comes to null. Try this:

where (mt.PARENT_KEY == id) || (mt.PARENT_KEY == null && id == null)

If this is the case then it's a pretty ugly corner case, but I can understand why it's done that way... if the generated SQL is just using

WHERE PARENT_KEY = @value

then that won't work when value is null - it needs:

WHERE (PARENT_KEY = @value) OR (PARENT_KEY IS NULL AND @value IS NULL)

which is what the latter LINQ query should generate.

Out of interest, why are you selecting with

select new { mt.NAME }

instead of just

select mt.NAME

?) Why would you want a sequence of anonymous types instead of a sequence of strings (or whatever type NAME is?

I have updated Nick's answer to reflect how Object.Equals renders to SQL. In your opinion, is the extra `(mt.PARENT_KEY IS NOT NULL) AND (@id IS NOT NULL)` in Line 2 superfluous? This is suggested by the your answer. (More characters of LINQ, fewer characters of SQL) — Brian Webster, Jan 14 '12 at 21:32

score 0 · Answer 3 · edited May 23 '17 at 12:07

0

It's definitely a matter of C# and SQL having different notions of how to compare nulls - the question has been addressed here before:

Compare nullable types in Linq to Sql

edited May 23 '17 at 12:07

Community

1
1

answered Jul 14 '09 at 15:32

Dave

4,375
3
24
30

LINQ to SQL - nullable types in where clause

3 Answers3

Linked