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

asp.net-mvc-4 c# entity-framework linq

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.

1
7
3/25/2016 8:38:29 AM

Accepted Answer

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.

12
3/25/2016 8:49:26 AM

Popular Answer

use and .AsQueryable()

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


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow