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

Answer 2

您正在按 AppointResolved.Date 进行分组。

因此，对于 daygroups 中的每个条目，daygroups.Key == AppointResolved.Date 和 c.AppointSet.Date == daygroups.Key where 子句只会为您提供在解决约会的同一天设置约会的所有条目。

在我看来，如果您想实现既定目标，则必须列出表格任何字段中提到的所有日期并计算它们。

例如，考虑一下：

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

这应该为您提供一个设置约会的日期列表，以及每个约会的数量。

Answer 3

我想带有 2 个读取查询的解决方案工作得很好，并且现在可以使用。