linq group by: good syntax but weird output
The syntax of my query is good but not the output and it's really strange.
I have the following table:
| 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 |
Note that I've defined the fields AppointAttended, AppointCancelled and AppointRescheduled as nullable because there might not be a date for these.
The query is as follows:
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 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.
Thanks.
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
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.
Assuming the following sample data:
// 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()
});
This should result in the following output:
Is this what you're trying to do?
您可以为每列生成单独的计数，然后将它们连接在一起，就像这样（我只对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)
});
If you can't change the schema, I'd start by reshaping the data a little:
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
})
Now we make a 2 flat tables of dates vs reasons (0,1,2 are attended,cancelled,rescheduled and 4 is set)
var setDates = appointments
.Select(a => new {Date = a.Date, Reason = 4});
var otherDates = appointments
.Select(a => new {Date = a.resolveDate,Reason = a.ResolveReason});
UnionAll the two tables above:
var allDates = setDates.Concat(otherDates);
And group:
var groups = allDates.GroupBy(a => new{a.Date, a.Reason});
So to query a specific day:
groups
.Where(g => g.Key.Date == someDate)
.Select(g=> new{g.Key.Reason, Count = g.Key.Count()})
which should (if my completely untested code is anywhere near working) show 4 rows with counts for each reason.
Not the shape you want, but it wouldn't be difficult to pivot if that's what you need.
链接地址: http://www.djcxy.com/p/31910.html上一篇: LINQ聚合算法解释