How can I query for null values in entity framework?
.NetEntity Frameworkado.net.Net Problem Overview
I want to execute a query like this
var result = from entry in table
where entry.something == null
select entry;
and get an IS NULL
generated.
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.
Edit no.2:
The above query works as intended. It correctly generates IS NULL
. My production code however was
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 will close this question.
.Net Solutions
Solution 1 - .Net
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.
Solution 2 - .Net
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.
Solution 3 - .Net
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:
- 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.
Solution 4 - .Net
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 =)
Solution 5 - .Net
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
Solution 6 - .Net
to deal with Null Comparisons use Object.Equals()
instead of ==
check this reference
Solution 7 - .Net
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)
Solution 8 - .Net
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.
Solution 9 - .Net
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.
Solution 10 - .Net
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.
Solution 11 - .Net
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").
Solution 12 - .Net
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.
Solution 13 - .Net
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;
Solution 14 - .Net
var result = from entry in table
where entry.something == value||entry.something == null
select entry;
use that