Linq to EntityFramework DateTime

Solution 1:

When using LINQ to Entity Framework, your predicates inside the Where clause get translated to SQL. You're getting that error because there is no translation to SQL for DateTime.Add() which makes sense.

A quick work-around would be to read the results of the first Where statement into memory and then use LINQ to Objects to finish filtering:

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

You could also try the EntityFunctions.AddDays method if you're 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 it's now System.Data.Entity.DbFunctions.AddDays.

Solution 2:

I think this is what that last answer was trying to suggest, but rather than trying to add days to p.startdat (something that cannot be converted to a sql statement) why not do something that can be equated to sql:

var baselineDate = DateTime.Now.AddHours(-24);

something.Where(p => p.startdate >= baselineDate)

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

To be honest, I not sure what you are trying to achieve, but this may work

Solution 4:

If you need that your expression gets translated to SQL you can try to use

System.Data.Entity.Core.Objects.AddDays method.

Actually is marked obsolete but it works. It should be replaced by System.Data.Entity.DbFunctions.AddDays but I can't find it...