LINQ JOIN + GROUP BY + SUM

aggregate, c#, group-by, linq, sum

Solution

A minor mistake, corrected:

var itineraryItems = from ii in this.ItineraryItemRecords 
   join t in this.TransactionRecords on ii.OperatorID equals t.TransactionActor.OperatorID 
   into g 
   select new 
   { 
       OperatorID = ii.OperatorID 
       //, TotalBuy = g.Sum(i => ii.TotalBuy) 
       , TotalBuy = g.Sum(i => i.TotalBuy) 
       //, TotalSell = g.Sum(i => ii.TotalSell) 
       , TotalSell = g.Sum(i => i.TotalSell) 
       , PaidTC = (0 - (g.Sum(t => t.AmountTC))) 
       , PaidAUD = (0 - (g.Sum(t => t.AmountAUD))) 
   };

I recommend against re-using identifiers - will help avoid these mistakes in the future.

Problem

I have two LINQ statements that I would like to make into one, but for the life of me I can't get it to work. I can't get the grouping to work in the first statement. It complains that the `TotalBuy` and `TotalSell` properties aren't there, although doesn't complain about `AmountTC` and `AmountAUD`. This should be simple. Any thoughts? ``` var itineraryItems = from ii in this.ItineraryItemRecords join t in this.TransactionRecords on ii.OperatorID equals t. TransactionActor.OperatorID into g select new { OperatorID = ii.OperatorID, TotalBuy = g.Sum(i = >ii.TotalBuy) , TotalSell = g.Sum(i = >ii.TotalSell) , PaidTC = (0 - (g.Sum(t = >t.AmountTC))) , PaidAUD = (0 - (g.Sum(t = >t.AmountAUD))) }; var itineraryItemz = from i in itineraryItems group i by i.OperatorID into g select new { OperatorID = g.Key, TotalBuy = g.Sum(i = >i.TotalBuy) , TotalSell = g.Sum(i = >i.TotalSell) , PaidTC = (0 - (g.Sum(i = >i.PaidTC))) , PaidAUD = (0 - (g.Sum(i = >i.PaidAUD))) }; ``` As a side note, `ItineraryItemRecords` and `TransactionRecords` are Collections of classes handled by `SubSonic`. This really should be simple, so any help would be appreciated. Regards, John

Original source