Column Summing using Linq

Question

My requirement is merging of rows where ID's are same with same values in each columns and summing up its Amount and Percentage columns.

Datatable example as given bellow -

Datatable (dtrec)

 COL1   COL2  COL3     ID     AMT   PERCENTAGE  COL4
-----------------------------------------------------
 2022   2024   2021   5001    400     .20     213
 2022   2024   2021   5001    400     .20
 2031   2021   2134   6001    500     .20

Expected Result -

Desired Output - Datatable (dtrec) 
COL1  COL2   COL3  ID   AMT  PERCENTAGE  COL4
 ----------------------------------------------------- 
2022  2024  2021  5001  800   .40         213 
2031  2021  2134  6001  500   .20

By using bellow linq statement i am receiving an output like bellow -

DataTable dt = dtrec.AsEnumerable()
                .GroupBy(r => r.Field<decimal>("ID"))
                 .Select(g =>
                 {
                       var row = dtrec.NewRow();
                       row["ID"] = g.Key;
                       row["AMT"] = g.Sum(r => r.Field<decimal>("AMT"));
                       row["PERCENTAGE"] = g.Sum(r => r.Field<decimal>("PERCENTAGE"));
                       return row;
                 }).CopyToDataTable();

Datatable (dt)
COL1  COL2   COL3  ID   AMT  PERCENTAGE  COL4
 ----------------------------------------------------- 
                    5001  800   .40         
                    6001  500   .20

As mentioned above my requirement is that, a linq statement, that would select all columns from datatable and additionally it would be summing up AMT and PERCENTAGE columns. As shown in desired result.

Another important point to consider that apart from ID, AMT and PERCENTAGE columns other columns are dynamic i.e. columns might increase or decrease (e.g. COL1, COL2, COL3, COL4, COl5.... etc) hence we cannot include COL1 or COL4 inside select statement.

Any suggestion would be helpful.

Thanks

Yeah, since in your query you don't seem to use COL1 or COL4 they won't be in the result. — Sach
Thanks Sach, COL1 and COL4 can be included in the select statement, but for instance if column COL2 or COL3 contains values then i have to put other columns e.g COL2 and COL3 as well. Hence need some other way round to do it. — aditya mukherjee

Sach Sach · Accepted Answer · 2019-06-10T21:55:24

Simply add the columns you need summed to your query.

DataTable dt = dtrec.AsEnumerable()
    .GroupBy(r => r.Field<decimal>("ID"))
     .Select(g =>
     {
         var row = dtrec.NewRow();
         row["COL1"] = g.Sum(r => r.Field<decimal>("COL1"));
         row["ID"] = g.Key;
         row["AMT"] = g.Sum(r => r.Field<decimal>("AMT"));
         row["PERCENTAGE"] = g.Sum(r => r.Field<decimal>("PERCENTAGE"));
         row["COL4"] = g.Sum(r => r.Field<decimal>("COL4"));
         return row;
     }).CopyToDataTable();

Column Summing using Linq

1 Answers