CODEWITHSUNDEEP
c#linqentity-frameworkdatetime
Yucel
Yucel

Reputation: 2673

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

Upvotes: 119

Views: 87796

Answers (4)

Justin Niessner
Justin Niessner

Reputation: 245479

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.

Upvotes: 222

bubi
bubi

Reputation: 6501

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

Upvotes: 3

andrew
andrew

Reputation: 1260

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)

Upvotes: 96

TimC
TimC

Reputation: 1061

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

Upvotes: 3

Related Questions