Linq to Entities does not recognize the method System.DateTime.. and cannot translate this into a store expression

Question

I have a problem that has taken me weeks to resolve and I have not been able to.

I have a class where I have two methods. The following is supposed to take the latest date from database. That date represents the latest payment that a customer has done to "something":

public DateTime getLatestPaymentDate(int? idCustomer)
{
    DateTime lastDate;

    lastDate = (from fp in ge.Payments
                from cst in ge.Customers
                from brs in ge.Records.AsEnumerable()
                where (cst.idCustomer == brs.idCustomer && brs.idHardBox == fp.idHardbox
                       && cst.idCustomer == idCustomer)
                select fp.datePayment).AsEnumerable().Max();

    return lastDate;
}//getLatestPaymentDate

And here I have the other method, which is supposed to call the previous one to complete a Linq query and pass it to a Crystal Report:

//Linq query to retrieve all those customers'data who have not paid their safebox(es) annuity in the last year.
public List<ReportObject> GetPendingPayers()
{
    List<ReportObject> defaulterCustomers;

      defaulterCustomers = (from c in ge.Customer
                            from br in ge.Records
                            from p in ge.Payments

                            where (c.idCustomer == br.idCustomer
                                   && br.idHardBox == p.idHardBox)

                            select new ReportObject
                            {
                                CustomerId = c.idCustomer,
                                CustomerName = c.nameCustomer,
                                HardBoxDateRecord = br.idHardRecord,
                                PaymentDate = getLatestPaymentDate(c.idCustomer),
                            }).Distinct().ToList();
}//GetPendingPayers

No compile error is thrown here, but when I run the application and the second method tries to call the first one in the field PaymentDate the error mentioned in the header occurs:

Linq to Entities does not recognize the method System.DateTime.. and cannot translate this into a store expression

Please anybody with an useful input that put me off from this messy error? Any help will be appreciated !

Thanks a lot !

score 1 · Accepted Answer · edited May 23 '17 at 10:33

Have a look at these other questions :

LINQ to Entities does not recognize the method

LINQ to Entities does not recognize the method 'System.DateTime Parse(System.String)' method

Basically, you cannot use a value on the C# side and translate it into SQL. The first question offers a more thorough explanation ; the second offers a simple solution to your problem.

EDIT :

Simply put : the EF is asking the SQL server to perform the getLatestPaymentDate method, which it has no clue about. You need to execute it on the program side.

Simply perform your query first, put the results into a list and then do your Select on the in-memory list :

 List<ReportObject> defaulterCustomers;

  var queryResult = (from c in ge.Customer
                        from br in ge.Records
                        from p in ge.Payments

                        where (c.idCustomer == br.idCustomer
                               && br.idHardBox == p.idHardBox)).Distinct().ToList();

defaulterCustomers = from r in queryResult
                     select new ReportObject
                        {
                            CustomerId = r.idCustomer,
                            CustomerName = r.nameCustomer,
                            HardBoxDateRecord = r.idHardRecord,
                            PaymentDate = getLatestPaymentDate(r.idCustomer),
                        }).Distinct().ToList();

I don't have access to your code, obviously, so try it out and tell me if it works for you! You'll end up with an in-memory list

Hi, Thanks for your inputs, but I really could not make this work yet. You said that the second question provided by you was the simplest but I do not get how it would actually solve my problem due to I already declared a variable of DateTime type and parsed it to a hypothetical date and still does not work. Could you explain this a little more to me? I would appreciate it so much ! — Stefan S, Mar 14 '13 at 22:26
Of course! Simply put : the `getLatestPaymentDate` has no SQL equivalent. I'll edit my answer to be more explicit in what you could do. — SolarBear, Mar 15 '13 at 14:38

Linq to Entities does not recognize the method System.DateTime.. and cannot translate this into a store expression

1 Answers1