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();

Answer 2

First of all, you should setup navigation properties for relevant foreign keys (for example, 1:N relation of target to transactions, as well as from transaction to products). If setup correctly, the database design/EF schema should take most of the work from your hands. After that you can go:

(from target in targets
  where target.Year == year && target.ProductGroup.TeamId == teamId
  group target by new 
                      { 
                        target.ProductGroupID, 
                        target.StateID, 
                        target.Year 
                      } into targetGroup
  select new { Key = targetGroup.Key,
               TargetL1 = targetGroup.Sum(target => target.Level1_Target),
               ActualL1 = targetGroup.SelectMany(tg => tg.Transactions)
                                     .Sum(trans => trans.Level1_Total)
             });

However, I couldn't test it, so there might be errors.