.net 如何在实体框架中查询空值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/682429/
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
How can i query for null values in entity framework?
提问by Adrian Zanescu
I want to execute a query like this
我想执行这样的查询
var result = from entry in table
where entry.something == null
select entry;
and get an IS NULLgenerated.
并得到一个IS NULL生成的。
Edited: After the first two answers i feel the need to clarify that I'm using Entity Framework and not Linq to SQL. The object.Equals() method does not seem to work in EF.
编辑：在前两个答案之后，我觉得有必要澄清我使用的是实体框架而不是 Linq to SQL。object.Equals() 方法在 EF 中似乎不起作用。
Edit no.2:
The above query works as intended. It correctly generates IS NULL. My production code however was
编辑 2：上述查询按预期工作。它正确生成IS NULL. 然而，我的生产代码是
value = null;
var result = from entry in table
where entry.something == value
select entry;
and the generated SQL was something = @p; @p = NULL. It seems that EF correctly translates the constant expression but if a variable is involved it treats it just like a normal comparison. Makes sense actually. I'll close this question
生成的 SQL 是something = @p; @p = NULL. EF 似乎正确地转换了常量表达式，但如果涉及到一个变量，它会将其视为正常的比较。其实有道理。我会关闭这个问题
回答by BlueRaja - Danny Pflughoeft
Workaround for Linq-to-SQL:
Linq-to-SQL 的解决方法：
var result = from entry in table
where entry.something.Equals(value)
select entry;
Workaround for Linq-to-Entities (ouch!):
Linq-to-Entities 的解决方法（哎哟！）：
var result = from entry in table
where (value == null ? entry.something == null : entry.something == value)
select entry;
This is a nasty bug which has bitten me several times. If this bug has affected you too, please visit the bug report on UserVoiceand let Microsoft know that this bug has affected you as well.
这是一个讨厌的虫子，它已经咬了我好几次了。 如果此错误也影响了您，请访问UserVoice 上的错误报告，并让 Microsoft 知道此错误也影响了您。
Edit:This bug is being fixed in EF 4.5! Thanks everyone for upvoting this bug!
编辑：此错误正在 EF 4.5 中修复！感谢大家为这个错误点赞！
For backwards compatibility, it will be opt-in - you need manually enable a setting to make entry == valuework. No word yet on what this setting is. Stay tuned!
为了向后兼容，它将是可选的 - 您需要手动启用设置才能entry == value工作。目前还没有关于这个设置是什么的消息。敬请关注！
Edit 2:According to this postby the EF team, this issue has been fixed in EF6! Woohoo!
编辑 2：根据EF 团队的这篇文章，此问题已在 EF6 中修复！呜呼！
We changed the default behavior of EF6 to compensate for three-valued logic.
我们更改了 EF6 的默认行为以补偿三值逻辑。
This means that existing code that relies on the old behavior (null != null, but only when comparing to a variable)will either need to be changed to not rely on that behavior, or set UseCSharpNullComparisonBehaviorto false to use the old broken behavior.
这意味着依赖旧行为（null != null，但仅在与变量比较时）的现有代码要么需要更改为不依赖该行为，要么设置UseCSharpNullComparisonBehavior为 false 以使用旧的损坏行为。
回答by ITmeze
Since Entity Framework 5.0 you can use following code in order to solve your issue:
从 Entity Framework 5.0 开始，您可以使用以下代码来解决您的问题：
public abstract class YourContext : DbContext
{
public YourContext()
{
(this as IObjectContextAdapter).ObjectContext.ContextOptions.UseCSharpNullComparisonBehavior = true;
}
}
This should solve your problems as Entity Framerwork will use 'C# like' null comparison.
这应该可以解决您的问题，因为 Entity Framerwork 将使用“C# like”空比较。
回答by divega
There is a slightly simpler workaround that works with LINQ to Entities:
有一种适用于 LINQ to Entities 的稍微简单的解决方法：
var result = from entry in table
where entry.something == value || (value == null && entry.something == null)
select entry;
This works becasuse, as AZ noticed, LINQ to Entities special cases x == null (i.e. an equality comparison against the null constant) and translates it to x IS NULL.
正如 AZ 所注意到的，这是因为 LINQ to Entities 特殊情况 x == null（即与 null 常量的相等比较）并将其转换为 x IS NULL。
We are currently considering changing this behavior to introduce the compensating comparisons automatically if both sides of the equality are nullable. There are a couple of challenges though:
我们目前正在考虑更改此行为，以便在等式的两边都可以为空时自动引入补偿比较。但是有几个挑战：
- This could potentially break code that already depends on the existing behavior.
- The new translation could affect the performance of existing queries even when a null parameter is seldom used.
- 这可能会破坏已经依赖于现有行为的代码。
- 即使很少使用空参数，新的转换也可能影响现有查询的性能。
In any case, whether we get to work on this is going to depend greatly on the relative priority our customers assign to it. If you care about the issue, I encourage you to vote for it in our new Feature Suggestion site: https://data.uservoice.com.
在任何情况下，我们是否开始工作将在很大程度上取决于我们的客户分配给它的相对优先级。如果您关心这个问题，我鼓励您在我们的新功能建议网站上为它投票：https: //data.uservoice.com。
回答by Svish
If it is a nullable type, maybe try use the HasValue property?
如果它是可空类型，可以尝试使用 HasValue 属性吗？
var result = from entry in table
where !entry.something.HasValue
select entry;
Don't have any EF to test on here though... just a suggestion =)
不过这里没有任何 EF 可以测试...只是一个建议 =)
回答by Konstantin Tarkus
var result = from entry in table
where entry.something.Equals(null)
select entry;
MSDN Reference: LINQ to SQL: .NET Language-Integrated Query for Relational Data
MSDN 参考：LINQ to SQL：.NET Language-Integrated Query for Relational Data
回答by Oscar Cabrero
回答by drzaus
Pointing out that all of the Entity Framework < 6.0 suggestions generate some awkward SQL. See second example for "clean" fix.
指出所有 Entity Framework < 6.0 建议都会生成一些笨拙的 SQL。请参阅“干净”修复的第二个示例。
Ridiculous Workaround
荒谬的解决方法
// comparing against this...
Foo item = ...
return DataModel.Foos.FirstOrDefault(o =>
o.ProductID == item.ProductID
// ridiculous < EF 4.5 nullable comparison workaround http://stackoverflow.com/a/2541042/1037948
&& item.ProductStyleID.HasValue ? o.ProductStyleID == item.ProductStyleID : o.ProductStyleID == null
&& item.MountingID.HasValue ? o.MountingID == item.MountingID : o.MountingID == null
&& item.FrameID.HasValue ? o.FrameID == item.FrameID : o.FrameID == null
&& o.Width == w
&& o.Height == h
);
results in SQL like:
导致 SQL 如下：
SELECT TOP (1) [Extent1].[ID] AS [ID],
[Extent1].[Name] AS [Name],
[Extent1].[DisplayName] AS [DisplayName],
[Extent1].[ProductID] AS [ProductID],
[Extent1].[ProductStyleID] AS [ProductStyleID],
[Extent1].[MountingID] AS [MountingID],
[Extent1].[Width] AS [Width],
[Extent1].[Height] AS [Height],
[Extent1].[FrameID] AS [FrameID],
FROM [dbo].[Foos] AS [Extent1]
WHERE (CASE
WHEN (([Extent1].[ProductID] = 1 /* @p__linq__0 */)
AND (NULL /* @p__linq__1 */ IS NOT NULL)) THEN
CASE
WHEN ([Extent1].[ProductStyleID] = NULL /* @p__linq__2 */) THEN cast(1 as bit)
WHEN ([Extent1].[ProductStyleID] <> NULL /* @p__linq__2 */) THEN cast(0 as bit)
END
WHEN (([Extent1].[ProductStyleID] IS NULL)
AND (2 /* @p__linq__3 */ IS NOT NULL)) THEN
CASE
WHEN ([Extent1].[MountingID] = 2 /* @p__linq__4 */) THEN cast(1 as bit)
WHEN ([Extent1].[MountingID] <> 2 /* @p__linq__4 */) THEN cast(0 as bit)
END
WHEN (([Extent1].[MountingID] IS NULL)
AND (NULL /* @p__linq__5 */ IS NOT NULL)) THEN
CASE
WHEN ([Extent1].[FrameID] = NULL /* @p__linq__6 */) THEN cast(1 as bit)
WHEN ([Extent1].[FrameID] <> NULL /* @p__linq__6 */) THEN cast(0 as bit)
END
WHEN (([Extent1].[FrameID] IS NULL)
AND ([Extent1].[Width] = 20 /* @p__linq__7 */)
AND ([Extent1].[Height] = 16 /* @p__linq__8 */)) THEN cast(1 as bit)
WHEN (NOT (([Extent1].[FrameID] IS NULL)
AND ([Extent1].[Width] = 20 /* @p__linq__7 */)
AND ([Extent1].[Height] = 16 /* @p__linq__8 */))) THEN cast(0 as bit)
END) = 1
Outrageous Workaround
令人发指的解决方法
If you want to generate cleaner SQL, something like:
如果你想生成更干净的 SQL，比如：
// outrageous < EF 4.5 nullable comparison workaround http://stackoverflow.com/a/2541042/1037948
Expression<Func<Foo, bool>> filterProductStyle, filterMounting, filterFrame;
if(item.ProductStyleID.HasValue) filterProductStyle = o => o.ProductStyleID == item.ProductStyleID;
else filterProductStyle = o => o.ProductStyleID == null;
if (item.MountingID.HasValue) filterMounting = o => o.MountingID == item.MountingID;
else filterMounting = o => o.MountingID == null;
if (item.FrameID.HasValue) filterFrame = o => o.FrameID == item.FrameID;
else filterFrame = o => o.FrameID == null;
return DataModel.Foos.Where(o =>
o.ProductID == item.ProductID
&& o.Width == w
&& o.Height == h
)
// continue the outrageous workaround for proper sql
.Where(filterProductStyle)
.Where(filterMounting)
.Where(filterFrame)
.FirstOrDefault()
;
results in what you wanted in the first place:
结果是你首先想要的：
SELECT TOP (1) [Extent1].[ID] AS [ID],
[Extent1].[Name] AS [Name],
[Extent1].[DisplayName] AS [DisplayName],
[Extent1].[ProductID] AS [ProductID],
[Extent1].[ProductStyleID] AS [ProductStyleID],
[Extent1].[MountingID] AS [MountingID],
[Extent1].[Width] AS [Width],
[Extent1].[Height] AS [Height],
[Extent1].[FrameID] AS [FrameID],
FROM [dbo].[Foos] AS [Extent1]
WHERE ([Extent1].[ProductID] = 1 /* @p__linq__0 */)
AND ([Extent1].[Width] = 16 /* @p__linq__1 */)
AND ([Extent1].[Height] = 20 /* @p__linq__2 */)
AND ([Extent1].[ProductStyleID] IS NULL)
AND ([Extent1].[MountingID] = 2 /* @p__linq__3 */)
AND ([Extent1].[FrameID] IS NULL)
回答by Adrian Zanescu
var result = from entry in table
where entry.something == null
select entry;
The above query works as intended. It correctly generates IS NULL. My production code however was
上述查询按预期工作。它正确生成 IS NULL。然而，我的生产代码是
var value = null;
var result = from entry in table
where entry.something == value
select entry;
and the generated SQL was something = @p; @p = NULL. It seems that EF correctly translates the constant expression but if a variable is involved it treats it just like a normal comparison. Makes sense actually.
生成的 SQL 是 something = @p; @p = NULL。EF 似乎正确地转换了常量表达式，但如果涉及到一个变量，它会将其视为正常的比较。其实有道理。
回答by Vincent Courcelle
Personnally, I prefer:
就个人而言，我更喜欢：
var result = from entry in table
where (entry.something??0)==(value??0)
select entry;
over
超过
var result = from entry in table
where (value == null ? entry.something == null : entry.something == value)
select entry;
because it prevents repetition -- though that's not mathematically exact, but it fits well most cases.
因为它可以防止重复——尽管这在数学上并不准确，但它适用于大多数情况。
回答by JasonCoder
It appears that Linq2Sql has this "problem" as well. It appears that there is a valid reason for this behavior due to whether ANSI NULLs are ON or OFF but it boggles the mind why a straight "== null" will in fact work as you'd expect.
看起来 Linq2Sql 也有这个“问题”。由于 ANSI NULL 是 ON 还是 OFF，这种行为似乎是有正当理由的，但令人难以置信的是，为什么直接的“== null”实际上会按您的预期工作。
