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

Question

As the name says, my goal is to join two tables (goal and transaction) to multiple columns, and then group the result of this join and sum the column values ​​from the BOTH tables. The following query allows access to columns from the FIRST table in a 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 (approximately):

  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 to do it in LINQ?

Answer 1

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

change the group by command 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 must configure the navigation properties for the corresponding foreign keys (for example, the target 1: N ratio to transactions, as well as from transaction to products). If the configuration is correct, the database creation scheme / EF scheme should do 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 could not verify it, so there may be errors.