LINQ to SQL - Group By

Question

I'm trying to figure out how to create the LINQ-to-SQL for the following SQL and not having any luck. Any help would be appreciated. C# code for the response is prefered. Also, indexes other than PKey not shown for brevity.

Table Schema:

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

I'm getting closer with this, but still not there yet:

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 }

Thanks in advance, --Ed

Answer 1

Try:

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

Answer 2

I believe this is what you would like, this works for the Northwind database, Orders table

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() 
          };

Transposing your query it should be...

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 
      };