linq groupby question

Question

I have a table that has several columns: HarvestID, HarvestDate, UserID to mention the main ones. For each date, there are going to be several HarvestID per day.

So far, I have the following linq query:

TheUserID and TheMonth are passed in as an int and a DateTime

var MyQuery = from h in MyDC.HarvestTable
              where h.UserID == TheUserID
              where h.HarvestDate.Month == TheMonth.Month
              where h.HarvestDate.Year == TheMonth.Year
              group h by h.HarvestDate.Day into TheDays
              from d in TheDays
              select new
              {
                  TheDay = d.HarvestDate.Date,
                  TheDayCount = (from c in TheDay
                                 select c.HarvestID).Count()
              };

I'm looking to have the output be a list of counts per day. The query doesn't bug but the problem is that at the moment the query is not returning a unique row for each day. The grouping doesn't work and I'm not finding out why. What's wrong with this code?

Thanks.

score 2 · Answer 1 · answered Jan 20 '11 at 04:46

2

var MyQuery = from h in MyDC.HarvestTable
              where h.UserID == TheUserID
              && h.HarvestDate.Month == TheMonth.Month
              && h.HarvestDate.Year == TheMonth.Year
              group h by h.HarvestDate.Day into g
              select new
              {
                  TheDay = g.Key,
                  TheDayCount = g.Count()
              };

This will not give you zeroes on the days where there is no data - but should give you a count where there is.

http://msdn.microsoft.com/en-us/vcsharp/aa336746 is my go-to page for LINQ examples.

answered Jan 20 '11 at 04:46

paulash

31
2

The problem is that TheDay is a DateTime. I tried (DateTime)TheDays.Key but the cast is not working. – frenchie Jan 20 '11 at 05:51
I got it. TheDay is a date, it has nothing to do with the Key. I just removed that line and replaced it with TheDay = (from c in TheDay select c.Date).First(); Is this statement going to cause me problems later? – frenchie Jan 20 '11 at 06:04

score 2 · Accepted Answer · answered Jan 20 '11 at 04:53

2

It think your query should be

var MyQuery = from h in MyDC.HarvestTable
              where h.UserID == TheUserID
              where h.HarvestDate.Month == TheMonth.Month
              where h.HarvestDate.Year == TheMonth.Year
              group h by h.HarvestDate.Day into TheDays
              select new
              {
                  TheDay = TheDays.Key,
                  TheDayCount = TheDays.Count()
              };

Here is a vry good refrence to above group by statement http://msdn.microsoft.com/en-us/vcsharp/aa336754.aspx#simple1

answered Jan 20 '11 at 04:53

Shekhar_Pro

18,056
9
55
79

The problem is that TheDay is a DateTime. I tried (DateTime)TheDays.Key but the cast is not working. – frenchie Jan 20 '11 at 05:50
I got it. TheDay is a date, it has nothing to do with the Key. I just removed that line and replaced it with TheDay = (from c in TheDay select c.Date).First(); Is this statement going to cause me problems later? – frenchie Jan 20 '11 at 06:04
TheDays.Key is an Integer as DateTime.Day returns an Integer representing the Day of the month – Shekhar_Pro Jan 20 '11 at 06:07
you should create a new date by TheDay = new DateTime(TheMonth.Year,TheMonth.Month,TheDays.Key) – Shekhar_Pro Jan 20 '11 at 06:11
ok, thanks for the clarification; you put me on the right path. – frenchie Jan 20 '11 at 06:14
What's the difference between group h by h.HarvestDate.Day into TheDays and group h by h.HarvestDate.Date into TheDays. What's better? – frenchie Jan 20 '11 at 06:15
Since in your case you want Date in the TheDay variable you should go with the Second one as it gives the Date component of the HarvestDate and so the TheDays.Key will give you a date instead of the integer – Shekhar_Pro Jan 20 '11 at 06:19

linq groupby question

2 Answers2