比较 Linq 和 Sql 中的可为空类型

问题描述

我有一个 Category 实体，它有一个 Nullable ParentId 字段.当下面的方法正在执行并且 categoryId 为空时，结果看起来为空，但有一些类别的 ParentId 值为空.

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;
}

顺便说一下，当我将条件更改为 (c.ParentId == null) 时，结果似乎正常.

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

推荐答案

首先要做的就是挂上日志，看看TSQL生成了什么；例如:

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 似乎对空值的处理有点不一致(取决于文字与值):

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!

即

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>();

<小时>

更新 - 我使用自定义 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);
    }