Linq syntax to find subtotal of grouped rows in a DataTable and update the DataTable

Question

I have been scratching my head about this one... I have a table in memory, a DataTable structured like this:

Input:
   ID   |   Invoice | Account | Payment | Subtotal
-----------------------------------------------------------------
    0    |   09310   |    123    |    6.0    |    ?
-----------------------------------------------------------------

And I wish to use Linq to perform the following (I am pretty new to Linq!)

For each row that is for the same account, add together all the payments and write or update the subtotal field(s) I do not want to collapse the table, the invoice numbers are going to be different. My thought is that there are two ways that this can be done

(A) start with a BLANK subtotal column for all records... the values from payment would be added together and then written into the subtotal column

(B) when table created, I DUPLICATE the payment values into the subtotal fields. Later, the linq would only have to add/replace the values in the same column

So we would ignore the ID and Invoice fields; its ACCOUNT and SUBTOTAL (and PAYMENT as well if using style (A))

(A) Input: *(note that there are two records for 123)*
   ID   |   Invoice | Account | Payment | Subtotal
-----------------------------------------------------------------
    0    |   03310   |    123    |    6.0    |    
-----------------------------------------------------------------
    1    |   09728   |    123    |    4.0    |    
-----------------------------------------------------------------
    2    |   07731   |    559    |    18.0   |    
-----------------------------------------------------------------

 

(B) Input:
   ID   |   Invoice | Account | Payment | Subtotal
-----------------------------------------------------------------
    0    |   03310   |    123    |    6.0    |    6.0
-----------------------------------------------------------------
    1    |   09728   |    123    |    4.0    |    4.0
-----------------------------------------------------------------
    2    |   07731   |    559    |    18.0   |    18.0
-----------------------------------------------------------------

 

Result:
   ID   |   Invoice | Account | Payment | Subtotal
-----------------------------------------------------------------
    0    |   03310   |    123    |    6.0    |    10.0
-----------------------------------------------------------------
    1    |   09728   |    123    |    4.0    |    10.0
-----------------------------------------------------------------
    2    |   07731   |    559    |    18.0   |    18.0
-----------------------------------------------------------------

And so, each Subtotal cell would have the total of all PAYMENTS for each unique ACCOUNT

I am thinking that style (B) would be easier because we only have to deal with those two columns

For style (B), I have tried something like

rpTable.AsEnumerable().GroupBy(g => int.Parse(g.Field<string>("Account"))).Select(g => g.Sum(p => p.Field<decimal>("SubTotal")));

but I can tell its missing something.....hmmmm

Answer 1

By using Select, you will not update the table. This just returns an IEnumerable of the selected values.

What you want to do is add the column to the table and then fill it:

var subTotalByAccount = table.AsEnumerable()
            .GroupBy(g => g.Field<string>("Account"))
            .Select(g => new { Account = g.Key, SubTotal = g.Sum(p => p.Field<decimal>("Payment")) })
            .ToDictionary(t => t.Account, t => t.SubTotal);

table.Columns.Add("SubTotal", typeof(decimal));
foreach (var row in table.AsEnumerable())
{
            row.SetField(columnName: "SubTotal", value: subTotalByAccount[row.Field<string>("Account")]);
}