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

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?

Answer 2

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

http://stackoverflow.com/questions/586097/compare-nullable-types-in-linq-to-sql

Answer 3

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)
                   select new { mt.NAME };