LINQ groupby statement with key
The syntax of my query is good but not the output and it's really strange.
I have the following table:
| 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 is a byte where 1 is for set, 2 is for attended, 3 is for rescheduled and 4 is for cancelled. AppointDatetime is the date for which is appointment is set.
What I'm trying to do is create the following output that counts the activity by day.
| 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 | |
This is what I have so far. TheDate is a date within the month I'm querying for (ie, pass March 4th and should return the March table)
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(),
One of the problem is that CountTotalSetOnDay is returning only the count of those that are set and resolved the same day; the other problem is that ViewDate needs to return all the dates: there are dates when there are no appointments set but appointments attended, rescheduled or cancelled, and vice versa, there are dates when appointments are set but none are resolved.
For now I'm running this with 2 queries and I join the results: one query returns the appointments set and the other returns the appointments resolved. However, I'm still stuck on the solution with one query in one read.
Any suggestions?
Thanks.
You need to group using both the Set date and the Resolved date. This could easily be achieved by adding another from clause doing a cross join with the set of dates and grouping by those dates.
The query below is based on a LINQ to Objects query. LINQ to SQL does not support this kind of query so you'll have to decide if you want to use this which runs on the client machine or on the server.
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),
};
Otherwise for a completely LINQ to SQL solution (not the cleanest) that should work, you can try this. There might be a nicer way to do this but I don't know of it.
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),
};
You are grouping by AppointResolved.Date.
Therefore, for each entry in daygroups, daygroups.Key == AppointResolved.Date, and the c.AppointSet.Date == daygroups.Key where clause will only give you all the entries where the appointment was set the same day as it was resolved.
It seems to me that if you want to achieve your stated goal, you will have to list all dates mentioned in ANY of the fields of your table and count them.
For example, consider:
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() }
This should give you a list of the dates where appointments where set, and for each, how many of them.
我猜想2个读取查询的解决方案工作得很好，现在就会有效。
链接地址: http://www.djcxy.com/p/5478.html上一篇: 实体框架在select上写入锁定
下一篇: LINQ groupby语句与键