Datetime.UtcNow in Entity Framework query evaluated different than DateTime.UtcNow in C# IL

Question

Are DateTime functions in an EF query evaluated by the SQL Server, as where DateTime functions outside of the query expression are evaluated by the machine running the IL?

I have an application that has SalesOrders

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

I run an EF query 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 when I do this:

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

I think this is because DateTime.UtcNow in an Entity Framework query is evaluated by the SQL Server, vs DateTime.UtcNow outside of the query is evaluated by the machine that's running the IL; I'm basing that off this answer.

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

Answer 1

Your thoughts are correct.

On SQL Server, your first query runs 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 the client's time is passed in as a parameter.

Your second query sends this to SQL server:

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

Here it is clear that the SQL Server clock is used.

Answer 2

DateTime.UtcNow is mapped to CurrentUtcDateTime(). Here is a full list:

CLR Method to Canonical Function Mapping