linq group by:语法不错，但输出奇怪 [英] linq group by: good syntax but weird output

问题描述

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

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

我有下表:

| 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

我想要做的是创建以下输出，该输出按天对活动进行计数.

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

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

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

Note that I've defined the fields AppointAttended, AppointCancelled and AppointRescheduled as nullable because there might not be a date for these.

查询如下:

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

TheDate is a parameter I pass that represents a date in the month I'm querying (ie March 1st). The problem is that in addition to all the dates of March, I'm getting results from the last 2 days of February and the first 2 days of April. Also, each day comes out with 3 rows.

如果您有任何建议，将不胜感激.

If you have any suggestions, that'd be really appreciated.

谢谢.

推荐答案

问题似乎是您的组将某些匿名对象作为其键.

The problem seems to be that your group has some anonymous object as its key.

您正在按对象将每个约会分组:

You are grouping each appointment by an object:

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

相反，您应该按实际键(例如AppointmentDatetime)分组.

Instead, you should be grouping by the actual key, such as the AppointmentDatetime.

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

Actually, for what you are trying to do, I don't believe you need to group by at all.

假设以下示例数据:

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

这将导致以下输出:

这是您要做什么吗?

这篇关于linq group by:语法不错，但输出奇怪的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！