How to match a NULL column using LINQtoSQL and lambda?

Question

Hey there SO,

I'm having a heckuva time trying to match rows where a column is null. I know that in SQL, I have to use the IS keyword to find null columns:

SELECT * FROM Categories WHERE ParentCategoryID IS NULL;

I'm trying to recreate the above query in LINQtoSQL. I've tried:

var RootCats = categoriesRepository.Categories
    .Where(c => c.ParentCategoryID == null);

But this doesn't return any records at all. I have found some posts that use a different syntax than what I'm used to in this post here. But I was having difficulty putting that principle into Lambda form.

How do I use LINQtoSQL and Lambda expressions to find rows with null columns?

Edit

Per question in the comments - ParentCategoryID is an int.

Answer 1

To explictly try to force a SQL IS NULL, try something like this,

int? foo=null;
var RootCats = categoriesRepository.Categories
    .Where(c => c.ParentCategoryID == foo );

In my test code, LINQPad shows the resultant SQL as: WHERE [t0].[ParentID] IS NULL

Answer 2

Per Jesper's questioning I made Category.ParentCategoryID a nullable int and all works well!

Answer 3

If the ParentCategoryID in your model isn't nullable then it will get the default value of 0 and 0 != null. Open the designer and change the ParentCategoryID so that it is nullable.

Answer 4

I've recently discovered that using object.equals(myobj, null) works for this, and can be included in the lambda itself so is rather neat.