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

timcbaoth · Accepted Answer · 2016-03-30T22:21:22.177

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")]);
}

score 2 · Answer 2 · answered Mar 30 '16 at 21:43

2

Thank you very much timcbaoth, yes I agree with what you said. I tried to upvote your post but the system says I may not :-(
I had figured it out (below) using brute force, but I will evaluate your solution as well! Thanks again!!

        var query = from row in rpTable.AsEnumerable()
                    group row by int.Parse(row.Field<string>("Account")) into grp
                    orderby grp.Key
                    select new
                    {
                        Id = grp.Key,
                        Sum = grp.Sum(r => r.Field<decimal>("Payment"))
                    };
        foreach (var grp in query)
        {
            rpTable.Select("Account ="+grp.Id).ToList<DataRow>().ForEach(r=>r["Payment"] = grp.Sum);
        }

answered Mar 30 '16 at 21:43

actinade

55
8

Great you figured it out! Your approach seems to be (almost) the same as mine. I could not find specifics about it on msdn but I would believe rpTable.Select has a linear runtime, which would mean that using a dictionary as in my approach you might get some constant factor speedup. But as I said that also may not be the case. – timcbaoth Mar 30 '16 at 22:14
Btw. just a small remark: You may also use interpolated strings: $"Account = {grp.Id}" This could make the statement more readable. https://msdn.microsoft.com/en-us/library/dn961160.aspx – timcbaoth Mar 30 '16 at 22:17
Thanks for the remarks. I really appreciate your help! – actinade Mar 30 '16 at 22:30

score -1 · Answer 3 · answered Apr 08 '16 at 19:20

-1

I found an insidious little bug, so FYI:

OLD: rpTable.Select("Account ="+grp.Id).ToList<DataRow>().ForEach(r=>r["Payment"] = grp.Sum);   
NEW: rpTable.Select("Account ='"+ grp.Id +"'").ToList<DataRow>().ForEach(r => r["Payment"] = grp.Sum);

answered Apr 08 '16 at 19:20

actinade

55
8

I'm not the downvoter, just for the record, but I *think* you can edit your own answer. Then clear this one and gain back a bit. – R.D. Alkire Aug 26 '20 at 00:55

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

3 Answers3