How to group and select in Linq based on total
I have a requirement where I need to group and select transactions where the number of starts exceeds a threshold of 10. After the threshold is exceeded, the counter will be reset.
Here is an example of what I am trying to do ...
Below are some of the transactions:
Id | Amount
1 | 5.50
2 | 4.10
3 | 1.20
4 | 1.05
5 | 3.25
6 | 1.25
7 | 5.15
8 | 8.15
9 | 5.15
The result I would like to achieve:
Group 1:
Id | Amount
1 | 5.50
2 | 4.10
3 | 1.20
Group 2:
4 | 1.05
5 | 3.25
6 | 1.25
7 | 5.15
Group 3:
8 | 8.15
9 | 5.15
I came up with a solution that uses a for and yield loop. You can see it at https://dotnetfiddle.net/rcSJO4 as well as below.
I just wondered if there was a more elegant solution, and if there is a smart and readable way that can be achieved using Linq.
My decision:
using System;
using System.Linq;
using System.Collections.Generic;
public class Transaction{
public int Id { get; set;}
public decimal Amount { get; set;}
}
public class Program
{
public static void Main()
{
var transactions = new Transaction [] {
new Transaction {Id = 1, Amount = 5.50m},
new Transaction {Id = 2, Amount = 4.10m},
new Transaction {Id = 3, Amount = 1.20m},
new Transaction {Id = 4, Amount = 1.05m},
new Transaction {Id = 5, Amount = 3.25m},
new Transaction {Id = 6, Amount = 1.25m},
new Transaction {Id = 7, Amount = 5.15m},
new Transaction {Id = 8, Amount = 8.15m},
new Transaction {Id = 9, Amount = 5.15m},
};
var grouped = ApplyGrouping(transactions);
foreach(var g in grouped)
{
Console.WriteLine("Total:" + g.Item1);
foreach(var t in g.Item2){
Console.WriteLine(" " +t.Amount);
}
Console.WriteLine("---------");
}
}
private static IEnumerable<Tuple<decimal, IEnumerable<Transaction>>> ApplyGrouping(IEnumerable<Transaction> transactions){
decimal runningTotal = 0m;
decimal threshold = 10m;
var grouped = new List<Transaction>();
foreach(var t in transactions){
grouped.Add(t);
runningTotal += t.Amount;
if (runningTotal <= threshold) continue;
yield return new Tuple<decimal, IEnumerable<Transaction>>(runningTotal, grouped);
grouped.Clear();
runningTotal = 0;
}
}
}
source to share
I don't know if you are going to find a solution that is completely satisfying. There are no built-in Linq operators (or combinations of operators) that will do this in a way that is aesthetically pleasing. Your approach - refactoring complexity into an extension method is the best approach. There is nothing wrong with it. It's Linq anyway. just an abstraction that hides the messy bits. However, if you use this pattern a lot, I would argue that you can refactor your code into something more general. What you have now will only work in this particular situation. If you do something like this:
public static class EnumerableExtensions
{
public static IEnumerable<TResult> GroupUntil<TSource, TAccumulation, TResult>(
this IEnumerable<TSource> source,
Func<TAccumulation> seedFactory,
Func<TAccumulation, TSource, TAccumulation> accumulator,
Func<TAccumulation, bool> predicate,
Func<TAccumulation, IEnumerable<TSource>, TResult> selector)
{
TAccumulation accumulation = seedFactory();
List<TSource> result = new List<TSource>();
using(IEnumerator<TSource> enumerator = source.GetEnumerator())
{
while(enumerator.MoveNext())
{
result.Add(enumerator.Current);
accumulation = accumulator(accumulation, enumerator.Current);
if(predicate(accumulation))
{
yield return selector(accumulation, result);
accumulation = seedFactory();
result = new List<TSource>();
}
}
if(result.Count > 0)
{
yield return selector(accumulation, result);
}
}
}
}
Then, for your case, you can call it like this:
var grouped =
transactions
.GroupUntil(
() => 0.0m,
(accumulation, transaction) => accumulation + transaction.Amount,
(accumulation) => accumulation > 10.0m,
(accumulation, group) => new { Total = accumulation, Transactions = group})
But it's general enough that it can be used elsewhere.
source to share
I would stick with your solution. Even if you can hack it with LINQ, it will most likely not be readable.
The only thing you have to change is to use the same one List<T>
over and over and clean it up after returning, because it List<T>
is a reference type that you also clean up already returned items.
Create a new List<T>
instad to call Clear
in the previous one.
private static IEnumerable<Tuple<decimal, IEnumerable<Transaction>>> ApplyGrouping(IEnumerable<Transaction> transactions){
decimal runningTotal = 0m;
decimal threshold = 10m;
var grouped = new List<Transaction>();
foreach(var t in transactions){
grouped.Add(t);
runningTotal += t.Amount;
if (runningTotal <= threshold) continue;
yield return new Tuple<decimal, IEnumerable<Transaction>>(runningTotal, grouped);
grouped = new List<Transaction>();
runningTotal = 0;
}
}
source to share
Here's a LINQ way to do this
var runningTotal = 0.0m;
List<List<Transaction>> list = new List<List<Transaction>>();
List<Transaction> currentList = new List<Transaction>();
transactions.Select(x => {
runningTotal += x.Amount;
currentList.Add(x);
if (runningTotal >= 10)
{
Console.WriteLine("Group 1 Total {0}", runningTotal);
runningTotal = 0;
list.Add(currentList);
currentList = new List<Transaction>();
}
return runningTotal;
}).ToList();
source to share