Linq中的比较可空类型到SQL [英] Compare nullable types in Linq to Sql

问题描述

我有其中有一个可为空的ParentId字段类别的实体。当下面的方法被执行和类标识符是已空，但有类别的结果似乎空具有空的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到SQL似乎把空一点不一致（根据字面VS值）：

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

---

---

更新 - 我得到它的工作正常使用自定义的防爆pression ：

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

这篇关于Linq中的比较可空类型到SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！