使用Linq对SQL的求和

本文关键字：求和 SQL Linq 使用 | 更新日期: 2023-09-27 18:08:11

我有一个SQL命令，我试图转换为LINQ到SQL命令，但我有困难。

SQL命令如下:

SELECT purchs.iclientid, ifeatureid, AddnlOptionList FROM purchs
WHERE AddnlOptionList <> ''
GROUP BY purchs.iclientid, ifeatureid, AddnlOptionList
HAVING (SUM(noptions) > 0)

我已经设法做到了以下例子:

var q =
   from purchs in db.Purchases
   group q by purchs.noptions into g
   where purchs.AddnlOptionList != ""
      && g.Sum(x => x.noptions) > 0
   select q;

然而，我似乎被困在组q与以下两个错误:

Cannot use local variable 'q' before it is declared
Cannot convert lambda expression to type 'System.Collections.Generic.IEqualityComparer<decimal?> because it is not a delegate type

从这里的一个例子说，这应该工作，虽然它使用一个连接，而我不是。如有任何帮助，不胜感激。

<<p> 解决方案/strong>

我不得不修改Xiaoy312的代码一点点得到我想要的，所以我想我将它张贴在这里，希望它可能会帮助别人在未来。谢谢你@Xiaoy312的帮助。

var updates = db.Purchases
   .Where(p => p.AddnlOptionList != "")
   .GroupBy(p => new { p.iclientid, p.ifeatureid, p.AddnlOptionList })
   .Where(g => g.Sum(p => p.noptions) > 0)
   .Select(g => g.Key);

使用Linq对SQL的求和

您不能将WHERE和HAVING子句都放入单个where中。另一种语法我不太熟悉，下面是方法语法:

var results = db.Purchases
    .Where(p => p.AddnlOptionList != "")
    .GroupBy(p => new { p.notions, p.iclientid, p.ifeatureid })
    .Where(g => g.Sum(p => p.notions) > 0)
    .SelectMany(g => g)

编辑:转换为Linq语法。

var results = from p in db.Purchases
              where p.AddnlOptionList != ""
              group p by new { p.notions, p.iclientid, p.ifeatureid } into g
              where g => g.Sum(p => p.notions) > 0
              from p in g
              select p;

编辑:我错过了读sql命令。它的意思是只拉组，而不是每个组中的每个项。

// method syntax
db.Purchases
    .Where(p => p.AddnlOptionList != "")
    .GroupBy(p => new { p.notions, p.iclientid, p.ifeatureid })
    .Where(g => g.Sum(p => p.notions) > 0)
    .Select(g => g.Key)
// query syntax
    from p in db.Purchases
    where p.AddnlOptionList != ""
    group p by new { p.notions, p.iclientid, p.ifeatureid } into g
    where g.Sum(p => p.notions) > 0
    select new { g.Key.notions, g.Key.iclientid, g.Key.ifeatureid };
相关文章：