LINQ to Entities for subtracting 2 dates
Entity FrameworkDatetimeLinq to-EntitiesEntity Framework Problem Overview
I am trying to determine the number of days between 2 dates using LINQ with Entity Framework. It is telling me that it does not recognize Subtract on the System.TimeSpan class
Here is my where portion of the LINQ query.
where ((DateTime.Now.Subtract(vid.CreatedDate).TotalDays < maxAgeInDays))
Here is the error I receive in the VS.NET debugger
>{"LINQ to Entities does not recognize the method 'System.TimeSpan Subtract(System.DateTime)' method, and this method cannot be translated into a store expression."}
Am I doing something wrong or is there a better way to get the number of days between 2 DateTimes in the entity framework?
thanks Michael
Entity Framework Solutions
Solution 1 - Entity Framework
The accepted answer is better in this case, but for reference you can use the EntityFunctions
class to perform operations on dates, among other things.
where (vid.CreatedDate >= EntityFunctions.AddDays(DateTime.Now, -maxAgeInDay))
Solution 2 - Entity Framework
Here is how I got it to work
I defined a datetime variable that represents the oldest date
DateTime oldestDate = DateTime.Now.Subtract(new TimeSpan(maxAgeInDays, 0, 0, 0, 0));
...
then I modified the where portion of the LINQ query
where (vid.CreatedDate >= oldestDate )
worked like a charm - thanks Micah for getting me to think about the expression tree
Solution 3 - Entity Framework
You can also use System.Data.Objects.EntityFucntions
:
currentDate = DateTime.Now;
...
where EntityFunctions.DiffDays(currentDate, vid.CreatedDate) < maxAgeIdDays
All functions from EntityFunctions
are only for Linq-to-entities and are mapped to SQL functions.
Solution 4 - Entity Framework
You run into these kind of isses because the predicate needs to be translated to an expression tree. And translation process doesn't recognize the DateTime.Now.Subtract method.
Solution 5 - Entity Framework
The fact is that by design, LINQ to Entities needs to translate the whole query to SQL statements. That's where it cannot recognize Subtract method. It will occur whenever you try to use a C#/VB method inside a query. In these cases you have to figure out a way to bring out that part from the query. This post explains a bit more: http://mosesofegypt.net/post/LINQ-to-Entities-what-is-not-supported.aspx
Solution 6 - Entity Framework
You may define new property in your model:
public DateTime StartDate{ get; set; }
public DateTime EndDate{ get; set; }
public TimeSpan CalculateTime{
get
{
return EndDate.Subtract(StartDate);
}
}
Now, you may use something like that:
var query = from temp in db.Table
select new MyModel {
Id = temp.Id,
Variable1 = temp.Variable1,
...
EndDate = temp.EndDate,
StartDate = temp.StartDate
}
When you have look at result, you may use return such as:
return query
Now, in query, we have CalculateTime (subtract between EndDate and Startdate).