Linq to EntityFramework DateTime

c# linq entity-framework datetime

79,520

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

View more solutions

79,520

Yucel

Updated on July 08, 2022

Comments

Yucel almost 2 years
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.
- Kevin Gauthier over 13 years
  
  What type is period? AddDays is the wrong function if it's a double.
Stefan P. over 13 years

This is a dangerous work-around, what happens if ToList() return a huge amount of data?
Justin Niessner over 13 years

@Stefan P. - Then the first statement would read a huge amount of data into memory. The second method should be fine, but it's only available with .NET 4.0.
Stefan P. over 13 years

Thanks for the EntityFunctions.AddDays tip I am using EF .net 4.0 but I didn't know about EntityFunctions, will look into it.
Saeed Amiri over 13 years

Why you using two Where clause?
Justin Niessner over 13 years

@SaeedAlg - In the first example it is necessary to read the items into memory. In the second example I kept two Where clauses to match the original format. Even if you compress the two into a single Where clause, Entity Framework generates identity SQL so it's really a matter of readability.
Yucel over 13 years

@ SaeedAlg. if i write where clauses seperatly it is easy to read. Is it decrease performance? i dont know
Justin Niessner over 13 years

@Yucel - It will not decrease performance at all. The generated SQL is identical.
Yucel over 13 years

Articles will be start to be shown on StartDate and end after x(period) days. This is what i am trying to do. Justin Niessner's second solution worked very well for what i want to see
Yucel over 13 years

@Justin Niessner thanks very much, i am trying to do that for four hours, you save my life in seconds thanks again
Leon about 12 years

I'm using Entity Framework 4 with MySql.Data.MySqlClient provider. MySql connector net 6.5.4. When I tried EntityFunctions.AddDays, it gives me an error: FUNCTION .AddDays does not exist. Does that mean it doesn't work with MySql?
Julien over 10 years

Thanks for System.Data.Entity.DbFunctions! I didn't know about that class
Frédéric almost 10 years

@Adrian Carr - It can be better for this use case but not for as many as the EntityFunctions solution. Here, the second operand is not retrieved from another entity in query and can be computed prior to querying. If both operand were to be found in db, the EntityFunctions solution would still be suitable while the solution of this response would not work anymore.
Isaac Llopis almost 9 years

When giving "quick" work-arounds with EF, we all should avoid the usage of ToList and ToArray methods. It's just as quick to calculate the date previously and compare to that value, and that works in an EF query without instantiating its results in memory.
mgrenier almost 9 years

Thanks for the note about EF 6, the EntityFunctions method wasn't working for me and I couldn't figure it out! That fixed my issue.
Travis J almost 9 years

DbFunctions worked great with EF6 for me as well. MSDN listed DateTime.AddDays as an appropriate solution but it failed. Thanks! :D
Andrew Harris about 6 years

This adds nothing more than the accepted answer from 4 years prior!
bubi about 6 years

@AndrewHarris also my answer is 4 years ago answer. In the first version of the answer there were a ToList (=> data materialization) before the Where (see the first comment of the answer).
Paul about 4 years

This is a better solution than the solution marked as the answer. The response from Justin / marked answer will return unnecessary results from the database. This solution actually sends the date in the SQL where filter and uses SQL to filter out the data, which is much more performant.