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.
This question is related to
.net
entity-framework
ado.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.
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;
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:
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").
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.
to deal with Null Comparisons use Object.Equals()
instead of ==
check this reference
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.
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.
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
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.
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 =)
Pointing out that all of the Entity Framework < 6.0 suggestions generate some awkward SQL. See second example for "clean" fix.
// 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
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)
Source: Stackoverflow.com