linq group通过：良好的语法，但奇怪的输出
我的查询语法很好，但不是输出，它真的很奇怪。
我有下表：
| 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 |
请注意，我已将AppointAttended，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月份的头两天收到结果。 另外，每天都会出现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()
});
这应该导致以下输出：
这是你想要做的吗？
您可以为每列生成单独的计数，然后将它们连接在一起，就像这样（我只对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)
});
如果你不能改变模式，我将首先重塑数据：
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});
UnionAll上面的两个表格：
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行，每个原因计数。
不是你想要的形状，但如果这是你所需要的，它不会很难转动。
链接地址: http://www.djcxy.com/p/31909.html