EntityFramework DateTime Linq

c# datetime entity-framework linq

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

1
105
9/19/2016 11:14:50 AM

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

195
1/14/2014 7:53:47 PM

Popular Answer

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)


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow