How to do LINQ DateDiff in .NET 3.5

You would have thought that doing datetime comparison in LINQ is nothing particular. But it turns out to be tricky and little bit difficult especially in .Net 3.5.

First trap is :

 from _contract in db.Contracts
 where  _contact.ExpiryDate <= DateTime.Now.AddHours(24)

This is a misleading query while no syntax error is thrown, but it does not give you the right answer, when you inspect the actual query executed by SQL server, it is actually comparing ExpiryDate with a string representation of DateTime.Now.AddHours(24) which is prone to a lot of environment variations like locale, the format of the datetime string etc.

The precise date time comparison in sql server has to use DateDiff, we know that some functions we used in LINQ can have run time issues if there is no equivalent implementation in LINQ to SQL, an error like … has no supported translation to SQL might be waiting for us.

Then a search of MSDN, I found we are lucky that most of the datetime functions are supported by LINQ

 from _contract in db.Contracts
 where _contact.ExpiryDate.Subtract(DateTime.Now).Hours <=24

Very tidy and beautifull, but again I get the error message :

Method System.TimeSpan Subtract(System.DateTime) has no supported translation to SQL

Unfortunate that those date time functions in LINQ only supported by .Net 4.0, while I am stuck on .Net3.5.

Here is my solution of date time comparison in LINQ for .Net3.5 using native T-SQL query command.

IEnumerable<Contract> contracts = db.ExecuteQuery<Contract>(
" select * from Contract where DATEDIFF(HOUR,GETDATE(),ExpiryDate) <=24 " );
// contracts.ToList();

I think even though this solution has to use the T-SQL, it still has the essence of LINQ to retrieve data in object way.

Have fun.


This entry was posted on Monday, September 6th, 2010 at 10:02 am and is filed under ASP.NET, SQLServer. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 Responses to “How to do LINQ DateDiff in .NET 3.5”

  1. Bart says:

    Greetings! Is it alright that I go a bit off topic? I am trying to view your domain on my Blackberry but it doesn’t display properly, do you have any suggestions? Thanks for the help I hope! Bart

  2. Christ CHRIST IS Wonderful

Leave a Reply