C# – LINQ to Entities: Using DateTime.AddMonth in Lambda Expression


I've been reading some posts but I don't find a solution to a problem that I have with LINQ To Entities, Lambda Expressions and DateTime.AddMonth.

The problem is that I'm trying to use DateTime.AddMonth inside a Lambda Expression and I'm getting this error:

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

when I execute this piece of code:

List<Orders> orders = context.Orders
                        .Where(o => o.IdOrderStatus == 1)
                        .Where(o => o.PaymentDate.Value.AddMonths(o.Products.ProductCategories.CommissionableMonths) > DateTime.Now)

Is there a way to avoid this exception but mantaining the same behavior?

I don't know a lot about Linq, Lambdas or Entity Framework.

Thank you very much in advance!


Best Solution

You could return the necessary information without filtering on the date, then filter on the date afterward. (Of course, I'm not sure what size your data will be, so this may be inefficient. If so, you'll need a SQL-based solution of some sort -- maybe a stored procedure.)

List<Orders> orders = context.Orders
                        .Where(o => o.IdOrderStatus == 1)
orders = orders.Where(o.PaymentDate.Value.AddMonths(o.Products.ProductCategories.CommissionableMonths) > DateTime.Now);

This turns the AddMonths portion of the query in to a Linq-to-Objects method instead of Linq-to-Entities call.