Linq-to-sql and nullable values

Yesterday I came across a bug with Linq-To-Sql I hadn't seen before. I tried to filter on a nullable column and when the value was null it failed

I was creating a function where I had a hierarchy and wanted to select all items with a certain parent. In one case I wanted to select all items at the top level, with parentID == null, but for some reason my query wasn't returning anything even though I knew there where items.

When I run into problems like this I always start SQL Profiler to investigate the query created. It turns out the generated SQL looked like this.

Now if you are used to C# that looks just fine. I have been fooled by this before though an remebered that the correct way to filter on NULL in SQL is the IS NULL check so I realised that filtering on a nullable columns was not working as I expected. The code that generated this SQL was:

public IEnumerable<string> GetUnavailableNames(int? pageID)
{
    return this.pageRepository.GetSavedPagesQuery().Where(p => p.ParentID == pageID).Select(p => p.Name);
}

Remember that ParentID is a nullable int too so I would have expected this to work. But seeing the results I realised it was something wrong with that. The workaround I came up with is slightly uglier but whatever it takes, right?

public IEnumerable<string> GetUnavailableNames(int? pageID)
{
    var names = this.pageRepository.GetSavedPagesQuery();
    names = pageID.HasValue ? names.Where(p => p.ParentID == pageID.Value) : names.Where(p => p.ParentID == null);
    return names.Select(p => p.Name);
}

Comparing those two methods I think the last one looks like [Insert ugly word here] but on the other hand the generated SQL when  pageID == null is

Mission completed! It came with a cost because I certainly prefered my original code but now it returned the first level items.