LINQ statement to aggregate data

Question

Teachers can raise 'dramas' which are when students forget equipment, don't do homework or are flagged for concern. Each drama for each student is stored as a separate record.

I seek a list of all unresolved dramas, grouped by student & date. So if on one day a student forgets their homework and doesn't have equipment, they come up as a single record with the properties EquipmentDrama and HomeworkDrama both set to true.

var dramas = ctx.Dramas.Where(o => !o.Resolved).Select(o => new
 {
 o.Id,
 o.StudentUsername,
 o.Student.FirstName,
 o.Student.LastName,
 o.Student.TutorGroup,
 o.Student.ClassName,
 TeacherName = o.Teacher.FirstName + " " + o.Teacher.LastName,
 o.DramaType,
 o.DateForDetention,
 o.DateHappened
 }).ToLookup(o => o.StudentUsername + o.DateHappened.ToString("ddMMyy"), o => o);
var students = dramas.Select(o => o.Key).Distinct().Select(o => dramas[o].ToList()).Select(o => new FollowUpItem
 {
 Id = String.Join(",", o.Select(d => d.Id)),
 FirstName = o[0].FirstName,
 LastName = o[0].LastName,
 TutorGroup = o[0].TutorGroup,
 EquipmentDrama = o.Any(d => d.DramaType == DramaType.Equipment),
 HomeworkDrama = o.Any(d => d.DramaType == DramaType.Homework),
 IsFlagged = o.Any(d => d.DramaType == DramaType.Flagged),
 ClassName = o[0].ClassName,
 TeacherName = o[0].TeacherName,
 DateForDetention = o[0].DateForDetention,
 DateHappened = o[0].DateHappened
 }).ToList();

Note the reason for using an anonymous type in the first LINQ statement is that I want to retrieve the related fields o.Teacher.FirstName and o.Teacher.LastName in the initial (only) database call. There must be a better way of doing this though!

• svick: What LINQ provider are you using? Can't you just retrieve the whole Drama, Student and Teacher objects?
• James: Linq to SQL. The Person table which contains student and teacher info has lots of other fields which I don't need to retrieve.

Answer 1

It seems like you could do this:

var results = 
 (from d in ctx.Dramas
 where !d.Resolved
 group d by new { d.Student, DateHappened = d.DateHappened.ToString("ddMMyy") } into g
 select new
 {
 Student = g.Key.Student,
 DateHappened = g.Key.DateHappened,
 EquipmentDrama = g.Any(d => d.DramaType == DramaType.Equipment),
 HomeworkDrama = o.Any(d => d.DramaType == DramaType.Homework),
 IsFlagged = o.Any(d => d.DramaType == DramaType.Flagged),
 Dramas = g.Select(d => new 
 {
 d.Id,
 TeacherName = d.Teacher.FirstName + " " + d.Teacher.LastName,
 d.DateForDetention
 })
 });

NOTE: as far as I can tell, there's nothing limiting Teacher (or DateForDetention) to be the same in all grouped dramas; each drama could have a different Teacher. Here's an alternative that includes the teacher in the grouping:

var results = 
 (from d in ctx.Dramas
 where !d.Resolved
 group d by new { d.Student, d.Teacher, DateHappened = d.DateHappened.ToString("ddMMyy") } into g
 select new
 {
 Student = g.Key.Student,
 DateHappened = g.Key.DateHappened,
 EquipmentDrama = g.Any(d => d.DramaType == DramaType.Equipment),
 HomeworkDrama = o.Any(d => d.DramaType == DramaType.Homework),
 IsFlagged = o.Any(d => d.DramaType == DramaType.Flagged),
 TeacherName = d.Key.Teacher.FirstName + " " + d.Key.Teacher.LastName,
 Dramas = g.Select(d => new 
 {
 d.Id,
 d.DateForDetention
 })
 });

Of course, you could do the same for DateForDentention if you wanted.