Linq to EntityFramework DateTime

Question

In my application, I am using Entity Framework.

My table

-Article -period -startDate 

I need records that match => DateTime.Now > startDate and (startDate + period) > DateTime.Now

I tried this code but now it works

 Context.Article .Where(p => p.StartDate < DateTime.Now) .Where(p => p.StartDate.AddDays(p.Period) > DateTime.Now) 

When I run my code, the following exception occurs

LINQ to Entities does not recognize the 'System.DateTime AddDays (Double)' method, and this method cannot be translated into a storage expression.

Answer 1

When using the LINQ to Entity Framework, your predicates inside the Where clause are converted to SQL. You get this error because there is no SQL translation for DateTime.Add() , which makes sense.

The quick work is to read the results of the first Where clause in memory, and then use LINQ to Objects to complete the filtering:

 Context.Article.Where(p => p.StartDate < DateTime.Now) .ToList() .Where(p => p.StartDate.AddDays(p.Period) > DateTime.Now); 

You can also try EntityFunctions.AddDays if using .NET 4.0:

 Context.Article.Where(p => p.StartDate < DateTime.Now) .Where(p => EntityFunctions.AddDays(p.StartDate, p.Period) > DateTime.Now); 

Note: In EF 6 now System.Data.Entity.DbFunctions.AddDays .

Answer 2

I think this is what the last answer tried to answer, but instead of trying to add days to p.startdat (something that cannot be converted to an SQL query), why not do something that can be equated to sql:

 var baselineDate = DateTime.Now.AddHours(-24); something.Where(p => p.startdate >= baselineDate) 

Answer 3

How about subtracting 2 days from DateTime.Now:

 Context.Article .Where(p => p.StartDate < DateTime.Now) .Where(p => p.StartDate > DateTime.Now.Subtract(new TimeSpan(2, 0, 0, 0))) 

Honestly, I'm not sure what you are trying to achieve, but it may work

Answer 4

If you need your expression to be translated into SQL, you can try using

System.Data.Entity.Core.Objects.AddDays Method.

In fact, it is deprecated, but it works. It should be replaced by System.Data.Entity.DbFunctions.AddDays, but I can not find it ...