LINQ to Entities using datetime differently

Question

Apologies it's probably a poor title; I have a question more out of intrigue than anything.

I have tested the same LINQ to Entities statement wrote in 2 different ways, one using Datetime.Now and one using a date variable:

var timeNow = DateTime.Now;
var pendingMailshots = db.MailshotHistoryDatas.Where(m =>
                m.SendDate < timeNow).ToList();

var pendingMailshots = db.MailshotHistoryDatas.Where(m =>
                m.SendDate < DateTime.Now).ToList();

I noticed there were discrepencies in the data they were finding and after some digging and some profiling I found this:

exec sp_executesql N'SELECT 
[Extent1].[MailshotGuid] AS [MailshotGuid], 
[Extent1].[MailshotLineId] AS [MailshotLineId], 
[Extent1].[SendDate] AS [SendDate], 
[Extent1].[MessageType] AS [MessageType], 
[Extent1].[SendStatus] AS [SendStatus], 
[Extent1].[Recipients] AS [Recipients], 
[Extent1].[SendAttempts] AS [SendAttempts], 
[Extent1].[DateSent] AS [DateSent]
FROM  [dbo].[MailshotLineDatas] AS [Extent1]
INNER JOIN [dbo].[MailshotDatas] AS [Extent2] ON [Extent1].[MailshotGuid] = [Extent2].[MailshotGuid]
WHERE ([Extent1].[SendDate] < @p__linq__0),N'@p__linq__0 datetime2(7),@p__linq__0='2018-01-04 15:11:26.5618636'


SELECT 
[Extent1].[MailshotGuid] AS [MailshotGuid], 
[Extent1].[MailshotLineId] AS [MailshotLineId], 
[Extent1].[SendDate] AS [SendDate], 
[Extent1].[MessageType] AS [MessageType], 
[Extent1].[SendStatus] AS [SendStatus], 
[Extent1].[Recipients] AS [Recipients], 
[Extent1].[SendAttempts] AS [SendAttempts], 
[Extent1].[DateSent] AS [DateSent]
FROM  [dbo].[MailshotLineDatas] AS [Extent1]
INNER JOIN [dbo].[MailshotDatas] AS [Extent2] ON [Extent1].[MailshotGuid] = [Extent2].[MailshotGuid]
WHERE([Extent2].[StartDate] < (SysDateTime())))

Using the datetime variable it runs the query as a stored procedure and using Datetime.Now it converts the code into TSQL.

Can anyone explain why this is? Also, what would you consider better practice?

Thanks in advance, Adam

Answer 1

You're defining an expression that will be executed at a later time.

The first LINQ expression contains a DateTime variable which is initialized with a certain value (DateTime.Now). However, at the time that the expression will be executed, that variable will no longer have the value of the current date. Therefore, a parameter is used when the expression is actually executed.

In the latter case, you specify a query where the filter clause must use the current date/time. Since the expression is deffered executed, the engine does not know when you will actually execute it, so it uses the database-specific function to get the current date and time.

For this situation, there is no such thing as 'what is the better practice'. It all depends on your use-case. This behaviour is just specific to the deferred execution of LINQ queries: you're essentially building an expression that will only be executed once you call ToList() / ToArray / etc...

When you re-write your code to something like this:

var timeNow = DateTime.Now;
var query = db.MailshotHistoryDatas.Where(m =>
                m.SendDate < timeNow);

var pendingMailshots = query.ToList();

In the above sample, the query will only be executed at the line of code where you call query.ToList();
Since it is possible that you build the expression and execute it much later in your program, LINQ must use a query with a parameter to make sure that the correct date and time is passed to that query, hence the variable you've defined.

Answer 2

If you pass DateTime.Now in your filter expression, the provider knows what to do with it and replaces it with the call to SYSDATETIME, otherwise it has no choice but to assume you are looking for a specific date. This is how Expressions work in C# (see here)

As for which one to use, well that really depends on your use case. The call to SYSDATETIME will use the time on the server, whereas the latter will use the time on the machine making the call. In practice these are likely to be the same anyway, or at least only slightly different (taking into account network latency, time drift etc.)

Answer 3

Well, in the first case, it KNOWS it's using the current system time because you passed on DateTime.Now, so LINQ is trying to find an equivalent in SQL, which is SysDateTime().

In the second case you're passing a value, there's no equivalent in SQL, which means LINQ will have to make it into an SP and pass the value.

I would highly recommend using

var pendingMailshots = db.MailshotHistoryDatas.Where(m =>
            m.SendDate < DateTime.Now).ToList();

because then you can be sure it's as current as it can possibly be when executed by SQL. If you're not really looking for system accuracy but instead are looking for the value passed on by the variable, then use

var timeNow = DateTime.Now;
var pendingMailshots = db.MailshotHistoryDatas.Where(m =>
                m.SendDate < timeNow).ToList();

Answer 4

This occurs because of how the Expression Tree is parsed to transform your LINQ statements into SQL. In the example where you use a variable to store the DateTime, the Expression being traversed is a ParameterExpression, which is converted to SQL by adding a parameter to the query. In the example where you use DateTime.Now directly in the LINQ query, the Expression is of type MemberExpression, and the Expression Tree traverser for Entity Framework has a special case for a MemberExpression representing DateTime.Now that converts it to SQL as SysDateTime().

If your intent is always to use the current database time in the query, I would use DateTime.Now directly in the LINQ expression. If the intent is to use a specific time captured on the Application Server in the query, I would use the variable.

This MSDN Blog has a comprehensive explanation of how this works.