Click here to Skip to main content
15,310,056 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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(),

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

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 for more information.

Any ideas on how to achieve this dynamically?
Updated 14-Jun-21 3:47am

1 solution

EF Core's GroupBy support is notoriously bad.

Something like this might work:
    .SelectMany(c => c.StaticEvents)
    .GroupBy(c => c.Time.Month, (month, items) => new {
        Month = month,
        Categories = items.GroupBy(i => i.CategoryId, (key, m) => new { Sub = key, Score = m.Count() })
However, given the large number of GroupBy issues logged on the EF Core GitHub repo, there are no guarantees.
Issues · dotnet/efcore · GitHub[^]

You might end up having to execute a raw SQL query instead.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900