Join two tables using LINQ to Entities, then group and total the columns from each tables.

c# entity-framework linq linq-to-entities

Question

As stated in the headline, I want to categorize the results of a join between two tables (target and transaction) based on a number of their columns, then add the values of the columns in BOTH tables. The ensuing query restricts access to columns from the join's FIRST table only!

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

This is easy to implement in T-SQL, as shown below, 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 can I implement this with LINQ?

1
4
2/22/2011 12:50:14 AM

Accepted Answer

The transaction variable is not within its purview. You may utilize it if you group the result and include it.

Modify your group by clause to read:

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

the following is what your pick clause may do:

select new
        {
            TargetL1 = grouped.Sum(groupedThing => groupedThing.target.Level1_Target,
            ActualL1 = grouped.Sum(trans => groupedThing.transaction.Level1_Total)
        }).SingleOrDefault();
10
2/22/2011 2:43:31 AM

Popular Answer

Setting up navigation properties for pertinent foreign keys should come first (for example, 1:N relation of target to transactions, as well as from transaction to products). The database architecture and EF schema should do the majority of the work if everything is set up properly. Following that, you may:

(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 was unable to test it, so there might be mistakes.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow