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

user_2290217 · Answer

Most of the other answers to this, which properly set the running totals within the objects, rely on a side-effect variable, which is not in the spirit of functional coding and the likes of .Aggregate(). This solution eliminates the side-effect variable.

(NB - This solution will run on the client as with other answers, and so may not be optimal for what you require.)

var results = FetchDataFromDatabase()
    .OrderBy(item => item.Date)
    .Aggregate(new List(), (list, i) =>
        {
            var item = new Item
            {
                Amount = i.Amount,
                Date = i.Date,
                RunningTotal = i.Amount + (list.LastOrDefault()?.RunningTotal ?? 0)
            };

            return list.Append(item).ToList();
            // Or, possibly more efficient:
            // list.Add(item);
            // return list;
        });

LINQ to SQL and a running total on ordered results

6 Answers