Linq exception: this function can only be called from LINQ to Entities

Question

I am trying to get data stored in cache. But it throws an exception in the line "select new FilterSsrsLog". Exception: this function can only be called from LINQ to Entities.

List<ExecutionLog3> reportServerDB = UpdateCache(); var reportLog = (from r in reportServerDB orderby r.TimeStart descending where ((model.reportName == null ? true : r.ItemPath.Contains(model.reportName)) && (model.reportFolder == null ? true : r.ItemPath.Contains(model.reportFolder)) && (r.TimeStart >= startDateTime) && (r.TimeStart <= endDateTime) ) select new FilterSsrsLog { UserName = r.UserName, ReportName = r.ItemPath, ReportFolder = r.ItemPath, Format = r.Format, Parameters = r.Parameters, TimeStart = r.TimeStart, TimeEnd = r.TimeEnd, TotalTime = EntityFunctions.DiffMilliseconds(r.TimeStart, r.TimeEnd) }); 

If I remove the "select new FilterSsrsLog" block and write "select r", it will work. But I only need this coloumns, so what can I do to solve this problem?

Answer 1

The reason you get this error is because the query is executed in memory, not in the RDBMS. The DiffMilliseconds function is a token that the Entity Framework provider converts to RDBMS-specific SQL code to be sent to your DBMS. The function does not calculate its result when applied to an IQueryable<T> in memory, instead IQueryable<T> exception.

If you want to run this query in memory, replace

 TotalTime = EntityFunctions.DiffMilliseconds(r.TimeStart, r.TimeEnd) 

with

 TotalTime = (r.TimeEnd - r.TimeStart).TotalMilliseconds 

Subtracting two dates gives the TimeSpan value from which you can take TotalMilliseconds .