How to take top 3 elements in each group using Entity Framework-CodePudding

I have the data in the source table.

And I want to get this kind of result in list using lambda expression and Entity Framework. I don't know how to do it. I need to get top 3 rows in for each CategoryId.

Probably using something like this:

context.GroupBy(x => x.CategoryId)
       .Select(group => new { 
                                CategoryId = group.Key, 
                                NameId = group.Select(x => x.NameId), 
                                group.OrderByDescending(e => e.Count).Take(3)
                            })
       .ToListAsync()

CodePudding user response：

var list = context
    .GroupBy(x => x.CategoryId)
    .SelectMany(group => group.OrderByDescending(e => e.Count).Take(3))
    .OrderByDescending(e => e.Count)
    .ToListAsync();

If you want an anonymous type:

var list = context
    .GroupBy(x => x.CategoryId)
    .SelectMany(group => group.OrderByDescending(e => e.Count).Take(3))
    .Select(x => new
    {
        x.CategoryId, 
        x.NameId, 
        x.Count
    })
    .OrderByDescending(x => x.Count)
    .ToListAsync();