LINQ JOIN + GROUP BY + SUM

Question

Hey there,

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

Answer 1

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.