LINQ to Entities to Subtract 2 Dates

Question

I am trying to determine the number of days between two dates using LINQ with the Entity Framework. He tells me that he does not recognize Subtract in the System.TimeSpan class

Here is my part of the LINQ query.

where ((DateTime.Now.Subtract(vid.CreatedDate).TotalDays < maxAgeInDays)) 

Here is the error I get in VS.NET debugger

{"LINQ to Entities does not recognize the" System.TimeSpan Subtract (System.DateTime) "method, and this method cannot be translated into a storage expression." }

Am I doing something wrong or is there a better way to get the number of days between 2 DateTimes in an entity structure?

thanks michael

Answer 1

That's 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 changed where part of the LINQ query

 where (vid.CreatedDate >= oldestDate ) 

worked like a charm - thanks Micah for thinking about the expression tree

Answer 2

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)) 

Answer 3

You can also use System.Data.Objects.EntityFucntions :

 currentDate = DateTime.Now; ... where EntityFunctions.DiffDays(currentDate, vid.CreatedDate) < maxAgeIdDays 

All functions from EntityFunctions are Linq-to-entity only and are mapped to SQL functions.

Answer 4

You encounter such problems because the predicate must be translated into the expression tree. And the translation process does not recognize the DateTime.Now.Subtract method.

Answer 5

The fact is that when developing LINQ to Entities, you must translate the entire query into SQL queries. Where it cannot recognize the Subtract method. This will happen when you try to use the C # / VB method inside the request. In these cases, you need to figure out a way to extract this part from the query. This article explains a little more: http://mosesofegypt.net/post/LINQ-to-Entities-what-is-not-supported.aspx

Answer 6

You can define a 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 can use something like this:

 var query = from temp in db.Table select new MyModel { Id = temp.Id, Variable1 = temp.Variable1, ... EndDate = temp.EndDate, StartDate = temp.StartDate } 

When you look at the result, you can use return, for example:

 return query 

Now in the request we have CalculateTime (subtract from EndDate and Startdate).