Group by its count from datatable in C#

Question

I am new to linq.

I have a datatable and need to update a column according to another column using linq.

My value

customer | value1  |  value2  | count
---------------------------------------- 
   A     |   sda   |  sdas    |  0
   A     |   sda   |  sdas    |  0
   B     |   sda   |  sdas    |  0
   B     |   sda   |  sdas    |  0
   B     |   sda   |  sdas    |  0
   C     |   sda   |  sdas    |  0

Expected Value

customer | value1  |  value2  | count
---------------------------------------- 
   A     |   sda   |  sdas    |  2
   A     |   sda   |  sdas    |  2
   B     |   sda   |  sdas    |  3
   B     |   sda   |  sdas    |  3
   B     |   sda   |  sdas    |  3
   C     |   sda   |  sdas    |  1

Could you please suggest me the required linq for the above datatable?

So what have you tried so far? Please, show us your code and explain where did you get stuck. — Selim Yildiz, Oct 11 '20 at 16:21
https://stackoverflow.com/questions/5231845/c-sharp-linq-group-by-on-multiple-columns — urlreader, Oct 11 '20 at 16:26
`I have a datatable and need to update a column according to another column using linq` how is this `DataTable` being filled, is it bound to a source? Could you please update your post to include what you've tried and what isn't working? — Trevor, Oct 11 '20 at 16:36

score 0 · Accepted Answer · edited Oct 12 '20 at 04:33

You did not provide your code but I think you are looking for a something like this:(Assuming your DataTable is called 'dt')

foreach(DataRow dr in dt.Rows) // loop whole table
{
   //Find count of rows which has same value
    var count = dt.AsEnumerable().Where(i=> i["customer"] == dr["customer"] && i["value1"] == dr["value1"] && i["value2"] == dr["value2"]).Count();
    
    /Then update count column
    dr["count"] = count;
}

Another approach you can follow is that GroupBy multiple columns in datatable.

 dt.AsEnumerable()
    .GroupBy(g => new {Customer = g["customer"], Value1 = g["value1"], Value2= g["value2"]})
    .Select(g=> 
    {
        var newRow = dtNew.NewRow();
        
        row["customer"] = g.Key.Customer;
        row["value1"] = g.Key.Value1;
        row["value2"] = g.Key.Value2;
        row["count"] = g.Count();
        
        return newRow;
    })
    .CopyToDataTable();

See: How do I use SELECT GROUP BY in DataTable.Select(Expression)?

Group by its count from datatable in C#

1 Answers1