Linq to SQL and a Running Total on Ordered Results

LINQ to SQL and a running total on ordered results

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
}

running total by linq : place results in paged List

I find problem

var userDocs = db.Docs.AsQueryable();

Int64 running_total = 0;
var moeen = userDocs.ToList()
                       .OrderBy(a => a.DocDate)
                       .Select(a =>
                                   {
                                       running_total += 1;
                                       return new MoeenViewModel
                                       {
                                           Doc = a,
                                           remain = running_total
                                       };
                                   }
                              );
moeen = moeen.ToList();
return View(moeen.ToPagedList(1, 15));

before list moeen by .ToList(), every use of moeen cause to side effect on running_total.

See this :
http://www.blackwasp.co.uk/LinqRunningTotal.aspx

if you change the value of the variable that has been closed over before executing the query, the results will be affected.

Linq running total 1st value added to itself

Add a ToList() to the end of the call to avoid duplicate invocations of the selector.

This is a stateful LINQ query with side-effects, which is by nature unpredictable. Somewhere else in the code, you called something that caused the first element to be evaluated, like First() or Any(). In general, it is dangerous to have side-effects in LINQ queries, and when you find yourself needing them, it's time to think about whether or not it should just be a foreach instead.

Edit, or Why is this happening?

This is a result of how LINQ queries are evaluated: until you actually use the results of a query, nothing really happens to the collection. It doesn't evaluate any of the elements. Instead, it stores Abstract Expression Trees or just the delegates it needs to evaluate the query. Then, it evaluates those only when the results are needed, and unless you explicitly store the results, they're thrown away afterwards, and re-evaluated the next time.

So this makes the question why does it have different results each time? The answer is that runningTotal is only initialized the first time around. After that, its value is whatever it was after the last execution of the query, which can lead to strange results.

This means the question could just have easily have been "Why is the total always twice what it should be?" if the asker were doing something like this:

Console.WriteLine(statement.Count()); // this enumerates all the elements!
foreach (var item in statement) { Console.WriteLine(item.Total); }

Because the only way to get the number of elements in the sequence is to actually evaluate all of them.

Similarly, what actually happened in this question was that somewhere there was code like this:

if (statement.Any()) // this actually involves getting the first result
{ 
    // do something with the statement
}
// ...
foreach (var item in statement) { Console.WriteLine(item.Total); }

It seems innocuous, but if you know how LINQ and IEnumerable work, you know that .Any() is basically the same as .GetEnumerator().MoveNext(), which makes it more obvious that it requires getting the first element.

It all boils down to the fact that LINQ is based on deferred execution, which is why the solution is to use ToList, which circumvents that and forces immediate execution.

Entity Framework getting a running total from columns

The solution was to turn the results into a list first, then do the second select, and I had not seen this as the searches I had done only shown get data. Without showing how.

As this may trip other new developers up, I have pasted the working example below, this is a result of all the suggestions from those who have tried to help.

var tl = db.Transaction
    .Where(o => o.AccountId == 1)
    .OrderBy(p => p.TransactionDate)
    .ToList()
    .Select(i =>
            {
                currentTotal += p.TransCode == "Deposit" ? p.TransactionAmount : 0;
                return new TransactionList
                {
                    ToAccountId = i.ToAccountId,
                    TransactionDate = i.TransactionDate,
                    AccountBalance = currentTotal
                };
            }
    )
    .ToList();

Unfortunately, I do not know the reason why this has to be turned to a list first, then do the select, so if anyone can chime in as to the reason, it would be appreciated if someone who knows could explain that part.

Linq- Running Total and Sub Total

use Group and Sum:

from s in SalesPeople 
group s by s.RegionCode into g 
select new {Category=g.Key, Sum = g.Group.Sum(p => p.SalesAmount)}

See also http://www.develop-one.net/blog/2007/11/11/LINQSumAndGroupBy.aspx

Performant running SUM OVER Partition in LINQ

Your answer can be simplified greatly, but does scale poorly even then, as it must go through the Where for each row to compute each row, so O(list.Count^2).

Here is the simpler version, which preserves the original order:

var result = list.Select(item => new {
    RowType = item.RowType,
    RowValue = list.Where(prior => prior.RowNumber <= item.RowNumber && prior.RowType == item.RowType).Sum(prior => prior.RowValue)
});

You can go through list once if are willing to sort. (If you know the order is correct, or can use a simpler sort, you can remove or replace the OrderBy/ThenBy.)

var ans = list.OrderBy(x => x.RowType)
              .ThenBy(x => x.RowNumber)
              .Scan(first => new { first.RowType, first.RowValue },
                    (res, cur) => res.RowType == cur.RowType ? new { res.RowType, RowValue = res.RowValue + cur.RowValue }
                                                             : new { cur.RowType, cur.RowValue }
              );

This answer uses an extension method that is like Aggregate, but returns the intermediate results, based on the APL scan operator:

// TRes seedFn(T FirstValue)
// TRes combineFn(TRes PrevResult, T CurValue)
public static IEnumerable<TRes> Scan<T, TRes>(this IEnumerable<T> src, Func<T, TRes> seedFn, Func<TRes, T, TRes> combineFn) {
    using (var srce = src.GetEnumerator()) {
        if (srce.MoveNext()) {
            var prev = seedFn(srce.Current);

            while (srce.MoveNext()) {
                yield return prev;
                prev = combineFn(prev, srce.Current);
            }
            yield return prev;
        }
    }
}

Use linq to provide a running total of how many users existed by date

Well, the basic would be something like that, I would say...

var result = repository.Users
             .GroupBy(m => EntityFunctions.TruncateTime(addDate))
             .Select(g => new {
                 Date = g.Key,
                 TotalMemberShip = g.Count(),
                 IsActiveWithStatus1 = g.Count(x => x.isActive && x.status == 1),
                 IsActiveWithStatus2 = g.Count(x => x.isActive && x.status == 2),
                 IsInactiveWithStatus1 = g.Count(x => !x.isActive && x.status == 1),
                 IsInactivewithStatus2 = g.Count(x => !x.isActive && x.status == 2)
             });

EDIT (from comment)

hmmm, I would try this, but absolutely not sure that this will work

same as before, just change the line

TotalMemberShip = repository.Users.Count(x => EntityFunctions.TruncateTime(addDate) <= g.Key)

EDIT 2

Well, maybe rather this one (I use this syntax, which has the let keyword, if you need the "aggregation" for all your results).

from item in repository.Users
group item by EntityFunctions.TruncateTime(addDate) into g
let datesBefore = repository.Users.Where(x => EntityFunctions.TruncateTime(addDate) <= g.Key)
select new {
  Date = g.Key,
  TotalMemberShip = datesBefore.Count(),
  IsActiveWithStatus1 = datesBefore.Count(x => x.isActive && x.status == 1),
  //etc.
  //if you need datas BEFORE (and equal) use datesBefore.Count
  //if you need datas "at the exact day" use g.Count
}

---

Related Topics