您需要使用设置日期和解决日期进行分组。这可以通过添加另一个来轻松实现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),
};