Entity Framework queries and date differences in Where clauses

I'm using Entity Framework (EF) in many of my .NET applications, and in many cases, EF provides a nice set of features and coding convenience. However, in certain situations, I'd wish entity queries would be a bit better.

For example, just recently, I needed to write an entity query against an SQL Server database with C# and .NET 4.0. I needed to find certain database records that are not too old, based on a creation date field within the database table.

The query itself was pretty simple (or that is what I thought initially):

var orders = from o in entities.Orders
             where (System.DateTime.Now - o.OrderDate) < TimeSpan.FromDays(60)
             select o;

However, at runtime, this causes a System.ArgumentException with the message "DbArithmeticExpression arguments must have a numeric common type."

The problem is caused by the fact that Entity Framework doesn't directly support queries involving TimeSpan math. Of course, the simple fix is to calculate the date different first, and then compare the date with a temporary variable, such as like this:

DateTime dateLimit = System.DateTime.Now.AddDays(-60);
var orders = from o in entities.Orders
             where o.OrderDate >= dateLimit
             select o;

However, there might be situations where you could not use this simple solution, for instance when comparing values of two database fields.

Luckily, a class called EntityFunctions is available in .NET 4.0 and later, and this class contains a handy DiffDays method, which you can use like this:

var orders = from o in entities.Orders
             where EntityFunctions.DiffDays(o.OrderDate, System.DateTime.Now) < 60
             select o;

If you are using SQL Server, LINQ To Entities will convert this call to the SQL Server function DATEDIFF. Notice the order of parameters in the DiffDays method call.

The EntityFunctions class is defined in the System.Data.Objects namespace. For more details, see the documentation topic "Date and Time Canonical Functions" on MSDN.

Happy hacking!