linq exception : This function can only be invoked from LINQ to Entities

Question

I'm trying to get data which is kept on cache. But it throws an exception on "select new FilterSsrsLog" line. Exception: This function can only be invoked 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 "select new FilterSsrsLog" code block and write "select r" it works. But i need only that coloumns so what can i do to solve this problem?

Answer 1

The reason you are getting this error is that the query is executed in memory, not in RDBMS. The DiffMilliseconds function is a marker that Entity Framework provider converts to RDBMS-specific SQL to send to your RDBMS. The function does not compute its result when applied to an IQueryable<T> in memory, throwing an exception instead.

If you want to run this query in memory, replace

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

with

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

Subtraction of two dates produces a TimeSpan value from which you can take its TotalMilliseconds property.