LINQ to SQL和有序结果的运行总计

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.

我想在DataGridView中显示客户的会计历史记录，我希望有一个列显示其余额的运行总计。我这样做的旧方法是获取数据，循环遍历数据，然后逐行向DataGridView添加行并计算当时的运行总计。瘸。我更倾向于使用LINQ to SQL，或者如果不能使用LINQ to SQL，则使用LINQ来计算运行总数，这样我就可以将DataGridView.DataSource设置为我的数据。

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:

我想要一个可以生成如下结果的L2S或LINQ语句：

   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.

请注意，可能有多个具有相同日期的项目（12-02-2009）。在计算运行总计之前，应按日期对结果进行排序。我猜这意味着我需要两个语句，一个用于获取数据并对其进行排序，另一个用于执行运行总计算。

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.

我希望Aggregate可以做到这一点，但它并不像我希望的那样有效。或者也许我只是想不出来。

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:

编辑结合Alex和DOK的答案，这就是我最终得到的结果：

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

5 个解决方案

#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
}

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

#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.

如果还没有回答，我有一个解决方案，我一直在我的项目中使用。这与Oracle分区组非常相似。关键是让运行总计中的where子句与orig列表匹配，然后按日期对其进行分组。

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

#4

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

Aggregate也可用于获取运行总计：

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

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