c# - 带键的 LINQ groupby 语句

Question

我的查询语法很好，但输出不是，这真的很奇怪。

我有下表:

| 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 是一个字节，其中 1 表示设置，2 表示出席，3 表示重新安排，4 表示取消。 AppointDatetime 是设置约会的日期。

我想做的是创建以下按天计算事件的输出。

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

这就是我目前所拥有的。 TheDate 是我要查询的月份内的日期(即，通过 3 月 4 日并应返回 3 月表)

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(),

其中一个问题是 CountTotalSetOnDay 只返回当天设置和解决的数量；另一个问题是 ViewDate 需要返回所有日期:有些日期没有设置约会但有约会参加、重新安排或取消，反之亦然，有些日期设置了约会但没有解决。

目前，我使用 2 个查询运行此程序并加入结果:一个查询返回约会集，另一个返回已解决的约会。但是，我仍然停留在一次读取一个查询的解决方案上。

有什么建议吗？

谢谢。

Answer 1

您需要使用设置日期和解决日期进行分组。这可以通过添加另一个 from 子句来轻松实现，该子句对日期集进行交叉连接并按这些日期进行分组。

下面的查询基于 LINQ to Objects 查询。 LINQ to SQL 不支持这种查询，因此您必须决定是要使用在客户端计算机上还是在服务器上运行的查询。

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),
            };

---

否则，对于应该工作的完全 LINQ to SQL 解决方案(不是最干净的)，您可以试试这个。可能有更好的方法来执行此操作，但我不知道。

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),
            };