Ecyrb Ecyrb - 1 year ago 77
C# Question

LINQ to SQL and a running total on ordered results

I want to display a customer's accounting history in a

DataGridView
and I want to have a column that displays the running total for their balance. The old way I did this was by getting the data, looping through the data, and adding rows to the
DataGridView
one-by-one and calculating the running total at that time. Lame. I would much rather use LINQ to SQL, or LINQ if not possible with LINQ to SQL, to figure out the running totals so I can just set
DataGridView.DataSource
to my data.

This is a super-simplified example of what I'm shooting for. Say I have the following class.

class Item
{
public DateTime Date { get; set; }
public decimal Amount { get; set; }
public decimal RunningTotal { get; set; }
}


I would like a L2S, or LINQ, statement that could generate results that look like this:

Date Amount RunningTotal
12-01-2009 5 5
12-02-2009 -5 0
12-02-2009 10 10
12-03-2009 5 15
12-04-2009 -15 0


Notice that there can be multiple items with the same date (12-02-2009). The results should be sorted by date before the running totals are calculated. I'm guessing this means I'll need two statements, one to get the data and sort it and a second to perform the running total calculation.

I was hoping
Aggregate
would do the trick, but it doesn't work like I was hoping. Or maybe I just couldn't figure it out.

This question seemed to be going after the same thing I wanted, but I don't see how the accepted/only answer solves my problem.

Any ideas on how to pull this off?

Edit
Combing the answers from Alex and DOK, this is what I ended up with:

decimal runningTotal = 0;
var results = FetchDataFromDatabase()
.OrderBy(item => item.Date)
.Select(item => new Item
{
Amount = item.Amount,
Date = item.Date,
RunningTotal = runningTotal += item.Amount
});

Answer Source

Using closures and anonymous method:

List<Item> myList = FetchDataFromDatabase();

decimal currentTotal = 0;
var query = myList
               .OrderBy(i => i.Date)
               .Select(i => 
                           {
                             currentTotal += i.Amount;
                             return new { 
                                            Date = i.Date, 
                                            Amount = i.Amount, 
                                            RunningTotal = currentTotal 
                                        };
                           }
                      );
foreach (var item in query)
{
    //do with item
}
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download