How to calculate total using linq
I have the result of a linq query as shown in the picture. In the last request (not shown) I am grouping Year by LeaveType. However, I want to calculate the running grand total for leaveCarriedOver for each type over several years. That is, the sick LeaveCarriedOver in 2010 becomes the "opening" balance for sick leave in 2011 plus one for 2011.
I made another query on the shown list of results that looks like this:
var leaveDetails1 = (from l in leaveDetails
select new
{
l.Year,
l.LeaveType,
l.LeaveTaken,
l.LeaveAllocation,
l.LeaveCarriedOver,
RunningTotal = leaveDetails.Where(x => x.LeaveType == l.LeaveType).Sum(x => x.LeaveCarriedOver)
});
where leaveDetails is the result of the image.
The resulting RunningTotal is not cumulative as expected. How can I achieve my original goal. Open to any ideas - my last option would be to do this in javascript in the frontend. thanks in advance
source to share
A simple implementation is to first get a list of possible totals and then get a sum from the details for each of these categories.
get a separate list Year
and LeaveType
- this is a group by and select the first of each group. we return a List<Tuple<int, string>>
, where Int
is the year, and string
is the LeaveType value
var distinctList = leaveDetails1.GroupBy(data => new Tuple<int, string>(data.Year, data.LeaveType)).Select(data => data.FirstOrDefault()).ToList();
then we want to sum for each of these elements was necessary, so you want to choose this list to return the id ( Year
and LeaveType
) plus the total value, so the added value for Tuple<int, string, int>
.
var totals = distinctList.Select(data => new Tuple<int, string, int>(data.Year, data.LeaveType, leaveDetails1.Where(detail => detail.Year == data.Year && detail.LeaveType == data.LeaveType).Sum(detail => detail.LeaveCarriedOver))).ToList();
after reading the line above, you can see that it accepts the individual totals we want to list, create a new object, save Year
and LeaveType
for reference, and then set the latter Int
using Sum<>
filtered details for Year
and LeaveType
.
source to share
If I fully understand what you are trying to do, I don't think I will rely solely on the built-in LINQ operators. I think (emphasis on thought) that any combination of built-in LINQ operators is going to solve this problem in O (n ^ 2) runtime.
If I was going to implement this in LINQ, I would create an extension method for IEnumerable that is similar to a function Scan
in reactive extensions (or find a library in there that already implemented it):
public static class EnumerableExtensions
{
public static IEnumerable<TAccumulate> Scan<TSource, TAccumulate>(
this IEnumerable<TSource> source,
TAccumulate seed,
Func<TAccumulate, TSource, TAccumulate> accumulator)
{
// Validation omitted for clarity.
foreach(TSource value in source)
{
seed = accumulator.Invoke(seed, value);
yield return seed;
}
}
}
Then it should be done around O (n log n) (due to order of operations):
leaveDetails
.OrderBy(x => x.LeaveType)
.ThenBy(x => x.Year)
.Scan(new {
Year = 0,
LeaveType = "Seed",
LeaveTaken = 0,
LeaveAllocation = 0.0,
LeaveCarriedOver = 0.0,
RunningTotal = 0.0
},
(acc, x) => new {
x.Year,
x.LeaveType,
x.LeaveTaken,
x.LeaveAllocation,
x.LeaveCarriedOver,
RunningTotal = x.LeaveCarriedOver + (acc.LeaveType != x.LeaveType ? 0 : acc.RunningTotal)
});
You are not saying, but I am assuming the data comes from a database; if so, then you can get leaveDetails
back already sorted and skip sorting here. This will take you to O (n).
If you don't want to create an extension method (or find one) then it will do the same (just in an ugly way).
var temp = new
{
Year = 0,
LeaveType = "Who Cares",
LeaveTaken = 3,
LeaveAllocation = 0.0,
LeaveCarriedOver = 0.0,
RunningTotal = 0.0
};
var runningTotals = (new[] { temp }).ToList();
runningTotals.RemoveAt(0);
foreach(var l in leaveDetails.OrderBy(x => x.LeaveType).ThenBy(x => x.Year))
{
var s = runningTotals.LastOrDefault();
runningTotals.Add(new
{
l.Year,
l.LeaveType,
l.LeaveTaken,
l.LeaveAllocation,
l.LeaveCarriedOver,
RunningTotal = l.LeaveCarriedOver + (s == null || s.LeaveType != l.LeaveType ? 0 : s.RunningTotal)
});
}
It should also be O (n log n) or O (n) if you can pre-sort leaveDetails
.
source to share
If I understand the question, you want something like
decimal RunningTotal = 0;
var results = leaveDetails
.GroupBy(r=>r.LeaveType)
.Select(r=> new
{
Dummy = RunningTotal = 0 ,
results = r.OrderBy(o=>o.Year)
.Select(l => new
{
l.Year,
l.LeaveType ,
l.LeaveAllocation,
l.LeaveCarriedOver,
RunningTotal = (RunningTotal = RunningTotal + l.LeaveCarriedOver )
})
})
.SelectMany(a=>a.results).ToList();
Mostly overload is used Select<TSource, TResult>
to calculate the current balance, but grouped by LeaveType first, so we can reset RunningTotal for each LeaveType and then ungroup at the end.
source to share