How can i use DateTime.AddXXXX functions in a Linq-to-Entities query?

Question

I am trying to use AddMonths in a query

List<Entities.Subscriber> items = (from s in context.Subscribers
                 where s.Validated == false && s.ValidationEmailSent == true && s.SubscriptionDateTime < DateTime.Now.AddMonths(-1)
                 select s).ToList();

But I recieve an error :

LINQ to Entities does not recognize the method 'System.DateTime AddMonths(Int32)' method, and this method cannot be translated into a store expression.

Is there a way I can use this function inside my query?

Answer 1

The simplest fix to this is to work out the time limit once before using LINQ:

DateTime limit = DateTime.Now.AddMonths(-1);

List<Entities.Subscriber> items = (from s in context.Subscribers
             where s.Validated == false && s.ValidationEmailSent == true && 
                                           s.SubscriptionDateTime < limit)
             select s).ToList();

Or more readably IMO:

var items = context.Subscribers
                   .Where(s => !s.Validated &&
                               s.ValidationEmailSent &&
                               s.SubscriptionDateTime < limit)
                   .ToList();

There's no benefit in using a query expression here, and explicit comparisons with true and false are ugly IMO (unless your properties are of type Nullable<bool> of course).

Answer 2

Jon Skeet has already provided a simple fix, but if you want the DateTime.Now.AddMonths bit to run on the database, try the EntityFunctions.AddMonths method.

This is a more general approach that is especially useful when you cannot replicate the expression cheaply or correctly on the client.

Answer 3

You can change your code to:

 DateTime oneMonth = DateTime.Now.AddMonths(-1)
    List<Entities.Subscriber> items = (from s in context.Subscribers
                     where s.Validated == false && s.ValidationEmailSent == true && s.SubscriptionDateTime < oneMonth
                     select s).ToList();

You have to do this because AddMonth is a .NET function that can't be translated into SQL by Linq to Entities. Perform the calculation in your code and then use the resulting datetime will work.