How to select the most repeating records in related tables with Entity Framework-CodePudding

My tables here :

In C#

I want to know most repeated 3 categoryName and their count in Blog table, any idea? Thanks

CodePudding user response：

list.GroupBy(x => x.CategoryName).Select(x => new { x.Key, count = x.Count() }).OrderBy(x => x.count).Take(3);

This will first Group the items by the name. Then create an anonymous object with the group key (the name) and the Count of all items in every group. Then you order by count and take the first 3.

CodePudding user response：

You could group by category name, order by highest count first and pick the first three results. Example:

Blogs
    .GroupBy(b => b.Category.CategoryName) 
    .OrderByDescending(g => g.Count())
    .Take(3)
    .Select(x => new { CategoryName = x.Key, Count = x.Count() });

CodePudding user response：

var result = blogs.GroupBy(b => b.CategoryID)
            .OrderByDescending(g => g.Count())
            .Take(3)
            .Select(x => new {CategoryName = x.First().Category.CategoryName, Count = x.Count()})
            .ToList();

This will group your blogs by CategoryID, order by count of each grouping, take the top 3 and then select the category name and count of each group as a list.