实体框架的奇怪问题(直接为null与包含null的变量) [英] Strange problem with entity framework (null directly versus variable containing null)

问题描述

大家好，
我在处理实体框架时遇到了一个奇怪的问题.
这个问题以前从来没有发生过，所以我感到很惊讶.

关于AgencyId的所有内容，实际上为null.
我在第一行中设置agencyEqualsNull的地方对此进行了验证(这是真的).​​

Hi all,
I have a strange problem dealing with Entity Framework.
This problem never ever occured to me before, so I am kind of surprised.

It''s all about the AgencyId, which is in fact null.
I verify this where I set agencyEqualsNull in the first line (it is true).

// verify that AgencyId indeed is null
bool agencyEqualsNull = AgencyId == null;

// here I do not get a result -; test is null
tblTest test = context.tblTest.FirstOrDefault(w => 
w.couponId == Id
&&
w.couponCode == Code
&&
w.customerId == CustomerId
&&
w.agencyId == AgencyId
);

if (test != null)
{
    // we never get here
}

// here I do get a result - test is not null
tblTest test2 = context.tblTest.FirstOrDefault(w => 
w.couponId == Id
&&
w.couponCode == Code
&&
w.customerId == CustomerId
&&
w.agencyId == null
);

if (test2 != null)
{
    //we get here
}

现在，对于第一次尝试，我收到test为空，对于第二次尝试，我收到了test2的值(不为null).
唯一的区别是我不传递变量(实际上是null)，而是直接传递null ...

我会忽略什么吗?我认为查询都是相等的，应该返回一个值...

//更新
我启动了SQL Server Profiler，以了解情况
这是查询:

Now for the first try I receive null for test, for the second try I recieve a value for test2 (not null).
The only difference is that I do not pass the variable (which is in fact null) but null directly...

Do I overlook something? I ssumed the queries are both equal and should return a value...

// UPDATE
I fired up the SQL Server Profiler in order to enlight the situation
here is the query:

exec sp_executesql N'SELECT TOP (1) 
[Extent1].[id] AS [id], 
[Extent1].[code] AS [code], 
[Extent1].[customerId] AS [customerId], 
[Extent1].[employeeId] AS [employeeId],
FROM [dbo].[tblTest] AS [Extent1]
WHERE 
([Extent1].[Id] = @p__linq__0) AND 
([Extent1].[Code] = @p__linq__1) AND 
([Extent1].[customerId] = @p__linq__2) AND 
([Extent1].[employeeId] = @p__linq__3)',
N'@p__linq__0 bigint,
@p__linq__1 nvarchar(4000),
@p__linq__2 bigint,
@p__linq__3 bigint',
@p__linq__0=82,
@p__linq__1=N'ADVENT2012-CU88888',
@p__linq__2=88888,
@p__linq__3=NULL

在最后一行中，您可以看到@p__linq__3=NULL
如果我直接通过null，它将解析为

In the last line you can see @p__linq__3=NULL
If I pass null directly it resolves to

([Extent1].[employeeId] IS NULL)

代替

instead of

([Extent1].[employeeId] = @p__linq__3)

其中

where

@p__linq__3=NULL

现在我感到更加困惑，因为我真的假设如果我直接传递一个包含null或null的变量，生成的查询将是相同的.

//更新
我发现这可以解决问题:
改变

Now I am even more puzzled because I really assumed the generated query would be the same if I pass a variable containing null or null directly...

// UPDATE
I figured out that this will do the trick:
change

w.agencyId == AgencyId

到

to

(AgencyId == null) ? w.agencyId == null : w.agencyId == AgencyId

我以某种方式认为这将自动完成...
这实际上不是我的代码，现在我有点担心，我必须在完整的代码库中检查可能与此问题有关的所有内容...

有人对此有建议吗?


任何帮助，感激不尽，
最好的问候
Andy

I somehow assumed that this would be done automatically...
This is actually not my code and now I am kind of afraid that I have to check everything that might be related to this problem in the complete codebase...

Does anybody has a suggestion regarding this one?


Any help is kindly appreciated,
best regards
Andy

推荐答案

我认为行为是设计使然的，对于所有null值，需要分别进行比较.或者您在SQL Server上将ansi null设置为off，这将允许您比较Null值，例如AgencyID = null.

I assume behavior is by design, for all null values, they need to be compared separately. or you have another option is set ansi null off on sql server which will allow you to compare Null values like AgencyID = null.

SET ANSI_NULLS OFF
GO

这篇关于实体框架的奇怪问题(直接为null与包含null的变量)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！