CODEWITHSUNDEEP
c#entity-frameworklinqdatetimeazure
Eric
Eric

Reputation: 2293

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

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.

Upvotes: 7

Views: 1842

Answers (3)

Giorgi
Giorgi

Reputation: 30883

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

CLR Method to Canonical Function Mapping

Upvotes: 2

Yacoub Massad
Yacoub Massad

Reputation: 27871

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.

Upvotes: 9

Adilson de Almeida Jr
Adilson de Almeida Jr

Reputation: 2755

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

Upvotes: -1

Related Questions