Linq to EntityFramework DateTime

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.

What type is `period`? `AddDays` is the wrong function if it's a `double`. — Craig Stuntz, Nov 10 '10 at 15:56

score 218 · Accepted Answer · edited Jun 19 '20 at 12:17

218

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.

edited Jun 19 '20 at 12:17

Florian Falk

214
4
17

answered Nov 10 '10 at 15:56

Justin Niessner

242,243
40
408
536

42

This is a dangerous work-around, what happens if ToList() return a huge amount of data? – Stefan P. Nov 10 '10 at 16:00
@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. – Justin Niessner Nov 10 '10 at 16:07
7

Thanks for the EntityFunctions.AddDays tip I am using EF .net 4.0 but I didn't know about EntityFunctions, will look into it. – Stefan P. Nov 10 '10 at 16:15
Why you using two Where clause? – Saeed Amiri Nov 10 '10 at 16:18
2

@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. – Justin Niessner Nov 10 '10 at 16:24
@ SaeedAlg. if i write where clauses seperatly it is easy to read. Is it decrease performance? i dont know – Yucel Nov 10 '10 at 16:28
@Yucel - It will not decrease performance at all. The generated SQL is identical. – Justin Niessner Nov 10 '10 at 16:30
2

@Justin Niessner thanks very much, i am trying to do that for four hours, you save my life in seconds thanks again – Yucel Nov 10 '10 at 16:39
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? – Leon Mar 13 '12 at 12:56
Thanks for System.Data.Entity.DbFunctions! I didn't know about that class – Julien Jan 14 '14 at 00:39
3

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. – Isaac Llopis Jun 03 '15 at 08:45
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. – mgrenier Jul 21 '15 at 15:18
1

DbFunctions worked great with EF6 for me as well. MSDN listed DateTime.AddDays as an appropriate solution but it failed. Thanks! :D – Travis J Aug 05 '15 at 18:15
Thank you. Had to use DbFunctions.AddDays with EF6 – pjaaar Oct 25 '22 at 09:50
EF6 is no longer being actively developed. The latest EF library is EF Core. In EF Core, there is no longer an `AddDays` function. But you can use the newer `EF.Functions` instead. In particular, you can use `EF.Functions.DateDiffDays`. – Jack Daus May 23 '23 at 17:20

score 96 · Answer 2 · edited Nov 26 '12 at 21:14

96

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)

edited Nov 26 '12 at 21:14

Bo Persson

90,663
31
146
203

answered Oct 18 '11 at 20:16

andrew

1,260
9
14

3

@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. – Frédéric May 23 '14 at 13:20
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. – Paul May 07 '20 at 15:31

score 3 · Answer 3 · answered Nov 18 '14 at 15:07

3

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

answered Nov 18 '14 at 15:07

bubi

6,414
3
28
45

This adds nothing more than the accepted answer from 4 years prior! – Andrew Harris Mar 07 '18 at 02:10
@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). – bubi Mar 08 '18 at 17:59
1

Regarding the obsolescence of System.Data.Entity.Core.Objects.EntityFunctions.AddDays() there is a newer version called System.Data.Entity.DbFunctions.AddDays() that is newer and works as expected – Francesco Jan 23 '23 at 08:58

score 3 · Answer 4 · answered Nov 10 '10 at 16:12

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

answered Nov 10 '10 at 16:12

TimC

1,051
9
16

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 Nov 10 '10 at 16:38

Linq to EntityFramework DateTime

4 Answers4

Linked

Related