Search code examples
linqgroup-bydatatablehaving

c# linq Query on datatable groupby having count

I have a datatable and I need to perform the following query on it (sql):

SELECT C1, C2, C3, count(*)
FROM MyTable
group by C1, C2, C3
having COUNT (*) > 1

Can you point me to some code, in order to achieve this? Thanks

Solution

  • It's a simple LINQ query with GroupBy:

    var query = table.AsEnumerable()
   .GroupBy(r => (C1:r.Field<string>("C1"), C2:r.Field<string>("C2"), C3:r.Field<string>("C3")))
   .Where(g => g.Count() > 1)
   .Select(g => (g.Key.C1, g.Key.C2, g.Key.C3, Count: g.Count()));

    If you want to add it to a new DataTable:

    DataTable resultTable = new();
resultTable.Columns.Add("C1");
resultTable.Columns.Add("C2");
resultTable.Columns.Add("C3");
resultTable.Columns.Add("Count", typeof(int));

foreach(var x in query)
{
    resultTable.Rows.Add(x.C1, x.C2, x.C3, x.Count);
}