How to make LINQ GroupBy, where the key can be canceled?

Question

I am trying to make complex GroupBy in LINQ, but I am having problems with my key selector. In the following code, I can group by my key in one direction (by SellerID, BuyerID), but I really need to group by my key and vice versa (by SellerID, BuyerID or BuyerID, SellerID). My ultimate goal of this query is that when the keys are reversed, I need to make the sum of the assets negative. This will allow me to exclude any amounts that exist on both sides, and then I get only records that have amounts on this particular side.

The following code should explain this:

public class Record { public int RecordID; public int SellerID; public int BuyerID; public List<Asset> Assets; } public class Asset { public int AssetID; public decimal Amount; } var groups = new List<Record> { new Record { RecordID = 1, SellerID = 100, BuyerID = 200, Assets = new List<Asset> { new Asset { AssetID = 5, Amount = 10 }}}, new Record { RecordID = 2, SellerID = 100, BuyerID = 200, Assets = new List<Asset> { new Asset { AssetID = 5, Amount = 20 }}}, new Record { RecordID = 3, SellerID = 100, BuyerID = 200, Assets = new List<Asset> { new Asset { AssetID = 6, Amount = 60 }}}, new Record { RecordID = 4, SellerID = 200, BuyerID = 100, Assets = new List<Asset> { new Asset { AssetID = 5, Amount = 40 }}}, new Record { RecordID = 5, SellerID = 200, BuyerID = 100, Assets = new List<Asset> { new Asset { AssetID = 5, Amount = 50 }}}, new Record { RecordID = 6, SellerID = 200, BuyerID = 100, Assets = new List<Asset> { new Asset { AssetID = 6, Amount = 35 }}} }; var result = groups.GroupBy( r => new { r.SellerID, r.BuyerID }, r => r.Assets, (r, assets) => new { r.SellerID, r.BuyerID, AssetSummation = assets.SelectMany(asset => asset).GroupBy(a => a.AssetID).Select(a2 => new { AssetID = a2.Key, Amount = a2.Sum(a3 => a3.Amount) }) }); 

I would like my conclusion to be as follows:

• Record 1
  • Seller: 100
  • Buyer: 200
  • Assets:
    • Asset
      • AssetID: 6
      • Amount: 25
• Record 2
  • Seller: 200
  • Buyer: 100
  • Assets:
    • AssetID: 5
    • Amount: 60

I think I have a good start, but I'm not sure where to go from here. How do I flip a key and then make the amounts negative so that I can sum them? I think that after I can do this, I can filter out any Asset lines where the value is 0 (this means that the recording was inverse.

EDIT # 1: Maybe what I'm trying to do is to attach a group variable to myself in SUM with all the relevant entries on both sides of the connection. Thus, I eventually join the Seller on the left side to BuyerID on the right side and BuyerID on the left side to the Seller on the right side.

Answer 1

Here is a query that returns the expected results:

 var result = records .SelectMany(r => new[] { r, new Record { // 1 SellerID = r.BuyerID, BuyerID = r.SellerID, Assets = r.Assets.Select(a => new Asset { AssetID = a.AssetID, Amount = -a.Amount }).ToList() }}) .GroupBy(r => new { r.SellerID, r.BuyerID }) // 2 .Select(g => new { // 3 Seller = g.Key.SellerID, Buyer = g.Key.BuyerID, Assets = g.SelectMany(r => r.Assets) .GroupBy(a => a.AssetID) .Select(ag => new { AssetID = ag.Key, Amount = ag.Sum(a => a.Amount) }) .Where(x => x.Amount > 0) }); 

How it works? Very simple:

• For each record, I select two records - one as it is, and the other with the return seller and buyer (also all assets are negative). Then I smooth out all the records using SelectMany .
• And group them by seller and buyer.
• Rest is a simple calculation of the amount of assets from each group.

BTW, instead of returning anonymous objects, you can create Record and Asset objects in the last select statement.

Answer 2

Helper Class:

 public class RecordItem { public int SellerID; public int BuyerID; public int AssetID; public decimal Amount; } 

Equivalence Ratio:

 public class RecordItemEqualityComparer : IEqualityComparer<RecordItem> { public bool Equals(RecordItem x, RecordItem y) { if (x.AssetID != y.AssetID) return false; if (x.BuyerID == y.BuyerID && x.SellerID == y.SellerID) return true; if (x.BuyerID == y.SellerID && x.SellerID == y.BuyerID) return true; return false; } public int GetHashCode(RecordItem obj) { return string.Format("{0}_{1}", obj.BuyerID * obj.SellerID, obj.AssetID).GetHashCode(); } } 

And LINQ query:

 var recordItemComparer = new RecordItemEqualityComparer(); var items = groups.SelectMany(r => r.Assets.Select(a => new RecordItem { BuyerID = r.BuyerID, SellerID = r.SellerID, AssetID =a.AssetID, Amount = a.Amount })) .GroupBy(ri => ri, recordItemComparer) .Select(g => new RecordItem() { BuyerID = g.Key.BuyerID, SellerID = g.Key.SellerID, AssetID = g.Key.AssetID, Amount = g.Sum(ri => (ri.BuyerID == g.Key.BuyerID) ? ri.Amount : -1 * ri.Amount) }).ToList(); 

Returns what you want: a list with two elements. The amount is calculated correctly, but the buyer and seller may be in the wrong order, so the amount may be, for example, -60 instead of 60 .

PS. It was a really good challenge!