When you run queries against non-nullable columns (NOT NULL in SQL), everything is going smoothly. You should pay some attention when you deal with nullable fields (one of the cases will be covered at the end of this post).
Now let’s look closer to Entity Framework queries against nullable field. I’ve added a new column LastUpdate with the type DateTime? to my Client entity.
|
1 2 |
[Column(TypeName = "date")] public DateTime? LastUpdate { get; set; } |
So I have some records with concrete dates, and some with NULL values.
1. Filtering “greater or equal than”
|
1 2 |
var query231 = ctx.Clients .Where(c => c.LastUpdate >= filterDate); |
C# understands it perfectly. We have a simple and correct SQL query.
|
1 2 3 4 5 6 7 8 |
SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], [Extent1].[CountryCode] AS [CountryCode], [Extent1].[Timestamp] AS [Timestamp], [Extent1].[LastUpdate] AS [LastUpdate] FROM [dbo].[Clients] AS [Extent1] WHERE [Extent1].[LastUpdate] >= @p__linq__0 |
2. Filtering all concrete dates aka WHERE … IS NOT NULL
You can use one of the choices.
|
1 2 3 4 5 |
var query232 = ctx.Clients .Where(c => c.LastUpdate != null); var query233 = ctx.Clients .Where(c => c.LastUpdate.HasValue); |
Both generate the same correct SQL query.
|
1 2 3 4 5 6 7 8 |
SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], [Extent1].[CountryCode] AS [CountryCode], [Extent1].[Timestamp] AS [Timestamp], [Extent1].[LastUpdate] AS [LastUpdate] FROM [dbo].[Clients] AS [Extent1] WHERE [Extent1].[LastUpdate] IS NOT NULL |
3. Filtering multiple dates aka WHERE … IN
This case is a bit harder because C# could not compare DateTime[] array and DateTime? field. You need to get a not-nullable value from column.
|
1 2 |
var query234 = ctx.Clients .Where(c => filterDateArray.Contains(c.LastUpdate.Value)); |
Looks good, but I’m not satisfied with conversion of C# DateTime array values to SQL Server DateTime2 type. Especially when I set a strong annotation to use Date type in database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], [Extent1].[CountryCode] AS [CountryCode], [Extent1].[Timestamp] AS [Timestamp], [Extent1].[LastUpdate] AS [LastUpdate] FROM [dbo].[Clients] AS [Extent1] WHERE ( [Extent1].[LastUpdate] IN ( convert(datetime2, '2017-07-01 00:00:00.0000000', 121), convert(datetime2, '2017-08-01 00:00:00.0000000', 121) ) ) AND ([Extent1].[LastUpdate] IS NOT NULL) |
If you don’t want to build a query with every parameter despite whether it is null or not, you might build a dynamic filter like in the following sample.
|
1 2 3 4 5 6 7 8 9 |
var query235 = ctx.Clients.AsQueryable(); if (startDate.HasValue) { query235 = query235.Where(c => c.LastUpdate >= startDate); } if (endDate.HasValue) { query235 = query235.Where(c => c.LastUpdate < endDate); } |
What will be if both parameters are null? No filters will be added, and you’ll get a query returning all(!) rows regardless of having a concrete value or not. In this case add another check when both parameters are null. One of the simplest implementation is to add third if statement.
|
1 2 3 4 |
if (!startDate.HasValue && !endDate.HasValue) { query235 = query235.Where(c => c.LastUpdate.HasValue); } |