LINQ to SQL - Grouping by hours?

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 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 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 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) }) Which generates 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.

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 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 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 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) }); Which generates 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.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.