ASP Net Core Web API: Client side GroupBy is not supported

Question

I am new here, and a newbie in asp.net core.

I am writing a web api using asp.net core 3.0. I wrote the following API endpoint code to retrieve a list of Databases entities and group them by a field named Value from another object Type.

//GET:api/Databases/graph
[HttpGet("graph")]
public async Task<ActionResult<IEnumerable<IGrouping<string, Database>>>> GetGraphDatabases()
{
    return await _context.Databases
        .Include(t => t.Type)
        .Where(d => d.Type.Code == "DATABASE_TYPE")
        .GroupBy(d => d.Type.Value)
        .ToListAsync();
}

But when I try to reach the endpoint, I get the following error message: InvalidOperationException: Client side GroupBy is not supported.

I checked this answer but it didn't help much.

Can you please support.

Answer 1

Shortly, this type of GroupBy is not supported in EF Core 3.0.

You are expected to explicitly switch to client evaluation before that operator. Which for sync version is as simple as inserting .AsEnumerable(). For async version it's not that easy - there is AsAsyncEnumerable() method, but then you need additional package to get GroupBy and other LINQ operators available.

Probably the easiest way to resolve it (with the cost of one additional list in memory) is to use ToListAsync() to complete the async part and then use the regular LINQ to Objects for the rest.

e.g. something like this

return (await _context.Databases
    .Include(t => t.Type)
    .Where(d => d.Type.Code == "DATABASE_TYPE")
    .ToListAsync())
    .GroupBy(d => d.Type.Value);

---

The additional package I'm talking about is System.Linq.Async. For more info, see Converting EF Core queries from 2.2 to 3.0 - async await.