Dynamic pivot using linq

See more:

C#

LINQ

entity-framework

I have been struggling to pivot a table dynamically in Entity Framework for 2 weeks now.

What I have tried:

I've been able to pivot a SQL table using the latest Entity Framework using static headers, hardcoding the columns, using the following: 

<pre lang="C#">
<pre>var query = _context.Categories
            .SelectMany(c => c.StaticEvents )
            .GroupBy(c => new {c.Time.Month})
            .Select(g => new {
                Month = g.Key.Month,
                Baralho = g.Where(c => c.CategoryId == 3).Count(),
                Coffee = g.Where(c => c.CategoryId == 1).Count(),
                Water = g.Where(c => c.CategoryId == 2).Count(),
                Tea = g.Where(c => c.CategoryId == 4).Count(),
            }).ToList();

When I try to go a step further and create the headers dynamically with the following:

var query = _context.Categories
            .SelectMany(c => c.StaticEvents )
            .GroupBy(c => new {c.Time.Month})
            .Select(g => new {
                Month = g.Key.Month,
                category = g.GroupBy(f => f.CategoryId).Select(m => new { Sub = m.Key, Score = m.Count()})
            }).ToList();

I get this error:

.GroupBy(f => f.CategoryId)' 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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Any ideas on how to achieve this dynamically?