LINQ groupby statement with key

Question

The syntax of my query is good but not the output and it's really strange.

I have the following table:

| 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

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.

What I'm trying to do is create the following output that counts the activity by day.

|    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        |               |

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)

var r = from appnt in MyDC.LeadsAppointments
        where appnt.UserID == TheUserID
        where (appnt.AppointResolved.Year == TheDate.Year && appnt.AppointResolved.Month == TheDate.Month) ||
        (appnt.AppointSet.Year == TheDate.Year && 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(),

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.

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.

Any suggestions?

Thanks.

Answer 1

You need to group using both the Set date and the Resolved date. This could easily be achieved by adding another from clause doing a cross join with the set of dates and grouping by those dates.

The query below is based on a LINQ to Objects query. LINQ to SQL does not support this kind of query so you'll have to decide if you want to use this which runs on the client machine or on the server.

const byte statusAttended = 2;
const byte statusRescheduled = 3;
const byte statusCancelled = 4;
var query = from a in MyDC.LeadsAppointments.AsEnumerable()
            from date in new[] { a.AppointSet.Date, a.AppointResolved.Date }
            where a.UserID == TheUserID
               && date.Year == TheDate.Year
               && date.Month == TheDate.Month
            group a by date into g
            orderby g.Key
            let set = g.Distinct().ToList()
            select new ViewMonthlyActivityModel
            {
                ViewDate = g.Key,
                CountTotalSetOnDay =
                    set.Count(a => a.AppointSet.Date == g.Key),
                CountTotalAttendedOnDay =
                    set.Count(a => a.AppointResolved.Date == g.Key
                                && a.AppointStatus == statusAttended),
                CountTotalRescheduledOnDay =
                    set.Count(a => a.AppointResolved.Date == g.Key
                                && a.AppointStatus == statusRescheduled),
                CountTotalCancelledOnDay =
                    set.Count(a => a.AppointResolved.Date == g.Key
                                && a.AppointStatus == statusCancelled),
            };

---

Otherwise for a completely LINQ to SQL solution (not the cleanest) that should work, you can try this. There might be a nicer way to do this but I don't know of it.

const byte statusAttended = 2;
const byte statusRescheduled = 3;
const byte statusCancelled = 4;
var query = from a in MyDC.LeadsAppointments
            let setDate = from aa in MyDC.LeadsAppointments
                          where aa.AppointID == a.AppointID
                          select aa.AppointSet.Date
            let resolvedDate = from aa in MyDC.LeadsAppointments
                               where aa.AppointID == a.AppointID
                               select aa.AppointResolved.Date
            from date in setDate.Concat(resolvedDate)
            where a.UserID == TheUserID
               && date.Year == TheDate.Year
               && date.Month == TheDate.Month
            group a by date into g
            orderby g.Key
            let set = g.Distinct()
            select new ViewMonthlyActivityModel
            {
                ViewDate = g.Key,
                CountTotalSetOnDay =
                    set.Count(a => a.AppointSet.Date == g.Key),
                CountTotalAttendedOnDay =
                    set.Count(a => a.AppointResolved.Date == g.Key
                                && a.AppointStatus == statusAttended),
                CountTotalRescheduledOnDay =
                    set.Count(a => a.AppointResolved.Date == g.Key
                                && a.AppointStatus == statusRescheduled),
                CountTotalCancelledOnDay =
                    set.Count(a => a.AppointResolved.Date == g.Key
                                && a.AppointStatus == statusCancelled),
            };

Answer 2

You are grouping by AppointResolved.Date.

Therefore, for each entry in daygroups, daygroups.Key == AppointResolved.Date, and the c.AppointSet.Date == daygroups.Key where clause will only give you all the entries where the appointment was set the same day as it was resolved.

It seems to me that if you want to achieve your stated goal, you will have to list all dates mentioned in ANY of the fields of your table and count them.

For example, consider:

var appointmentsTaken = (from appnt in GoyaDC.LeadsAppointments
                         where ... // your filters
                         group appnt by appnt.AppointSet.Date into daysset
                         select { Date = daysset.Key Count = daysset.Count() }

This should give you a list of the dates where appointments where set, and for each, how many of them.