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.

Maybe because you run them in different time and `DateTime.UtcNow` is different — Hamid Pourjam, Jan 08 '16 at 20:04
@dotctor "run them in different time" ? I have the query written one way, and I see expired things for a few seconds. I change the code, and expired things are done. — Eric, Jan 08 '16 at 20:07
@dotctor nope, I just have both of them there to lay out the problem. In fact I used "than" not "then". — Eric, Jan 08 '16 at 20:09

score 8 · Accepted Answer · answered Jan 08 '16 at 20:07

8

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.

answered Jan 08 '16 at 20:07

Yacoub Massad

27,509
2
36
62

Do you have a reference for it? – Hamid Pourjam Jan 08 '16 at 20:09
1

@dotctor he posted the query outputs and it jives with what I'm seeing and other answers that indicate the same. All you've done is give bad advice and try to close the question. – Eric Jan 08 '16 at 20:11
1

@doctor, This is the output I get from SQL Profiler. Check [Giorgi's answer](http://stackoverflow.com/a/34685436/2290059), it contains a reference. – Yacoub Massad Jan 08 '16 at 20:30

score 2 · Answer 2 · answered Jan 08 '16 at 20:22

2

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

CLR Method to Canonical Function Mapping

answered Jan 08 '16 at 20:22

Giorgi

30,270
13
89
125

score -1 · Answer 3 · answered Jan 08 '16 at 20:02

-1

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.

answered Jan 08 '16 at 20:02

Adilson de Almeida Jr

2,761
21
37

I want to believe that, but why would I get different results with the different code, if the dates are exactly the same? – Eric Jan 08 '16 at 20:06
Datetime.UtcNow specifically is rendered as a sql call to ´SysUtcDateTime()´. So in the second case you are using the SQL Server time. A tip to ease your next tests is to install MiniProfiler or any other tool which you can inspect the queries Entity Framework is performing. – Adilson de Almeida Jr Jan 08 '16 at 20:21

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

3 Answers3

Linked