Question

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.

Accepted Answer

2

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 Krjuchkov
1793●2●5

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,

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

Hi Denis, any updates on this yet?

(Jan 01 '12 at 18:50) ara

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

Subscription:

Related questions