Calculate total of a column using linq in c#

Question

I have a structure like

ID  Description Principal   Interest    Total
123 Paid        100          150        250
    Balance     50           50         100
    Total        ?            ?           ?
124 Paid        100          150        250
    Balance     50           50         100
    Total        ?            ?           ?

Class :

public class APIBillingHistory
{
    public List<APIBillingHistoryDetails> BillingHistoryDetails;
}

public class APIBillingHistoryDetails
{
    public string BillId;
    public string Description;
    public Decimal Principal;
    public Decimal Interest;
    public Decimal Total;
}

I would like to sum value for each column for each id. So in above example, Total for ID 123 for Principal would be 150, Interest 200 likewise. I checked this solution over here How to create Total column and Total row using LINQ but not able to get it.

Code:

responseObj = new APIBillingHistory
{
    BillingHistoryDetails = billingHistory.BillingHistoryDetails
            .GroupBy(detail => new { detail.BillId, detail.Description})
            .Select(group => new APIBillingHistoryDetails
            {
                BillId = group.Key.BillId,
                Description = group.Key.Description,
                Principal = group.Sum(t => t.Principal),
                Interest = group.Sum(t => t.Interest),
                Total = group.Sum(t => t.Principal) + group.Sum(t => t.Interest)
            }).Concat(new APIBillingHistoryDetails
            {
                Description = "Total",
                /*  Not sure what to write over here */
            })
};

Edit:

To clarify what I am trying to achieve:

My source record list does not contain "Total" column & "Total" row. I would like to add it manually.
I Was able to figure out how to Add "Total" column which would contain Sum of values.
Principal & Interest will contain values in decimal format.
ID column is an integer. I would like to Display Total row for each ID
Values for Principal & Interest are being calculated by aggregating based on ID, After that calculation, only Total row should get displayed.

Any Suggestions?

What does the ID column equals for the second and third rows? How can a `Decimal` be a `?` ? Do you want to sum `Total` or did you mean the 'Total' row isn't in your source data? — NetMage, Jun 26 '17 at 23:22
Also, does your source data already include the `Total` field for each row, or does that have to be added as well? — NetMage, Jun 26 '17 at 23:32
I'd like to recommend using a tool called LinqPad. It's like using Sql Server Management Studio but on C# entities. Allows you to visualise the results of you LINQ expressions and really get to understand what's going on. — Stephen York, Jun 27 '17 at 04:25

score 1 · Answer 1 · answered Jun 26 '17 at 23:37

1

You can't use Concat and reference previous data. You need to use SelectMany:

BillingHistoryDetails = billingHistory.BillingHistoryDetails
    .GroupBy(detail => detail.BillId)
    .SelectMany(bg => bg.Concat(new[] { new APIBillingHistoryDetails {
        BillId = bg.First().BillId,
        Description = "Total",
        Principal = bg.Sum(b => b.Principal),
        Interest = bg.Sum(b => b.Interest),
        Total = bg.Sum(b => b.Total)
    } }));

answered Jun 26 '17 at 23:37

NetMage

26,163
3
34
55

I can't avoid `Select` as I am doing aggregate on `Principal` and `Interest` group by `ID`. After doing that aggregate I would like to calculate the total for each column. – Shaggy Jun 27 '17 at 16:53
That is not evident in your question. – NetMage Jun 27 '17 at 17:51
I think it needs better editing; for example, LINQ doesn't "display" anything. – NetMage Jun 27 '17 at 19:51

Nkosi · Accepted Answer · 2017-06-28T02:42:38.233

Assuming the current data

BillId Description Principal   Interest
123    Paid        100          150    
123    Balance     50           50     
124    Paid        100          150     
124    Balance     50           50

Then just group by the identity, creating the total object by summing up the grouped properties setting the description as "Total".

var totalsObj = new APIBillingHistory {
    BillingHistoryDetails = billingHistory.BillingHistoryDetails
           .GroupBy(detail => detail.BillId)
           .Select(g => new APIBillingHistoryDetails {
             BillId = g.Key,
             Description = "Total",
             Principal = g.Sum(detail => detail.Principal),
             Interest = g.Sum(detail => detail.Interest),
             Total = g.Sum(detail => detail.Principal) + g.Sum(detail => detail.Interest)
           }).ToList()
};

and totalsObj.BillingHistoryDetails would have desired out put

After that calculation, only Total row should get displayed.

BillId Description Principal   Interest    Total
123    Total       150         200         350
124    Total       150         200         350

Calculate total of a column using linq in c#

2 Answers2