C# 比较 Linq 和 Sql 中的可为空类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/586097/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Compare nullable types in Linq to Sql
提问by Ali Ers?z
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.
我有一个 Category 实体，它有一个 Nullable ParentId 字段。当下面的方法正在执行并且 categoryId 为空时，结果似乎为空，但是有些类别的 ParentId 值为空。
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.
顺便说一句，当我将条件更改为 (c.ParentId == null) 时，结果似乎很正常。
采纳答案by Marc Gravell
The first thing to do is to put on logging, to see what TSQL was generated; for example:
首先要做的就是挂上日志，看看生成了什么TSQL；例如：
ctx.Log = Console.Out;
LINQ-to-SQL seems to treat nulls a little inconsistently (depending on literal vs value):
LINQ-to-SQL 似乎对空值的处理有点不一致（取决于文字与值）：
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.
IE
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:
更新 - 我使用自定义让它“正常”工作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);
}
回答by Eric Petroelje
My guess is that it's due to a rather common attribute of DBMS's - Just because two things are both null does not mean they are equal.
我的猜测是，这是由于 DBMS 的一个相当普遍的属性 - 仅仅因为两件事都是 null 并不意味着它们相等。
To elaborate a bit, try executing these two queries:
为了详细说明，请尝试执行以下两个查询：
SELECT * FROM TABLE WHERE field = NULL
SELECT * FROM TABLE WHERE field IS NULL
The reason for the "IS NULL" construct is that in the DBMS world, NULL != NULL since the meaning of NULL is that the value is undefined. Since NULL means undefined, you can't say that two null values are equal, since by definition you don't know what they are.
“IS NULL”构造的原因是在 DBMS 世界中，NULL != NULL 因为 NULL 的含义是值未定义。由于 NULL 表示未定义，因此您不能说两个空值相等，因为根据定义，您不知道它们是什么。
When you explicitly check for "field == NULL", LINQ probably converts that to "field IS NULL". But when you use a variable, I'm guessing that LINQ doesn't automatically do that conversion.
当您明确检查“field == NULL”时，LINQ 可能会将其转换为“field IS NULL”。但是当你使用一个变量时，我猜 LINQ 不会自动进行这种转换。
Here's an MSDN forum postwith more info about this issue.
这是 MSDN 论坛帖子，其中包含有关此问题的更多信息。
Looks like a good "cheat" is to change your lambda to look like this:
看起来不错的“作弊”是将您的 lambda 更改为如下所示：
c => c.ParentId.Equals(categoryId)
回答by Ryan Versaw
What about something simpler like this?
像这样更简单的东西怎么样？
public IEnumerable<ICategory> GetSubCategories(long? categoryId)
{
var subCategories = this.Repository.Categories.Where(c => (!categoryId.HasValue && c.ParentId == null) || c.ParentId == categoryId)
.ToList().Cast<ICategory>();
return subCategories;
}
回答by ariel
Other way:
另一种方式：
Where object.Equals(c.ParentId, categoryId)
or
或者
Where (categoryId == null ? c.ParentId == null : c.ParentId == categoryId)
回答by algreat
You need to use operator Equals:
您需要使用运算符等于：
var subCategories = this.Repository.Categories.Where(c => c.ParentId.Equals(categoryId))
.ToList().Cast<ICategory>();
Equalsfot nullable types returns trueif:
在以下情况下等于可空类型返回true：
- The HasValue property is false, and the other parameter is null. That is, two null values are equal by definition.
- The HasValue property is true, and the value returned by the Value property is equal to the other parameter.
- HasValue 属性为 false，另一个参数为 null。也就是说，根据定义，两个空值是相等的。
- HasValue 属性为真，Value 属性返回的值等于另一个参数。
and returns falseif:
并在以下情况下返回false：
- The HasValue property for the current Nullable structure is true, and the other parameter is null.
- The HasValue property for the current Nullable structure is false, and the other parameter is not null.
- The HasValue property for the current Nullable structure is true, and the value returned by the Value property is not equal to the other parameter.
- 当前 Nullable 结构的 HasValue 属性为 true，另一个参数为 null。
- 当前 Nullable 结构的 HasValue 属性为 false，另一个参数不为 null。
- 当前 Nullable 结构的 HasValue 属性为 true，并且 Value 属性返回的值不等于其他参数。
More info here Nullable<.T>.Equals Method
更多信息在这里Nullable<.T>.Equals 方法
回答by Ji?í Herník
Or you can simply use this. It will also translate to a nicer sql query
或者你可以简单地使用它。它还将转换为更好的 sql 查询
Where((!categoryId.hasValue && !c.ParentId.HasValue) || c.ParentId == categoryId)
回答by Kevbo
Linq to Entities supports Null Coelescing (??) so just convert the null on the fly to a default value.
Linq to Entities 支持 Null Coelescing (??)，因此只需将 null 动态转换为默认值即可。
Where(c => c.ParentId == categoryId ?? 0)
