Average of the difference of two DateTime objects with GroupBy in LINQ query

Question

I am developing an ASP.NET Core 3.1 project. I want to group Services in Tickets based on the average of the difference between CreatedTime and ModifiedTime of Tickets.

Basically, I want to know the average minutes it takes for a ticket, of every type of service, to close.

Something like this:

==========================
 Name        |  Avg. Mins
==========================

 Service 1   |   10 Mins
 Service 2   |   12 Mins
 Service 3   |   20 Mins

==========================

Here is what I have tried:

var d = _dbcontext.Tickets.Include(m => m.Services)
           .Where(m => m.ServiceId != null)
           .GroupBy(t => new { Name = t.Services.Name })
           .Select(m => new { x = m.Key.Name , y = m.Average(p => (p.CreatedDate - p.ModifiedDate).Minutes) })
           .ToList();

But I am getting this error:

InvalidOperationException: The LINQ expression '((EntityShaperExpression: EntityType: Tickets ValueBufferExpression: (ProjectionBindingExpression: EmptyProjectionMember) IsNullable: False ).CreatedDate - (EntityShaperExpression: EntityType: Tickets ValueBufferExpression: (ProjectionBindingExpression: EmptyProjectionMember) IsNullable: False ).ModifiedDate).Minutes' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync().

I tried AsEnumerable() after GroupBy but the GroupBy cannot execute on client-side it seems. Need a good way to solve it.

Does this answer your question? [Entityframework Core 3 linq expression could not be translated](https://stackoverflow.com/questions/59401492/entityframework-core-3-linq-expression-could-not-be-translated) — Jamshaid K., Nov 04 '20 at 21:18
Have you tried my code? If you encounter the same problem again in the future, you can use this method to solve it. — Yinqiu, Nov 05 '20 at 06:47
@Yinqiu I have tried your code and it solved my problem. I am very thankful to you. But the usage of AsEnumerable is not desirable in my scenario. Hence I accepted the other answer. Thanks again. — Junaid, Nov 05 '20 at 06:53

score 1 · Accepted Answer · answered Nov 04 '20 at 21:50

You can use server-side function DateDiffMinute:

var d = _dbcontext.Tickets
   .Where(m => m.ServiceId != null)
   .GroupBy(t => new { Name = t.Services.Name })
   .Select(m => new { 
       x = m.Key.Name, 
       y = m.Average(p => EF.DbFunctions.DateDiffMinute(p.CreatedDate, p.ModifiedDate)) 
    })
   .ToList();

score 1 · Answer 2 · answered Nov 05 '20 at 03:00

You get this error because starting with 3.0, EF Core only allows expressions in the top-level projection (the last Select() call in the query) to be evaluated on the client.

When expressions in any other part of the query can't be converted to either SQL or a parameter, an exception is thrown.

More details you can see here.

In your code,AsEnumerable()should be used before GroupBy,like this:

 var d = _dbcontext.Tickets.Include(m => m.Service)
     .Where(m => m.ServiceId != null).AsEnumerable()
     .GroupBy(t => new { Name = t.Service.Name })
     .Select(m => new { x = m.Key.Name, y = m.Average(p => (p.CreatedDate - p.ModifiedDate).Minutes) })
     .ToList();

Result:

Average of the difference of two DateTime objects with GroupBy in LINQ query

2 Answers2