时间：2019-03-17 标签：c#Lambda子查询（linq2sql）

Question

是否可以在 Lambda 中编写与此 TSQL 语句等效的语句？

SELECT  DISTINCT
        pba1.adGroupsId
,      Amount = (SELECT pba2.value 
                  FROM   ProjectBudgetAdjustment pba2 
                  WHERE  pba1.budgetId = pba2.budgetId 
                  AND    pba1.adGroupsId = pba2.adGroupsId 
                  AND    pba2.metricId IN 
                  (
                      SELECT  id 
                      FROM    Metrics 
                      WHERE   [description] = 'Amount')
                  )
,      [Hours] = (SELECT pba2.value 
                   FROM   ProjectBudgetAdjustment pba2 
                   WHERE  pba1.budgetId = pba2.budgetId 
                   AND    pba1.adGroupsId = pba2.adGroupsId 
                   AND    pba2.metricId IN 
                  (
                      SELECT id 
                      FROM   Metrics 
                      WHERE  [description] = 'Hours')
                  )
FROM    ProjectBudgetAdjustment pba1
WHERE   pba1.budgetId IN
(
   SELECT  id
   FROM ProjectBudget
   WHERE   projectId IN
   (
      SELECT  id
      FROM    Projects
      WHERE   code = 'xxx'
   )
)

Answer 1

我在这里可能完全错了，但它是这样的吗：

ProjectBudgetAdjustment
.Where( pba => pba.ProductBudget.Products.Code == "xxx")
.Where( pba => (pba.Metrics.Description == "Hours" 
             || pba.Metrics.Description == "Amount"))
.GroupBy(   pba => pba.ProductBudget.adGroupsId )
.Select (
    r => new
    {
        adGroupsId = r.Key,
        Hours  = r.Sum(i=> (i.Metrics.Description == "Hours"  ? i.value : 0m)),
        Amount = r.Sum(i=> (i.Metrics.Description == "Amount" ? i.value : 0m))
    }
);