CleanCode NZ

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.

Tags: LINQ