LINQ to Sql 左外连接与 Group By 和 Having 子句
LINQ to Sql 左外连接与 Group By 和 Having 子句
我花了一天的时间尝试将一个SQL查询转换为LINQ lambda表达式,但没有成功。
我的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();
我的结果是:
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
它没有对值进行分组和求和。
请帮助我!
谢谢!
LINQ to SQL 左外连接与分组和Having子句
在这个问题中,我们需要使用LINQ to SQL进行左外连接,同时还需要使用分组和Having子句。下面是解决这个问题的代码示例。
首先,我们可以使用下面的代码进行左外连接、分组和Having子句的操作:
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 };
其次,我们可以使用下面的代码进行左外连接的操作:
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 };
最后,我们还可以使用方法语法来进行左外连接的操作:
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();
希望以上代码示例能帮助你解决问题!