LINQ to SQL -分组依据

Question

我正在尝试弄清楚如何为下面的SQL创建LINQ- to -SQL，但没有任何运气。任何帮助都将不胜感激。最好使用响应的C#代码。此外，为简洁起见，未显示PKey以外的索引。

表架构：

CREATE TABLE [dbo].[FileHashes](
[ID] [uniqueidentifier] NOT NULL,
[FileName] [nvarchar](255) NULL,
[FileSize] [bigint] NULL,
[FileDirectory] [nvarchar](255) NULL,
[SHA-1] [nchar](40) NULL,
[MD5] [nchar](32) NULL,
[CRC32] [nchar](8) NULL,
CONSTRAINT [PK_FileHashes] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)) ON {PRIMARY]

T-SQL：

select COUNT(ID) as DupCount, FileName from FileHashes
 group by [SHA-1], FileName, FileSize
 having COUNT(ID) > 1
 order by DupCount desc, FileSize desc

我离这个越来越近了，但还没到那一步：

from f in FileHashes
 group f by new { f.SHA_1, f.FileName, f.FileSize } into g
 orderby g.Key.FileSize descending
 where g.Count() > 1
 select new { g.Key.SHA_1, g.Key.FileName, g.Key.FileSize }

提前谢谢你，--Ed

Answer 1

我相信这就是你想要的，这适用于Northwind数据库，Orders表

var orders =
      from p in Orders
      group p by new {p.CustomerID, p.ShipCity} into g
      let OrderCount = g.Count()
      orderby OrderCount descending, g.Key.ShipCity descending
      where OrderCount > 2
      select new
          { 
              CustomerID = g.Key.CustomerID, 
              City = g.Key.ShipCity, 
              NoOfOrders = g.Count() 
          };    

转置您的查询，它应该是...

var files =
      from f in FileHashes
      group f by new { f.SHA_1, f.FileName, f.FileSize } into g
      let DupCount = g.Count()
      orderby DupCount, g.Key.FileSize descending
      where DupCount > 1
      select new 
      { 
          g.Key.SHA_1, 
          g.Key.FileName, 
          g.Key.FileSize 
      };

Answer 2

尝试：

var result = from f in FileHashes
    group by new {f.SHA-1, f.FileName, F.FileSize} into g
    orderby g.DupCount desc, g.FileSize desc
    where g.Count(ID) > 1
    select g.DupCount = g.Count(ID), g.FileName