LINQ to Entities, join two tables, then group and take sums of columns from both tables

Question

As the title says, my goal is to JOIN two tables (target and transaction) on several columns, then group the result of that join and sum the values of columns from BOTH tables. The following query only allows access to columns from the FIRST table in the join!

 var actualsVsTargets = (from target in ObjectContext.PipelineTargets
                 join transaction in ObjectContext.Transactions on
                    new
                    {
                        target.Year,
                        target.Quarter,
                        target.StateID,
                        target.ProductGroup.TeamId
                    } equals new
                    {
                        transaction.Year,
                        transaction.Quarter,
                        transaction.StateID,
                        transaction.Product.ProductGroup.TeamId
                    }   
                 where target.Year == year && target.ProductGroup.TeamId == teamId
                 group target by new
                                     {
                                         target.ProductGroupID,
                                         target.StateID,
                                         target.Year
                                     }
                 into targetGroup
                 select new
                            {
                                // this works fine (accessing target column)
                                TargetL1 = targetGroup.Sum(target => target.Level1_Target,
                                // this doesn't work (accessing transaction column)
                                ActualL1 = targetGroup.Sum(trans => trans.Level1_Total)
                            }).SingleOrDefault();

As shown below, this is trivial to implement in T-SQL, (roughly):

   SELECT
    targets.Year, 
    targets.StateID, 
    SUM(targets.Level1_Target) L1_Target, -- get the sum of targets
    SUM(transactions.Level1_Total) L1_Total -- get the sum of transactions
  FROM PipelineTargets targets 
  JOIN Transactions transactions 
    JOIN Products prods ON 
        transactions.ProductID = prods.ProductID 
    ON 
        targets.Year = transactions.Year and 
        targets.Quarter = transactions.Quarter and 
        targets.StateID = transactions.StateID and 
        prods.ProductGroupID = targets.ProductGroupID
  WHERE targets.Year = '2010' and targets.StateID = 1
  GROUP BY targets.Year, targets.StateID, targets.ProductGroupID

How do I do this in LINQ?

Answer 1

the transaction variable is out of scope. If you include it in you grouped result then you can use it.

change you group by clause to:

group new
        {
            target,
            transaction
        }
        by new
        {
            target.ProductGroupID,
            target.StateID,
            target.Year
        } into grouped

and then your select clause can do this:

select new
        {
            TargetL1 = grouped.Sum(groupedThing => groupedThing.target.Level1_Target,
            ActualL1 = grouped.Sum(trans => groupedThing.transaction.Level1_Total)
        }).SingleOrDefault();