如何在实体框架中查询空值?

我想执行这样一个查询

   var result = from entry in table
where entry.something == null
select entry;

然后生成一个 IS NULL

编辑: 在前两个答案之后,我觉得有必要澄清一下,我正在使用实体框架 而不是 Linq to SQL。 object. Equals ()方法似乎在 EF 中不起作用。

编辑第二条: 以上查询按预期工作。它正确地生成 IS NULL。但是我的生产代码是

value = null;
var result = from entry in table
where entry.something == value
select entry;

生成的 SQL 为 something = @p; @p = NULL。似乎 EF 正确地翻译了常量表达式,但是如果涉及到一个变量,它就把它当作一个正常的比较。有道理。我来结束这个问题。

138787 次浏览
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

to deal with Null Comparisons use Object.Equals() instead of ==

check this reference

If it is a nullable type, maybe try use the HasValue property?

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 =)

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.

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

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.

Workaround for Linq-to-SQL:

var result = from entry in table
where entry.something.Equals(value)
select entry;

Workaround for Linq-to-Entities (ouch!):

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 UserVoice and let Microsoft know that this bug has affected you as well.


Edit: This bug is being fixed in EF 4.5! Thanks everyone for upvoting this bug!

For backwards compatibility, it will be opt-in - you need manually enable a setting to make entry == value work. No word yet on what this setting is. Stay tuned!


Edit 2: According to this post by the EF team, this issue has been fixed in EF6! Woohoo!

We changed the default behavior of EF6 to compensate for three-valued logic.

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 UseCSharpNullComparisonBehavior to false to use the old broken behavior.

There is a slightly simpler workaround that works with 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.

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:

  1. This could potentially break code that already depends on the existing behavior.
  2. 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.

I'm not able to comment divega's post, but among the different solutions presented here, divega's solution produces the best SQL. Both performance wise and length wise. I just checked with SQL Server Profiler and by looking at the execution plan (with "SET STATISTICS PROFILE ON").

var result = from entry in table
where entry.something == value||entry.something == null
select entry;

use that

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.

Unfortunately in Entity Framework 5 DbContext the issue is still not fixed.

I used this workaround (works with MSSQL 2012 but ANSI NULLS setting might be deprecated in any future MSSQL version).

public class Context : DbContext
{


public Context()
: base("name=Context")
{
this.Database.Connection.StateChange += Connection_StateChange;
}


void Connection_StateChange(object sender, System.Data.StateChangeEventArgs e)
{
// Set ANSI_NULLS OFF when any connection is opened. This is needed because of a bug in Entity Framework
// that is not fixed in EF 5 when using DbContext.
if (e.CurrentState == System.Data.ConnectionState.Open)
{
var connection = (System.Data.Common.DbConnection)sender;
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = "SET ANSI_NULLS OFF";
cmd.ExecuteNonQuery();
}
}
}
}

It should be noted that it is a dirty workaround but it is one that can be implemented very quickly and works for all queries.

Since Entity Framework 5.0 you can use following code in order to solve your issue:

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.

Pointing out that all of the Entity Framework < 6.0 suggestions generate some awkward SQL. See second example for "clean" fix.

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:

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:

// 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)

If you prefer using method (lambda) syntax as I do, you could do the same thing like this:

var result = new TableName();


using(var db = new EFObjectContext)
{
var query = db.TableName;


query = value1 == null
? query.Where(tbl => tbl.entry1 == null)
: query.Where(tbl => tbl.entry1 == value1);


query = value2 == null
? query.Where(tbl => tbl.entry2 == null)
: query.Where(tbl => tbl.entry2 == value2);


result = query
.Select(tbl => tbl)
.FirstOrDefault();


// Inspect the value of the trace variable below to see the sql generated by EF
var trace = ((ObjectQuery<REF_EQUIPMENT>) query).ToTraceString();


}


return result;