CODEWITHSUNDEEP
entity-frameworklinq-to-entities
Stefan S
Stefan S

Reputation: 267

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

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 !

Upvotes: 3

Views: 5954

Answers (1)

SolarBear
SolarBear

Reputation: 4629

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

Upvotes: 1

Related Questions