LINQ到实体，连接两个表，然后组，并采取列汇总来自两个表 [英] LINQ to Entities, join two tables, then group and take sums of columns from both tables

问题描述

正如标题所说，我的目标是在多个列JOIN两个表（目标和交易），那再组连接的结果，并从两个表总结列的值。下面的查询只允许在加盟！

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

如下图所示，这是平凡中的T-SQL来实现，（大约） >

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

我如何做到这一点的LINQ？

How do I do this in LINQ?

推荐答案

交易变量超出范围。如果你有这本事分组的结果，那么你可以使用它

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

by子句改变你组：

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

然后你的SELECT子句可以做到这一点：

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

这篇关于LINQ到实体，连接两个表，然后组，并采取列汇总来自两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！