I have a table like below,

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,

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

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

CodePudding user response：

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; }
}

CodePudding user response：

Maybe 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().