C#: Why is one of my .Where() conditions missing in my SQL query?

Question

I have a method with the following line of code being run. It's supposed to get all the messages that have been created within the past 24 hours and aren't created in a future time (because that shouldn't be possible).

messages.AddRange(_dbcontext.Messages
                .Where(message => message.CreationTime >= DateTime.UtcNow.AddHours(-24) && message.CreationTime <= DateTime.UtcNow)
                .ToList());

When the application runs and passes the above line, this is the SQL Query that ran:

SELECT [message].[Id], [message].[CreationTime], [message].[Value]
FROM [Messages] AS [message]
WHERE([message].[CreationTime] <= GETUTCDATE())

This basically retrieves all messages, instead of those created within the last 24 hours.

I want to know why this part of the Where() is being ignored or not being transformed to an SQL query, and what I can do to make it work.

(message => message.CreationTime >= DateTime.UtcNow.AddHours(-24)

Answer 1

This is a problem with SQL query translation in EF Core versions prior 2.0 (unsupported DateTime methods like AddHours, AddDays etc.) which leads to so called client evaluation. Your query should produce correct results, but will be inefficient because the second part of the where filter will be evaluated in memory after retrieving all records that match the other condition.

It has been improved in EF Core 2.0 where the generated SQL looks like this (as expected):

SELECT [message].[Id], [message].[CreationTime], [message].[Value]
FROM [Messages] AS [message]
WHERE ([message].[CreationTime] >= DATEADD(hour, -24E0, GETUTCDATE())) AND ([message].[CreationTime] <= GETUTCDATE())

So either upgrade to EF Core 2.0, or use the usual EF workaround by putting the DateTime.UtcNow.AddHours(-24) into variable outside the query and use it inside.