LINQ to SQL - 按小時分組

考慮到列類型是 DateTime，如何按小時對 LINQ to SQL 查詢的結果進行分組？

這是技術時間的解決方案（無上下文）。

var query = myDC.Orders
   .GroupBy(x => x.OrderDate.Hour)
   .Select(g => new {
       Hour = g.Key,
       Amount = g.Sum(x => x.OrderAmount)
   });

這會產生：

SELECT SUM([t1].[OrderAmount]) AS [Amount], [t1].[value] AS [Hour]
FROM (
   SELECT DATEPART(Hour, [t0].[OrderDate]) AS [value], [t0].[OrderAmount]
   FROM [dbo].[Orders] AS [t0]
   ) AS [t1]
GROUP BY [t1].[value]

---

這是營業時間的解決方案（上下文相關）。

DateTime zeroDate = new DateTime(2008, 1, 1);

var query = myDC.Orders
   .GroupBy(x => System.Data.Linq.SqlClient.SqlMethods.DateDiffHour(zeroDate, x.OrderDate)
   )
   .Select(g => new { Hours = g.Key, Amount = g.Sum(x => x.OrderAmount) })
   .Select(x => new { Hour = zeroDate.AddHours(x.Hours), Amount = x.Amount});

這會產生：

SELECT DATEADD(ms, (CONVERT(BigInt,(CONVERT(Float,[t2].[value2])) * 3600000)) % 86400000, DATEADD(day, (CONVERT(BigInt,(CONVERT(Float,[t2].[value2])) * 3600000)) / 86400000, @p1)) AS [Hour], [t2].[value] AS [Amount]
FROM (
   SELECT SUM([t1].[OrderAmount]) AS [value], [t1].[value] AS [value2]
   FROM (
       SELECT DATEDIFF(Hour, @p0, [t0].[OrderDate]) AS [value], [t0].[OrderAmount]
       FROM [dbo].[Orders] AS [t0]
       ) AS [t1]
   GROUP BY [t1].[value]
   ) AS [t2]

---

呃 - bigint/float/milliseconds 的東西很難看而且很難驗證。我更喜歡在客戶端進行添加：

var results = myDC.Orders
   .GroupBy(x => System.Data.Linq.SqlClient.SqlMethods.DateDiffHour(zeroDate, x.OrderDate)
   )
   .Select(g => new { Hours = g.Key, Amount = g.Sum(x => x.OrderAmount) })
   .ToList()
   .Select(x => new { Hour = zeroDate.AddHours(x.Hours), Amount = x.Amount});

這會產生：

SELECT SUM([t1].[OrderAmount]) AS [Amount], [t1].[value] AS [Hours]
FROM (
   SELECT DATEDIFF(Hour, @p0, [t0].[OrderDate]) AS [value], [t0].[OrderAmount]
   FROM [dbo].[Orders] AS [t0]
   ) AS [t1]
GROUP BY [t1].[value]

---

這是進行上下文時間的第三種方法。這個對 c# 非常友好，但是數據庫中有字元串邏輯（糟糕）。

var query = myDC.Orders
   .GroupBy(x => new DateTime(x.OrderDate.Year, x.OrderDate.Month, x.OrderDate.Day, x.OrderDate.Hour, 0, 0))
   .Select(g => new { Hour = g.Key, Amount = g.Sum(x => x.OrderAmount) });

產生

SELECT SUM([t1].[OrderAmount]) AS [Amount], [t1].[value] AS [Hour]
FROM (
   SELECT CONVERT(DATETIME, (CONVERT(NCHAR(4), DATEPART(Year, [t0].[OrderDate])) + ('-' + (CONVERT(NCHAR(2), DATEPART(Month, [t0].[OrderDate])) + ('-' + CONVERT(NCHAR(2), DATEPART(Day, [t0].[OrderDate])))))) + (' ' + (CONVERT(NCHAR(2), DATEPART(Hour, [t0].[OrderDate])) + (':' + (CONVERT(NCHAR(2), @p0) + (':' + CONVERT(NCHAR(2), @p1)))))), 120) AS [value], [t0].[OrderAmount]
   FROM [dbo].[Orders] AS [t0]
   ) AS [t1]
GROUP BY [t1].[value]

引用自：https://stackoverflow.com/questions/575042