LINQ Aggregate function up to current row

Question

Assuming I have the following

var list = new []{
            new { Price = 1000, IsFirst = true}, 
            new { Price = 1100, IsFirst = false},
            new { Price = 450, IsFirst = true},
            new { Price = 300, IsFirst = false}
};

and I want to generate the following output:

Price  IsFirst  First Second Final
----------------------------------
1000   True     1000  0      1000
1100   False    0     1100   -100
450    True     450   0      350
300    False    0     300    50

Is it possible to have some sort of aggregate function processed up to current row? I like to have all the stuff in pure LINQ but as of now I have no other choice than manually iterating the list and sum the column conditionally.

var result = list.Select(x => new 
{
    Price = x.Price,
    IsFirst = x.IsFirst,
    First = x.IsFirst ? x.Price : 0,
    Second = !x.IsFirst ? x.Price : 0,
    Final = 0 // ???
}).ToList();

int sum = 0;

for(int i=0; i<result.Count(); i++)
{
    sum += (result[i].IsFirst ? result[i].Price : - result[i].Price);   
    // updating Final value manually
}

If you're playing with `IEnumerable` and not `IQueryable` this should work http://stackoverflow.com/questions/1834753/linq-to-sql-and-a-running-total-on-ordered-results — ta.speot.is, Jul 13 '14 at 08:59

score 3 · Accepted Answer · answered Jul 13 '14 at 09:12

The easiest way to do this is to use the Microsoft Reactive Extension Team's "Interactive Extension" method Scan. (Use NuGet and look for Ix-Main.)

var query =
    list
        .Scan(new
        {
            Price = 0,
            IsFirst = true,
            First = 0,
            Second = 0,
            Final = 0
        }, (a, x) => new
        {
            Price = x.Price,
            IsFirst = x.IsFirst,
            First = x.IsFirst ? x.Price : 0,
            Second = !x.IsFirst ? x.Price : 0,
            Final = a.Final + (x.IsFirst ? x.Price : - x.Price)
        });

This gives:

result

However, you can do it with the built-in Aggregate operator like this:

var query =
    list
        .Aggregate(new []
        {
            new
                {
                    Price = 0,
                    IsFirst = true,
                    First = 0,
                    Second = 0,
                    Final = 0
                }
        }.ToList(), (a, x) =>
        {
            a.Add(new
            {
                Price = x.Price,
                IsFirst = x.IsFirst,
                First = x.IsFirst ? x.Price : 0,
                Second = !x.IsFirst ? x.Price : 0,
                Final = a.Last().Final + (x.IsFirst ? x.Price : - x.Price)
            });
            return a;
        })
        .Skip(1);

You get the same result.

score 2 · Answer 2 · answered Jul 13 '14 at 09:07

What you want is called a running total.

As far as I know, there is no built-in method to do this in LINQ, but various people have written extension methods to do that. One that I quickly found online is this one:

Rollup Extension Method: Create Running Totals using LINQ to Objects

Based on this, it should be fairly easy to turn your code into an extension method that

resets the intermediate value when encountering an item with IsFirst = true,
otherwise decrements the value,
and yields it.

score 0 · Answer 3 · answered Jul 13 '14 at 09:27

You can do something like this:

int final = 0;
var result = list.Select(x => new
{
    Price = x.Price,
    IsFirst = x.IsFirst,
    First = x.IsFirst ? x.Price : 0,
    Second = !x.IsFirst ? x.Price : 0,
    Final = x.IsFirst ? final+=x.Price : final-=x.Price
}).ToList();

But you would need to define an integer (final) outside of the linq expression to keep track of the total sum.

LINQ Aggregate function up to current row

3 Answers3