Linq Exception: Function can only be invoked from linq to entities

Question

I have a StudentReceipts table which stores ReceiptNo as string(001,002,003,..,099,..).

I want go get the last receiptno details inorder to increment the receiptno for next transaction.

This is what I have tried

  var _lastGeneratedRecDetails = _db.StudentReceipts
                                 .AsEnumerable()
                                 .Where(r => r.Status == true
                                             && EntityFunctions.TruncateTime(r.DueDate.Value) >= _startDate.Date
                                             && EntityFunctions.TruncateTime(r.DueDate.Value) <= _endDate.Date)                                                
                                            .OrderByDescending(x => Int32.Parse(x.ReceiptNo))
                                            .FirstOrDefault();

But there i am getting the following exception

this function can only be invoked from linq to entities

Any help will be highly appreciated.

Solution 1

By calling .AsEnumerable() you are going from Linq-To-Entities to Linq-To-Object. By calling it, you are also filtering all the results in memory, so you are pulling the whole StudentReceipts table from the database everytime you do that query as it gets executed past the .AsEnumerable() method. The general rule is to try to do as much as you can on the database side:

var _lastGeneratedRecDetails = 
   _db.StudentReceipts.Where(r => r.Status == true
                       && EntityFunctions.TruncateTime(r.DueDate.Value) >= _startDate.Date
                       && EntityFunctions.TruncateTime(r.DueDate.Value) <= _endDate.Date)             
                      .AsEnumerable()                                   
                      .OrderByDescending(x => Int32.Parse(x.ReceiptNo))
                      .FirstOrDefault();

If you do it like this, you will filter everything in the database and fetch the filtered results. I don't know what type x.ReceiptNo is though, but calling Int.Parse isn't allowed in Linq-To-Entities. You can filter first and then call AsEnumerable to be able to do the parsing and ordering in memory.

Solution 2

In my case, I was re-using a Func / Filter expression that included DbFunctions.TruncateTime in a follow-up processing statement AFTER I had already processed the query in SQL. Removing it cleared the instance of the exception for me.

Solution 3

use and .AsQueryable()

var _lastGeneratedRecDetails = _db.StudentReceipts
                             .AsEnumerable().AsQueryable()

Comments

[ksg]

Thanks for the reply mate. But when i tried the above query i'm getting error Value cannot be null.When I changed .OrderByDescending(x => Int32.Parse(x.ReceiptNo)) to .OrderByDescending(x => x.ReceiptNo) the query works.But still has doubt on how descending works on string values

[Alexander Derck]

@ksg You can try OrderByDescending(x => x.ReceiptNo != null ? Int32.Parse(x.ReceiptNo) : 0) with '0' as default value if it's null

[ksg]

Thanks mate it works.Forget that there could be a possibility of null .:)