By Ecyrb


2009-12-02 18:08:37 8 Comments

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

5 comments

@daydreamer 2017-08-16 12:26:07

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

@DF5 2014-09-30 21:43:09

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

@BrainSlugs83 2015-06-25 21:19:08

Interesting. Seems like this would offload data to the database properly.

@Aaron Anodide 2013-04-12 19:04:31

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

@DOK 2009-12-02 18:40:25

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

@Ecyrb 2009-12-02 19:17:39

Thanks! I like how you assign running_total inline. That's pretty slick.

@Alex Bagnolini 2009-12-02 21:52:32

running_total = (running_total = running_total + x) => Mind blown. I will surely remember this for the next time :)

@hagensoft 2013-07-15 17:53:06

cool! It works with strongly type objects as well, not just anonymous types

@ahruss 2014-07-30 14:22:35

Note that this ends up being wrong if you use the query result repeatedly. This question is a result of this. You can solve this by adding a .ToList() to the end of the query.

@BrainSlugs83 2015-06-25 21:17:46

This is another client-side only solution (that requires enumerating the whole dataset on the client -- this will suck for large datasets). :(

@Alex Bagnolini 2009-12-02 18:40:21

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
}

@Ecyrb 2009-12-02 19:16:53

I wish I could mark you both as the answer! Your answer was easy to understand and matches my example. I do like how DOK increments the currentTotal inline, during the assignment, though.

@Angkor Wat 2010-08-19 10:11:49

+1 SOF should have options for marking multiple answers.

@Neil Barnwell 2011-02-18 14:40:31

I've tried this with Linq to Entities (EF) and get a "A lambda expression with a statement body cannot be converted to an expression tree" compile error. Is that particular to EF as opposed to L2O?

@ahruss 2014-07-30 14:22:16

Note that this ends up being wrong if you use the query result repeatedly. This question is a result of this. You can solve this by adding a .ToList() to the end of the query.

@BrainSlugs83 2015-06-25 21:15:07

This solution will force execution to be on the client (i.e. it has to pull down the whole result set to get the correct answer) -- it seems like something like this would be much more performant if it were done on the SQL server...

@BrainSlugs83 2015-06-25 21:16:12

@NeilBarnwell it's telling you that the only way this works is if you pull the whole dataset down to the client first (note he's storing the dataset in a list and not in an IQueryable or IEnumerable). There has to be a better way.

Related Questions

Sponsored Content

34 Answered Questions

[SOLVED] What is the Java equivalent for LINQ?

  • 2009-08-01 18:53:26
  • Ahmed
  • 260795 View
  • 789 Score
  • 34 Answer
  • Tags:   java linq

23 Answered Questions

[SOLVED] LINQ query on a DataTable

11 Answered Questions

[SOLVED] LINQ Aggregate algorithm explained

  • 2011-08-18 09:51:21
  • Alexander Beletsky
  • 236238 View
  • 672 Score
  • 11 Answer
  • Tags:   c# .net linq

19 Answered Questions

[SOLVED] What is the syntax for an inner join in LINQ to SQL?

13 Answered Questions

[SOLVED] When to use .First and when to use .FirstOrDefault with LINQ?

  • 2009-06-21 19:15:29
  • Metro Smurf
  • 522990 View
  • 779 Score
  • 13 Answer
  • Tags:   c# .net linq

7 Answered Questions

[SOLVED] Multiple "order by" in LINQ

  • 2008-11-18 13:34:11
  • Sasha
  • 561819 View
  • 1502 Score
  • 7 Answer
  • Tags:   linq sql-order-by

9 Answered Questions

[SOLVED] Group by in LINQ

  • 2011-09-06 19:44:20
  • test123
  • 1166055 View
  • 966 Score
  • 9 Answer
  • Tags:   c# linq group-by

4 Answered Questions

[SOLVED] Convert Linq Query Result to Dictionary

16 Answered Questions

[SOLVED] Entity Framework vs LINQ to SQL

19 Answered Questions

[SOLVED] Dynamic LINQ OrderBy on IEnumerable<T> / IQueryable<T>

Sponsored Content