linq group by：语法不错，但输出很奇怪

Question

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

我有下表：

| AppointID | UserID | AppointSet | AppointAttended | AppointCancelled | AppointRescheduled | AppointmentDatetime
|     1     |   1    |  2/15/2011 |                 |   3/11/2011      |                    |  3/15/2011
|     2     |   1    |  2/17/2011 |                 |                  |    3/11/2011       |  3/10/2011
|     3     |   1    |  3/11/2011 |   3/11/2011     |                  |                    |  3/25/2011
|     4     |   1    |  3/10/2011 |                 |   3/11/2011      |                    |  3/11/2011

我想要做的是创建以下输出，按天计算活动。

|    Date     |   Set   |   Attended   |   Rescheduled   |   Cancelled   |
|  3/10/2011  |    1    |              |                 |               |
|  3/11/2011  |    1    |      1       |       1         |      2        |

请注意，我已将 AppointAttending、AppointCancelled 和 AppointRescheduled 字段定义为可为空，因为这些字段可能没有日期。

查询如下：

var OutputMonthlyActivity = from appnt in MyDC.LeadsAppointments
where appnt.UserID == TheUserID
where (appnt.AppointSet.Year == TheDate.Year && appnt.AppointSet.Month == TheDate.Month) ||
(appnt.AppointAttended.Value.Year == TheDate.Year && appnt.AppointAttended.Value.Month == TheDate.Month) ||
(appnt.AppointRescheduled.Value.Year == TheDate.Year && appnt.AppointRescheduled.Value.Month == TheDate.Month) ||
(appnt.AppointCancelled.Value.Year == TheDate.Year && appnt.AppointCancelled.Value.Month == TheDate.Month)
group appnt by new { SetOn = appnt.AppointSet.Date, ReschedOn = appnt.AppointRescheduled.Value.Date, Attended = appnt.AppointAttended.Value.Date, Cancelled = appnt.AppointCancelled.Value.Date } into daygroups
select new ViewMonthlyActivityModel()
{

ViewDate = (from d in daygroups select d.AppointDatetime.Date).First(),

CountTotalSetOnDay = (from c in daygroups
where c.AppointSet.Date == daygroups.Key.SetOn
select c.AppointID).Count(),

CountAttendedOnDay = (from c in daygroups
where c.AppointAttended.HasValue == true
select c.AppointID).Count(),

CountRescheduledOnDay = (from c in daygroups
where c.AppointRescheduled.HasValue == true
select c.AppointID).Count(),

CountCancelledOnDay = (from c in daygroups
where c.AppointCancelled.HasValue == true
select c.AppointID).Count()

};

TheDate 是我传递的一个参数，代表我正在查询的月份中的日期（即 3 月 1 日）。问题是，除了 3 月的所有日期之外，我还得到 2 月最后 2 天和 4 月前 2 天的结果。另外，每天都会出现 3 行。

如果您有任何建议，我们将不胜感激。

谢谢。

Answer 1

您可以为每一列生成单独的计数，然后将它们全部连接在一起，如下所示（我只为 AppointSet 和 AppointAttend 执行此操作，但其余的很简单）：

        var userAppoints = from appnt in MyDC.LeadsAppointments
                           where appnt.UserID == TheUserID
                           select appnt;

        var appntSets = from appnt in userAppoints
                        where appnt.AppointSet.Year == TheDate.Year && appnt.AppointSet.Month == TheDate.Month
                        group appnt by appnt.AppointSet
                        into groups
                        select new ViewMonthlyActivityModel()
                            {
                                ViewDate = groups.Key,
                                CountTotalSetOnDay = groups.Count()
                            };

        var appntAttends = from appnt in userAppoints
                           where appnt.AppointAttended != null && appnt.AppointAttended.Value.Year == TheDate.Year && appnt.AppointAttended.Value.Month == TheDate.Month
                           group appnt by appnt.AppointAttended.Value
                           into groups
                           select new ViewMonthlyActivityModel()
                               {
                                   ViewDate = groups.Key,
                                   CountAttendedOnDay = groups.Count()
                               };

        var allModels = appntSets.Concat(appntAttends).GroupBy(a => a.ViewDate, (date, models) => new ViewMonthlyActivityModel 
        { 
            ViewDate = date, 
            CountTotalSetOnDay = models.Aggregate(0, (seed, model) => seed + model.CountTotalSetOnDay), 
            CountAttendedOnDay = models.Aggregate(0, (seed, model) => seed + model.CountAttendedOnDay) 
        });

Answer 2

，我会首先稍微重塑数据：

var appoinments = MyDC
.LeadsAppointments
.Select(la => new 
    {
        somefields = somedata, //fill in the blanks
        resolveDate =
            la.AppointAttended  ??
            la.AppointCancelled ??
            la.AppointRescheduled,
        resolveReason = 
            la.AppointAttended != null ? 0 : la.AppointCancelled != null ? 1 : 2
    })

现在我们制作 2 个日期与原因的平面表（0,1,2 参加，取消，重新安排，4 已设置）

var setDates = appointments
    .Select(a => new {Date = a.Date, Reason = 4});

var otherDates = appointments
    .Select(a => new {Date = a.resolveDate,Reason = a.ResolveReason});

如果您无法更改模式 上面的两个表：

var allDates = setDates.Concat(otherDates);

和组：

var groups = allDates.GroupBy(a => new{a.Date, a.Reason});

所以要查询特定的一天：（

groups
    .Where(g => g.Key.Date == someDate)
    .Select(g=> new{g.Key.Reason, Count = g.Key.Count()})

如果我完全未经测试的代码几乎可以工作）应该显示 4 行，其中包含每个原因的计数。

不是您想要的形状，但如果您需要的话，旋转并不困难。

Answer 3

问题似乎是您的组有一些匿名对象作为其密钥。

您将按对​​象对每个约会进行分组：

new { SetOn = appnt.AppointSet.Date, ReschedOn = appnt.AppointRescheduled.Value.Date, Attended = appnt.AppointAttended.Value.Date, Cancelled = appnt.AppointCancelled.Value.Date } into daygroups

相反，您应该按实际键（例如 AppointmentDatetime）进行分组。

实际上，对于您想要做的事情，我认为您根本不需要分组。

假设有以下示例数据：

// First, get all appointments for the following user
var TheUserID = 1;
var appointments = from appointment in Appointments where appointment.UserID == TheUserID select appointment;

// Then, find all unique dates
var uniqueDates = appointments.Select(a => a.AppointSet)
    .Union(appointments.Where(a => a.AppointAttended.HasValue).Select(a => a.AppointAttended.Value))
    .Union(appointments.Where(a => a.AppointCancelled.HasValue).Select(a => a.AppointCancelled.Value))
    .Union(appointments.Where(a => a.AppointRescheduled.HasValue).Select(a => a.AppointRescheduled.Value))
    .Union(appointments.Select(a => a.AppointmentDatetime))
      // Filter by the month/year
    .Where(x => x.Month == TheDate.Month && x.Year == TheDate.Year)
      // Finally, select a new object for each unique date holding the counts
    .Select(x => new {
        Date = x,
        Cancelled = appointments.Where(a => a.AppointCancelled.HasValue && a.AppointCancelled.Value == x).Count(),
        Rescheduled = appointments.Where(a => a.AppointRescheduled.HasValue && a.AppointRescheduled.Value == x).Count(),
        Set = appointments.Where(a => a.AppointSet == x).Count(),
        Attended = appointments.Where(a => a.AppointAttended.HasValue && a.AppointAttended.Value == x).Count()
    });

这应该会产生以下输出：

这是您想要做的吗？