Yucel Yucel - 2 months ago 59
C# Question

Linq to EntityFramework DateTime

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 its now working

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 occur


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

Answer

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.