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.