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?

Answer 1

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

Answer 2

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?