c# - LINQ to SQL and a running total on ordered results

Question

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

question from:https://stackoverflow.com/questions/1834753/linq-to-sql-and-a-running-total-on-ordered-results

Answer 1

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
}