SQL to linq Having Clause

本文关键字：Clause Having linq to SQL | 更新日期: 2023-09-27 18:19:58

我正试图使用LINQ在C#中编写以下SQL查询，获得相同结果的最佳方法是什么？

SELECT [t1].[JOB]
FROM (
    SELECT COUNT(*) AS [value], [t0].[JOB], [t0].[LINK], [t0].[PREFIX]
    FROM [dbo].[LABELS] AS [t0]
    GROUP BY [t0].[JOB], [t0].[LINK], [t0].[PREFIX]
    HAVING [t0].[PREFIX] = 'B' AND [t0].[LINK] != 0
    ) AS [t1]
GROUP BY [t1].[JOB], [t1].[value]
HAVING [t1].[value] > 1

JOB和LINK字段上的标签应该是唯一的，组合不应该重复。这个查询是检查哪些JOB违反了该规则

编辑：到目前为止，我的LINQ请求，它没有产生预期的结果。

dbP.PackagingLabels.GroupBy(l => new {l.JOB, l.LINK, l.PREFIX})
    .Where(g => g.Select(e => e.PREFIX != "B" && e.LINK != 0).Count() > 1)
    .Select(g => g.Key.JOB);

SQL to linq Having Clause

看起来你可以极大地简化它：

SELECT COUNT(*) AS [counts], [t0].[JOB], [t0].[LINK]
FROM [dbo].[LABELS] AS [t0]
WHERE [t0].[PREFIX] = 'B' AND [t0].[LINK] != 0
GROUP BY [t0].[JOB], [t0].[LINK]
HAVING COUNT(*) > 1

同样，就像您的SQL查询一样，您的LINQ应该首先应用WHERE子句：

var groupsOfDupes = dbP.PackagingLabels
   .Where(e => e.PREFIX != "B" && e.LINK != 0)
   .GroupBy(l => new {l.JOB, l.LINK})
   .Where(grp => grp.Skip(1).Any()) //this returns true if there is more than one item in the group
   .ToList();

这将为您提供一个组列表。请注意，列表中的每个项目本身就是一个列表。内部列表是共享相同JOB和LINK的项目。

foreach(var group in groupsOfDupes)
{
   Console.WriteLine( "This combination has count > 1: { JOB: '" + group.Key.JOB  + "', LINK: '" + group.Key.LINK + "' } with count of: " + group.Count());
}
相关文章：