I know how to make LINQ compiler extensions to translate properties/methods into SQL DOM. However, what I'd like to do is make a compiler that will allow me to use SQL SERVER's GETUTCDATE() in a query.

from customer in session.Query.All<Customer>
where customer.CreatedOn <= [some way to get the query to use SQL's GETUTCDATE()]

That should generate:

select * from Customer where CreatedOn <= GETUTCDATE()

It would be even better if I could generate other dates relative to GETUTCDATE(). For example, if I wanted to query all customers created in the last 24 hours, the predicate would be CreatedOn >= [..].AddHours(-24) ... and this would be translated into CreatedOn >= DATEADD(HOUR, -24, GETUTCDATE()).

I can't simply use the web server's DateTime.UtcNow because the web server's time often differs from the database server's.

asked Dec 01 '11 at 15:15

ara's gravatar image


One Answer:

Hello, Ara. Try the following snippet. Make sure the following type is registered in DomainConfiguration

public static class CustomSqlCompilers
    [Compiler(typeof(DateTime), "UtcNow", TargetKind.Static | TargetKind.PropertyGet)]
    public static SqlExpression DateTimeUtcNow()
      return SqlDml.FunctionCall("GETUTCDATE");

answered Dec 02 '11 at 05:29

Denis%20Krjuchkov's gravatar image

Denis Krjuchkov

Hi, Denis. Unfortunately, this doesn't work (v4.5). The compiler method is never executed for some reason. The value of DateTime.UtcNow is simply passed as a parameter to the SQL query.

(Dec 04 '11 at 15:27) ara ara's gravatar image


We did some investigations which revealed that our LINQ translation pipeline tries extracting as much information as possible to convert it to SQL parameters. In your case static property it is always extracted that way.

Unfortunately, for now there is no graceful workaround for such behavior, although we have an idea of how this should be done. I hope that we'll implement that in time frame of week or two. Stay tuned.

(Dec 05 '11 at 09:17) Denis Krjuchkov Denis%20Krjuchkov's gravatar image

Hi Denis, any updates on this yet?

(Jan 01 '12 at 18:50) ara ara's gravatar image

Hello Ara. There are no updates yet. Everybody is on new year vacation :-) If this issue is critical for you, I can provide you a (somewhat hacky) workaround until full-featured solution is implemented.

(Jan 02 '12 at 10:35) Denis Krjuchkov Denis%20Krjuchkov's gravatar image
Your answer
toggle preview





Asked: Dec 01 '11 at 15:15

Seen: 3,979 times

Last updated: Jan 02 '12 at 10:35

powered by OSQA