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

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 }