LINQ到SQL - 分组由小时 [英] LINQ to SQL - Grouping by hours
问题描述
我怎么能一个LINQ to SQL查询时所考虑到列类型为日期时间?组结果
How can I group result of a LINQ to SQL query by hours considering that the column type is DateTime?
推荐答案
下面是技术小时(上下文更少)的解决方案。
Here's a solution for technical hours (context-less).
var query = myDC.Orders
.GroupBy(x => x.OrderDate.Hour)
.Select(g => new {
Hour = g.Key,
Amount = g.Sum(x => x.OrderAmount)
});
产生这样的:
Which generates this:
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]
下面是营业时间(上下文FUL)的解决方案。
Here's a solution for business hours (context-ful) .
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});
产生这样的:
Which generates this:
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 /浮点/毫秒的东西是丑陋和难以核实。我preFER做加法回客户端:
Ugh - that bigint/float/milliseconds stuff is ugly and hard to verify. I prefer doing the addition back on the client side:
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});
产生这样的:
Which generates this:
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]
和这里做contextful小时的第三条道路。这一个是非常C#友好,但有串逻辑数据库(呸)。
And here's a third way of doing the contextful hours. This one is very c# friendly, but there's string logic in the database (yuck).
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]
这篇关于LINQ到SQL - 分组由小时的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!