Datetime.UtcNow in Entity Framework object request evaluates differently than DateTime.UtcNow in C # IL

Question

Are DateTime functions in an EF query evaluated by SQL Server since DateTime functions outside the query expression are evaluated by the machine running IL?

I have an application that has SalesOrders in it

public class SalesOrder { public Int32 OrderID {get;set;} public DateTime Expiration {get;set;} } 

I run an EF request and get different results when I do this:

 DateTime utcnow = DateTime.UtcNow; var open = (from a in context.SalesOrders where a.Expiration > utcnow select a).ToList(); 

Than I will do it:

 var open = (from a in context.SalesOrders where a.Expiration > DateTime.UtcNow select a).ToList(); 

I think this is due to the fact that DateTime.UtcNow in the Entity Framework query is evaluated by SQL Server, vs DateTime.UtcNow outside the query is evaluated by the machine running IL; I base this on the answer .

I'm on an Azure platform as a service, debugging locally using Azure SQL DB, if that matters.

Answer 1

Your thoughts are correct.

In SQL Server, your first query launches the following SQL query:

 exec sp_executesql N'SELECT [Extent1].[OrderID] AS [OrderID], [Extent1].[Expiration] AS [Expiration] FROM [dbo].[SalesOrders] AS [Extent1] WHERE [Extent1].[Expiration] > @p__linq__0',N'@p__linq__0 datetime2(7)',@p__linq__0='2016-01-08 20:05:25.4433282' 

It is clear here that client time is passed as a parameter.

The second query sends it to the SQL server:

 SELECT [Extent1].[OrderID] AS [OrderID], [Extent1].[Expiration] AS [Expiration] FROM [dbo].[SalesOrders] AS [Extent1] WHERE [Extent1].[Expiration] > (SysUtcDateTime()) 

It’s clear here that the clock is SQL Server.

Answer 2

DateTime.UtcNow maps to CurrentUtcDateTime (). Here is the complete list:

CLR method for canonical mapping of functions

Answer 3

No. When you write a query that compares dates, EF creates a Datetime parameter, sends it to SQL Server, and the comparison is performed by the server.