CODEWITHSUNDEEP
c#entity-frameworklinqasp.net-mvc-4
ksg
ksg

Reputation: 4067

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

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.

Upvotes: 8

Views: 27291

Answers (4)

bergerb
bergerb

Reputation: 116

I was wondering why that error occurs, I remembered some DbFunctions working completely fine. The reason is, that not all DbFunctions are implemented in C# code!

If you look at the "implementation" of TruncateTime
https://github.com/dotnet/ef6/blob/255a32450db09a2feb07cb6199af1dcdfa189b8d/src/EntityFramework/DbFunctions.cs#L655

[DbFunction("Edm", "TruncateTime")]
public static DateTime? TruncateTime(DateTime? dateValue)
{
    throw new NotSupportedException(Strings.ELinq_DbFunctionDirectCall);
}

You can see why that error is thrown. It's just not implemented.

The code above basically says: execute the SQL-Function TruncateTime, if used in LINQ to Entities, and run the code (in this case: throw Exception), if used in LINQ to Objects

You can implement your own Method, just copy the code above in your own class, an replace your DbFunctions.TruncateTime(.) call with MyFunctions.TruncateTime(.)

That was really annoying to find. I needed this because sometimes the query will get run against the database, and sometimes only in code..

Of course, if you will only need to call that function against the database, you are completely fine with the solutions above

Upvotes: 0

Boyardee
Boyardee

Reputation: 324

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.

Upvotes: 3

Piotr
Piotr

Reputation: 11

use and .AsQueryable()

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

Upvotes: 1

Alexander Derck
Alexander Derck

Reputation: 14488

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.

Upvotes: 13

Related Questions