Hi,

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

ara
395838791


One Answer:

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

[CompilerContainer(typeof(SqlExpression))]
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
179325

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

Ara,

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
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!
toggle preview

powered by OSQA