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
}

Answer 2

How about this: (credit goes to this source)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication1
{
    class Program
    {
        delegate string CreateGroupingDelegate(int i);

        static void Main(string[] args)
        {
            List<int> list = new List<int>() { 1, 2, 3, 4, 5, 7, 8, 9, 10, 11, 12, 13, 69, 2007};
            int running_total = 0;

            var result_set =
                from x in list
                select new
                {
                    num = x,
                    running_total = (running_total = running_total + x)
                };

            foreach (var v in result_set)
            {
                Console.WriteLine( "list element: {0}, total so far: {1}",
                    v.num,
                    v.running_total);
            }

            Console.ReadLine();
        }
    }
}

Answer 3

In case this hasn't been answered yet, I have a solution that I have been using in my projects. This is pretty similar to an Oracle partitioned group. The key is to have the where clause in the running total match the orig list, then group it by date.

var itemList = GetItemsFromDBYadaYadaYada();

var withRuningTotals = from i in itemList    
                       select i.Date, i.Amount,    
                              Runningtotal = itemList.Where( x=> x.Date == i.Date).
                                                      GroupBy(x=> x.Date).
                                                      Select(DateGroup=> DateGroup.Sum(x=> x.Amount)).Single();

Answer 4

Aggregate can be used to obtain a running total as well:

var src = new [] { 1, 4, 3, 2 };
var running = src.Aggregate(new List<int>(), (a, i) => {
    a.Add(a.Count == 0 ? i : a.Last() + i);
    return a;
});

Answer 5

using System;
using System.Linq;
using System.Collections.Generic;

public class Program
{
    public static void Main()
    {
        var list = new List<int>{1, 5, 4, 6, 8, 11, 3, 12};

        int running_total = 0;

        list.ForEach(x=> Console.WriteLine(running_total = x+running_total));
    }
}

Answer 6

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<Item>(), (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;
        });