title: LINQ groupby statement with key
body: <p>The syntax of my query is good but not the output and it's really strange.</p>
  
  <p>I have the following table:</p>
  
  <pre><code>| AppointID | UserID | AppointSet | AppointResolved |  AppointStatus | AppointmentDatetime
  |     1     |   1    |  3/1/2011  |   3/1/2011      |      1         |     3/15/2011
  |     2     |   1    |  3/2/2011  |   3/5/2011      |      4         |     3/16/2011
  |     3     |   1    |  3/2/2011  |   3/11/2011     |      2         |     3/11/2011
  |     4     |   1    |  3/3/2011  |   3/7/2011      |      3         |     3/25/2011
  </code></pre>
  
  <p>ApponintStatus is a byte where 1 is for set, 2 is for attended, 3 is for rescheduled and 4 is for cancelled. AppointDatetime is the date for which is appointment is set.</p>
  
  <p>What I'm trying to do is create the following output that counts the activity by day.</p>
  
  <pre><code>|    Date     |   Set   |   Attended   |   Rescheduled   |   Cancelled   |
  |  3/1/2011   |    1    |              |                 |               |
  |  3/2/2011   |    2    |              |                 |               |
  |  3/3/2011   |    1    |              |                 |               |
  |  3/5/2011   |         |              |                 |      1        |
  |  3/7/2011   |         |      1       |                 |               |
  |  3/11/2011  |         |              |        1        |               |
  </code></pre>
  
  <p>This is what I have so far. TheDate is a date within the month I'm querying for (ie, pass March 4th and should return the March table)</p>
  
  <pre><code>var r = from appnt in MyDC.LeadsAppointments
          where appnt.UserID == TheUserID
          where (appnt.AppointResolved.Year == TheDate.Year &amp;&amp; appnt.AppointResolved.Month == TheDate.Month) ||
          (appnt.AppointSet.Year == TheDate.Year &amp;&amp; appnt.AppointSet.Month == TheDate.Month)
          group appnt by appnt.AppointResolved.Date into daygroups
          select new ViewMonthlyActivityModel()
          {
              ViewDate = (from d in daygroups
                          select daygroups.Key.Date).First(), // Problem here: need to get dates for instances where an appointment is set but none are resolved
  
              CountTotalSetOnDay = (from c in daygroups
                                    where c.AppointSet.Date == daygroups.Key // Problem here
                                    select c.AppointID).Count(),
  
              CountAttendedOnDay = (from c in daygroups
                                    where c.AppointResolved.Date == daygroups.Key.Date
                                    where c.AppointStatus == 2
                                    select c.AppointID).Count(),
  </code></pre>
  
  <p>One of the problem is that CountTotalSetOnDay is returning only the count of those that are set and resolved the same day; the other problem is that ViewDate needs to return all the dates: there are dates when there are no appointments set but appointments attended, rescheduled or cancelled, and vice versa, there are dates when appointments are set but none are resolved.</p>
  
  <p>For now I'm running this with 2 queries and I join the results: one query returns the appointments set and the other returns the appointments resolved. However, I'm still stuck on the solution with one query in one read.</p>
  
  <p>Any suggestions?</p>
  
  <p>Thanks.</p>