Doing comparisons with NULL
can yield unexpected results.
Relational database engines treat the NULL
case differently. NULL
is not a value; rather, it is the absence of a value, so the syntax around it is special. To check if a column value is NULL
, this is the syntax we use:
SELECT * FROM MyTable WHERE MyCol IS NULL
Entity Framework, as with other Object-Relational Mappers, has to take this into account. So, what happens if we issue a LINQ query that needs to be executed with a parameter value that may be null
? Let's consider this query:
var name = GetParameterValue(); var records = ctx .MyEntities .Where(x => x.Name == name) .ToList();
By default, it will produce the following SQL:
SELECT [x].[Id] AS Id, [x].[Name] AS Name FROM [dbo].[MyEntities] AS [x] WHERE ([x].[Name] == @__name_0) OR (([x].[Name] IS NULL) AND ((@__name_0 IS NULL))
This is hardly ideal and it is caused by the fact that, when the SQL is being generated, Entity Framework does not know what value the name parameter will have when the query is executed. If the LINQ query instead uses a literal null, or something clearly different than null, the problem does not occur. Because it doesn't know, it has to be cautious and check, if the values are identical using normal semantics or check if they are both NULL
. Unfortunately, this results in some extra work for the database engine.
If we are 100% sure that the values that we will be using in LINQ comparison queries, we can turn on the UseRelationalNulls
flag:
protected override void OnConfiguring( DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseSqlServer(_connectionString, opt => { //if this is present, use the simple check opt.UseRelationalNulls(); }); base.OnConfiguring(optionsBuilder); }
If you set this, then the same query will produce this SQL instead:
SELECT [x].[Id] AS Id, [x].[Name] AS Name FROM [dbo].[MyEntities] AS [x] WHERE [x].[Name] == @__name_0
Of course, this will never return any records if the parameter is ever null
, so be warned.