LINQ to Entities, join two tables, then group and take sums of columns from both tables
16,685
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();
Author by
Daniel Coffman
Updated on June 04, 2022Comments
-
Daniel Coffman almost 2 years
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?
-
Daniel Coffman about 13 yearsThank you, couldn't figure out the "group new {target, transaction}" part. Makes perfect sense now.