C# – Linq to EntityFramework DateTime


In my application I am using Entity Framework.

My Table


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

I tried this code but its now working

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

When I run my code the following exception occur

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

Best Solution

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