Using LINQ to group and sum

Question

So i have a List called transactions.

var itemA = new TransactionItem() { ProductId = 1, Quantity = 2 }; var itemB = new TransactionItem() { ProductId = 1, Quantity = 3 }; var tranA = new Transaction() { Type = TransactionType.credit, Items = new List<TransactionItem>() { itemA } }; var tranB = new Transaction() { Type = TransactionType.credit, Items = new List<TransactionItem>() { itemB } }; var tranC = new Transaction() { Type = TransactionType.debit, Items = new List<TransactionItem>() { itemA } }; var transactions = new List<Transaction>() { tranA, tranB };

How do I get a grouping that cancels out Credits and Debits; ie in the above I have two credits where ProductID equals 1, totaling 5, and one debit where ProductID equals 1, totaling 2, so I'd like to project a new list of transactionItems showing the resulting positive amount.

(background: I'm trying to create a function that takes a list of transactions and determines after all the credits and debits what items a person should have remaining on account.)

Alberto Monteiro · Accepted Answer · 2016-01-07 01:29:02Z

This should solve your problem

transactions.SelectMany(t => t.Items, (t, ti) => new { t.Type, ti.ProductId, ti.Quantity }) .GroupBy(x => x.ProductId, x => x.Type == TransactionType.credit ? x.Quantity : -x.Quantity) .Select(x => new TransactionItem { ProductId = x.Key, Quantity = x.Sum() })

The result is

Collection containing one TransactionItem with values: { ProductId=1, Quantity=3 }

Some improvement

If you can change the TransactionType to be this

public enum TransactionType { credit = 1, debit = -1 }

Then the LINQ query could be simplified to that

transactions.SelectMany(t => t.Items, (t, ti) => new { t.Type, ti.ProductId, ti.Quantity }) .GroupBy(x => x.ProductId, x => (int)x.Type * x.Quantity) .Select(x => new TransactionItem { ProductId = x.Key, Quantity = x.Sum() })

Yeah, I had just fixed up some of my code and tweaked ChaseMedallion's answer to work, but yours works perfectly. I had some transactions with null Items. Thanks.
@EvanMorrison nice, if you have any other question I would love to help.

ChaseMedallion · Accepted Answer · 2016-01-07 01:04:00Z

I'm not 100% sure what you mean by "cancel out credits and debits", but it sounds like maybe you want to subtract one from the other?

// first, use SelectMany() to flatten the "list of lists" structure var result = transaction.SelectMany( // for each transaction, select all items t => t.Items, // for each (transaction, item) pair, select out // an anonymous type with product id and quantity (I'm using Type // here to give Quantity a sign, although I'm not sure that's // what you meant) (t, ti) => new { ti.ProductId, t.Type == TransactionType.credit ? ti.Quantity : -ti.Quantity } ) // take the big list and group up by product id .GroupBy(t => t.ProductId) // for each group, aggregate a new item with the sum quantity .Select(g => new TransactionItem { ProductId = g.Key, Quantity = g.Sum() }) // remove items where everything canceled out .Where(t => t.Quantity != 0) .ToList();

line 12: t.Type == TransactionType will want a Type instead of quantity... I'm still piecing through it all though..

Collectives™ on Stack Overflow

Using LINQ to group and sum

2 Answers 2

3 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Related