Group By Count - Iteration Count LINQ Query

Question

I have a table like below,

Name	Value
A	Sample1
A	Sample2
A	Sample3
B	Sample3
B	Sample1
C	Sample2
C	Sample3
D	Sample1

If I group the table by Name to get the count,

Select Name, Count(*) as count from table group by Name;

I will get the following result,

Name	count
A	3
B	2
C	2
D	1

I need to get the number of repetitions of each count. Means desired outcome below,

count	numberOfTimes
3	1
2	2
1	1

I know the sql query would be

SELECT DISTINCT COUNT(*) AS count,
   COUNT(*) OVER (PARTITION BY COUNT(*)) AS numberOfTimes FROM tablename GROUP BY Name;

But I am not sure how to write this in LINQ

Answer 1

Actually a double grouping gives the intended result.

var allCombinedVersion = table
    .GroupBy(e => e.Name)
    .Select(g => new { Name = g.Key, Count = g.Count() })
    .GroupBy(e => e.Count)
    .Select(g => new { GroupedCount = g.Key, Count = g.Count() }).ToList();

You can test it from here. Explained version,

var table = new List<Table>
{
    new() { Name = "A", Value = "Sample1" },
    new() { Name = "A", Value = "Sample2" },
    new() { Name = "A", Value = "Sample3" },
    new() { Name = "B", Value = "Sample3" },
    new() { Name = "B", Value = "Sample1" },
    new() { Name = "C", Value = "Sample2" },
    new() { Name = "C", Value = "Sample3" },
    new() { Name = "D", Value = "Sample1" }
};

var groupNames = table.GroupBy(e => e.Name)
    .Select(g => new { Name = g.Key, Count = g.Count() }).ToList();

foreach (var line in groupNames)
{
    Console.WriteLine($"{line.Name} {line.Count}");
}

var groupCounts = groupNames.GroupBy(e => e.Count)
    .Select(g => new { GroupedCount = g.Key, Count = g.Count() }).ToList();

foreach (var line in groupCounts)
{
    Console.WriteLine($"{line.GroupedCount} {line.Count}");
}

internal class Table
{
    public string Name { get; set; }
    public string Value { get; set; }
}

Answer 2

You can use:

var nameGroups = table.GroupBy(x => x.Name);
var resultCountList = nameGroups
    .Select(g => (
        count: g.Count(), 
        numberOfTimes: nameGroups.Count(ng => g.Count() == ng.Count())))
    .Distinct()
    .ToList();

Demo: https://dotnetfiddle.net/tnW9P5

The nameGroups is just a db-query, so the second query is still using the database until ToList().