LINQ groupby语句与键
我的查询语法很好，但不是输出，它真的很奇怪。
我有下表：
| 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 | |
这是我迄今为止所拥有的。 日期是我查询的月份内的一个日期（例如，通过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需要返回所有的日期：有没有设置约会但约会出席，重新安排或取消的日期，反之亦然，有约会设置但没有解决的日期。
现在我用两个查询来运行这个查询，并加入结果：一个查询返回约会集，另一个查询返回已解决的约会。 然而，我仍然坚持在一次阅读中一个查询的解决方案。
有什么建议么？
谢谢。
您需要使用“设置”日期和“已解决”日期进行分组。 这很容易通过增加另一个实现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),
};
您正在按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() }
这应该给你一个预约日期的列表，其中每个预约设置了多少个。
我猜想2个读取查询的解决方案工作得很好，现在就会有效。
链接地址: http://www.djcxy.com/p/5477.html