LINQ to SQL的左外连接与GROUP BY和HAVING子句 [英] LINQ to Sql Left Outer Join with Group By and Having Clause

问题描述

我失去了一天来尝试翻译一个SQL查询LINQ lambda表达式，但不是成功

I lost a day to try translate a sql query to LINQ lambda expression but not success.

我的SQL查询：

SELECT a.ID,
       Sum(b.[Value]) AS [Value],
       c.ContractValue
FROM   Contracts a
       LEFT JOIN DepositHistories b
              ON b.ContractID = a.ID
       INNER JOIN LearningPackages c
               ON a.LearningPackageID = c.ID
GROUP  BY a.ID,
          c.ContractValue
HAVING Sum(b.[Value]) < c.ContractValue
        OR Sum(b.[Value]) IS NULL
        OR Sum(b.[Value]) = 0 

这是LINQ查询：

var contracts = (
                from a in db.Contracts
                from b in db.LearningPackages.Where(e => e.ID == a.LearningPackageID).DefaultIfEmpty()
                group a by new
                {
                    a.ID,
                    b.ContractValue
                } into g
                from c in db.DepositHistories.Where(e => e.ContractID == g.Key.ID).DefaultIfEmpty()
                where g.Sum(e => c.Value) < g.Key.ContractValue || g.Sum(e => c.Value) == null
                select new
                {
                    ID = g.Key.ID,
                    ContractValue = g.Key.ContractValue,
                    Value = g.Sum(e => c.Value != null ? c.Value : 0)
                }
                ).ToList();

我的结果是：

My result:

  ID  ContractValue    Value  
  1      6000000      500000  
  1      6000000      500000  
  1      6000000      500000  
  1      6000000      500000  
  1      6000000      500000  
  3      7000000      500000  
  3      7000000      500000  
  3      7000000      500000  
  4      6000000      500000  
  5      6000000      0  
  6      6000000      0 

这不是组和值相加。

请帮帮我！

感谢

推荐答案

你可以做这样的：

var result = from b in db.DepositHistories
             join a in db.Contracts on b.CotractID equals a.ID
             join c in db.LearningPackages on a.LearningPackageID equals c.ID
             group b by new{ a.ID,c.COntractValue} into g
             where g.Sum(x=>x.Value) < g.Key.COntractValue 
             || g.Sum(x=>x.Value) == null 
             || g.Sum(x=>x.Value) == 0
            select new 
                  { 
                   ID = g.Key.ID, 
                   Value = g.Sum(x=>x.Value), 
                   ContractValue = g.Key.COntractValue
                  };

我做了一个的 DEMO FIDDLE 更清晰。

有关左外连接你要做的加入你的病情到somealias 并从别名somealias.DefaultIfEmpty（）。

For left outer join you have to do join your condition into somealias and them from alias in somealias.DefaultIfEmpty().

下面是左外连接版这给正确的结果：

Here is the version with left outer join which gives correct results:

var result = from a in Contracts
             join b in DepositHistories on a.ID equals b.CotractID into e
             from f in e.DefaultIfEmpty()
             join c in LearningPackages on a.LearningPackageID equals c.ID
             group f by new 
                       { 
                          a.ID, 
                          c.COntractValue 
                       } into g
             where g.Sum(x => x==null ? 0 : x.Value) < g.Key.COntractValue 
             ||  g.Sum(x => x==null ? 0 : x.Value) == 0
             select new 
                   { 
                      ID = g.Key.ID, 
                      Value = g.Sum(x => x == null ? 0 : x.Value), 
                      ContractValue = g.Key.COntractValue 
                   };

已更新FIDDLE演示

您也可以检查的这个SO张贴关于如何做到左外LINQ中

使用查询方法，你必须使用群组加入（）方法< 。/ A>为左外连接

Using query method you have to use GroupJoin() method for left outer join.

下面是方法查询上面的代码：

Here is the above code with Method Query:

var Result = Contracts.GroupJoin(DepositHistories, 
                                    a => a.ID, 
                                    b => b.CotractID, 
                                    (a, b) => new { a = a, b = b })
                                  .Join(LearningPackages, 
                                  a => a.a.LearningPackageID, 
                                  b => b.ID, 
                                  (a, b) => new { a = a, b = b })
                                  .GroupBy(e => new 
                                                    { 
                                                        e.a.a.ID, 
                                                        e.b.COntractValue 
                                                    }, 
                                                    (k, g) => new 
                                                                { 
                                                                    ID = k.ID, 
                                                                    ContractValue = k.COntractValue, 
                                                                    Value =  g.Sum(x => x == null ? 0 : x.a.b.Sum(d=>d.Value)) 
                                                                }
                                            ).Where(x => x.Value < x.ContractValue || x.Value == 0).ToList();

执行方法QUERY 修订FIDDLE

这篇关于LINQ to SQL的左外连接与GROUP BY和HAVING子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！