Need some groupby Linq2Sql help, please.

Question

Hi folks,

I need to display a history all our Products we've sold by day, week, month and year. This data will be sent to google charts API to display a line graph of the results.

So if i have a table called Products and it looks like:-

Products

ProductID INT
DateCreated DATETIMEOFFSET

and the user asks to see the history for ProductID 1. How could i retrieve this? eg output.

Graph 1 (Dates vs Sale Count)

Monday 1st Dec: 0
Tuesday 2nd Dec: 3
Wed 3rd Dec: 1

Graph 2 (Weeks vs Sale Count)

Week 49 2008: 21
Week 50 2008: 11
Week 51 2008: 45
Week 52 2008: 0

Graph3 (Months vs Sale Count)

Dec 08: 102
Jan 09: 23

I'm not sure if the 'by day' can be done ... or any of it.

cheers :)

Update 1 : got part of it working...

After spending a bit of time, i got the first one working... but still need help on the other two AND making it all part of one query...

from p in Products
where p.ProductId == 69
group p.DateCreated by p.DateCreated.Date into grouping
orderby grouping.Key
select new { Date = grouping.Key, Count = grouping.Count() }

Answer 1

I don't know how to do it with a LINQ expression, but you could write a stored procedure using the PIVOT operator. See Using PIVOT and UNPIVOT.

Answer 2

var data = from p in ctx.Products
           where p.ProductID == *productId*
           group p by p.DateCreated.DayOfWeek into groupedProducts
           select new { DayOfWeek = groupedProducts.Key, Count = groupedProcuts.Count() };

Without testing I think that may do it for you

To do it per-year something like this:

var data = from p in ctx.Products
           where p.ProductID == *productId*
           group p by n.CreateDate.Year into gn
           select new {
            Count = from a in gn
                 group a by a.CreateDate.DayOfYear into aa
                 select new { Count = aa.Count(), Key = new DateTime(gn.Key , 1, 1).AddDays(aa.Key) }
           };

(Sorry about the variable names :P)

Answer 3

Use DateTime.Date, DateTime.Month and DateTime.Year. However there is a bug in LINQ-to-SQL that sometimes translates those to invalid SQL. As a workaround you can group by SqlMethods.DateDiffDay, SqlMethods.DateDiffMonth and SqlMethods.DateDiffYear from say '1980-01-01'.

The week part is not so easy. Start of the week depends on Sql Server settings so it's not reliable. Alternatively you can use SqlMethods.DateDiffDay from some date that starts the week in your case and divide day difference by 7. If you need the week number in a given year you should calculate day difference from the most recent week start day that is less or equal to start of the year.