LINQ to SQL 和有序结果的运行总计 [英] LINQ to SQL and a running total on ordered results

问题描述

我想在 DataGridView 中显示客户的会计历史，并且我想有一列显示其余额的运行总计.我这样做的旧方法是获取数据，遍历数据，然后将行一一添加到 DataGridView 并计算当时的运行总数.瘸.我更愿意使用 LINQ to SQL 或 LINQ(如果 LINQ to SQL 不可能的话)来计算运行总数，这样我就可以将 DataGridView.DataSource 设置为我的数据.

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

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

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

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

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.

我希望 Aggregate 可以解决问题，但它并不像我希望的那样工作.或者我只是想不通.

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?

编辑结合 Alex 和 DOK 的答案，这就是我的结果:

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

推荐答案

使用闭包和匿名方法:

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
}

这篇关于LINQ to SQL 和有序结果的运行总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！