How to use DATEADD over column in LINQ - DateAdd is not recognized by LINQ

Question

I am trying to invalidate requests of friendship that were reponded less than 30 days ago.

var requestIgnored = context.Request
    .Where(c => c.IdRequest == result.IdRequest 
             && c.IdRequestTypes == 1 
             && c.Accepted == false 
             && DateTime.Now <= (((DateTime)c.DateResponse).AddDays(30)))
   .SingleOrDefault();

c.DateResponse is of type DateTime?. The error I am having is :

LINQ does not recognize the command .AddDays

What LinqProvider are you using? For L2E: http://stackoverflow.com/questions/4146300/linq-to-entityframework-datetime — Tim Schmelter, Jun 28 '13 at 19:06

score 60 · Accepted Answer · edited Jan 15 '20 at 01:40

60

Edit: If you're using EntityFramework >= 6.0, use DbFunctions.AddDays. For older versions of Entity Framework, use EntityFunctions.AddDays:

var requestIgnored = context.Request
    .Where(c => c.IdRequest == result.IdRequest 
             && c.IdRequestTypes == 1 
             && c.Accepted == false 
             && DateTime.Now <= DbFunctions.AddDays(c.DateResponse, 30))
   .SingleOrDefault();

edited Jan 15 '20 at 01:40

JProgrammer

2,750
2
25
36

answered Jun 28 '13 at 19:08

Scott Wegner

7,263
2
39
55

1

EntityFunctions is a new world for me !! thank you!! This worked really good. – Bryan Arbelo - MaG3Stican Jun 28 '13 at 19:21
2

You should then "accept as answer". Cross the checkmark next to the answer. This will give credit to the person who helped you and help others quickly locate the correct answer. – Tormod Jun 28 '13 at 19:27
Note that `EntityFunctions` will only work inside an EF context. Attempting to evaluate the method (i.e. on a local collection for unit tests) will throw an exception. – Scott Wegner Jun 28 '13 at 20:42
1

Just found this post and implemented the above code, to be instructed that System.Data.Entity.Core.Objects.EntityFunctions is now obsolete, and is replaced by System.Data.Entity.DbFunctions. – Brett Rigby Nov 10 '14 at 04:23
2

Thanks @BrettRigby, I've updated the answer for EF 6 – Scott Wegner Nov 10 '14 at 15:22
Please note: Unfortuantely EFCore 1.1 (which used to be EF7) does not yet have this functionality. This can result in horribly inefficient SQL where logic is being ran in C# that you would have expected to run in the DB : https://github.com/aspnet/EntityFramework/issues/2850 – Simon_Weaver Dec 22 '16 at 09:22
Both of those links go to the same place. – juharr Mar 14 '18 at 12:04

score 6 · Answer 2 · answered Jun 28 '13 at 19:04

You might try this:

var thirtyDaysAgo = DateTime.Now.AddDays(-30);
var requestIgnored = context.Request
    .Where(c => 
        c.IdRequest == result.IdRequest && 
        c.IdRequestTypes == 1 && 
        c.Accepted == false && 
        c.DateResponse.HasValue &&
        thirtyDaysAgo <= c.DateResponse.Value)
    .SingleOrDefault();

How to use DATEADD over column in LINQ - DateAdd is not recognized by LINQ

2 Answers2

Linked