LINQ to Entities: Using DateTime.AddMonth in Lambda Expression

Question

Hello,

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)
                        .ToList();

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

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

Thank you very much in advance!

Gsus.

Answer 1

LINQ to Entities converts your linq expression into SQL code, sometimes this conversion is seamless such as integer addition, selects, groups, etc, but sometimes the abstraction leaks, as in date operations.

Answer 2

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)
                        .ToList();
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.