Compare nullable types in Linq to Sql

Question

I have a Category entity which has a Nullable ParentId field. When the method below is executing and the categoryId is null, the result seems null however there are categories which has null ParentId value.

What is the problem in here, what am I missing?

public IEnumerable<ICategory> GetSubCategories(long? categoryId)
{
    var subCategories = this.Repository.Categories.Where(c => c.ParentId == categoryId)
        .ToList().Cast<ICategory>();

    return subCategories;
}

By the way, when I change the condition to (c.ParentId == null), result seems normal.

Answer 1

The first thing to do is to put on logging, to see what TSQL was generated; for example:

ctx.Log = Console.Out;

LINQ-to-SQL seems to treat nulls a little inconsistently (depending on literal vs value):

using(var ctx = new DataClasses2DataContext())
{
    ctx.Log = Console.Out;
    int? mgr = (int?)null; // redundant int? for comparison...
    // 23 rows:
    var bosses1 = ctx.Employees.Where(x => x.ReportsTo == (int?)null).ToList();
    // 0 rows:
    var bosses2 = ctx.Employees.Where(x => x.ReportsTo == mgr).ToList();
}

So all I can suggest is use the top form with nulls!

i.e.

Expression<Func<Category,bool>> predicate;
if(categoryId == null) {
    predicate = c=>c.ParentId == null;
} else {
    predicate = c=>c.ParentId == categoryId;
}
var subCategories = this.Repository.Categories
           .Where(predicate).ToList().Cast<ICategory>();

---

Update - I got it working "properly" using a custom Expression:

    static void Main()
    {
        ShowEmps(29); // 4 rows
        ShowEmps(null); // 23 rows
    }
    static void ShowEmps(int? manager)
    {
        using (var ctx = new DataClasses2DataContext())
        {
            ctx.Log = Console.Out;
            var emps = ctx.Employees.Where(x => x.ReportsTo, manager).ToList();
            Console.WriteLine(emps.Count);
        }
    }
    static IQueryable<T> Where<T, TValue>(
        this IQueryable<T> source,
        Expression<Func<T, TValue?>> selector,
        TValue? value) where TValue : struct
    {
        var param = Expression.Parameter(typeof (T), "x");
        var member = Expression.Invoke(selector, param);
        var body = Expression.Equal(
                member, Expression.Constant(value, typeof (TValue?)));
        var lambda = Expression.Lambda<Func<T,bool>>(body, param);
        return source.Where(lambda);
    }