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

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.

Contents hide

Answers:

Method 1

Method 2

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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();

Method 2

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:

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating