LINQ-to-Entities Date Comparison Gotcha

I came across this problem very recently while trying to deploy a working product to a new server.  There's more information in this StackOverflow question I posted, but here's a summary.

When you do a date comparison with DateTime.Now using LINQ-to-Entities against a SQL Server database, the engine translates DateTime.Now to GetDate().

For example:

  
var items = (from t in context.Token  
             where t.Expiry > DateTime.Now).ToList();

will ultimately generate SQL that says:

  
...
WHERE Token.Expiry > GetDate()  
...

This can cause some significant (and subtle) problems:

If the application running this code is on a different server to the SQL database, any time differences can break code.

This is what happened to me.  There was a one minute time difference between the web service running this code and the SQL Server machine.  I was using the Entity Framework to set the expiry date to DateTime.Now.AddSeconds(60), and retrieving it later by comparing it to DateTime.Now.  Despite this, I was effectively setting the expiry using the web server's time, and comparing it using the SQL Server's time.

If you're having a similar problem, I hope you stumble across this post.

Damian Brady

I'm an Australian developer, speaker, and author specialising in DevOps, developer process, and software architecture. I love Octopus Deploy, Visual Studio Team Services, and reducing process waste.