rioting bits

Let Category be a class/table, with a nullable int field named ParentId, to help me keep an hierarchy of categories.

And the following method, which returns an enumeration of all the categories having the given ParentId:

public static IEnumerable<Category> Enumerate(int? parentId)
{
    RiotingDataContext dataContext = new RiotingDataContext();

    return dataContext.Categories.Where(c => c.ParentId == parentId);
}

Now, this works as expected… right until you pass a parentId which is null. At that moment, the returned sequence is empty, although there are categories in the database that have a null ParentId.

I ran into this more than once and, needless to say, you are quite surprised when you see this for the first time. What to do? Of course, a quick check with SQL Profiler, to see what the hell Linq-to-SQL is doing underneath.

exec sp_executesql N'SELECT [t0].[Id], [t0].[Name], [t0].[ParentId]
FROM [dbo].[Categories] AS [t0]
WHERE [t0].[ParentId] = @p0',N'@p0 int',@p0=NULL

The problem is that the parameterized stored procedure that is generated checks if our nullable int equals null by doing ParentId = NULL, when the right way to do it (SQL-style) is ParentId IS NULL. So the code we would want to be generated, when ParentId is null, is:

SELECT [t0].[Id], [t0].[Name], [t0].[ParentId]
FROM [dbo].[Categories] AS [t0]
WHERE [t0].[ParentId] IS NULL

, and is the result we will get if we modify our method like this:

public static IEnumerable<Category> Enumerate(int? parentId)
{
    RiotingDataContext dataContext = new RiotingDataContext();

    if (parentId != null)
        return dataContext.Categories.Where(c => c.ParentId == parentId);
    else
        return dataContext.Categories.Where(c => c.ParentId == null);
}

Or, in a more compact manner (and the way I like to use it):

public static IEnumerable<Category> Enumerate(int? parentId)
{
    RiotingDataContext dataContext = new RiotingDataContext();

    return dataContext.Categories.Where(c => parentId != null ? c.ParentId == parentId : c.ParentId == null);
}