From the last post we discussed how to get the number of classes belong to each subject.
Now we should focus on getting the number of teachers belong to each subject. It’s quite similar to the previous one.
//Get number of teachers belong each subject var staffCount = (from s in db.Subjects join c in db.Classes on s.Id equals c.SubjectId into box from b in box.DefaultIfEmpty() join t in db.Teachers on b.TeacherId equals t.Id into otherBox from o in otherBox.DefaultIfEmpty() select new { Subject = s, Teacher = o }).GroupBy(x => new { x.Subject }).Select(x => new { Id = x.Key.Subject.Id, StaffCount = x.Where(g => g.Teacher != null).Distinct().Count() });
Then get the number of enrollments belong to each subject
//Get number of enrolments belong each subject var enrollmentCount = (from s in db.Subjects join c in db.Classes on s.Id equals c.SubjectId into box from b in box.DefaultIfEmpty() join e in db.Enrollments on b.Id equals e.ClassId into otherBox from o in otherBox.DefaultIfEmpty() select new { Subject = s, Enrollment = o }).GroupBy(x => new { x.Subject }).Select(x => new { Id = x.Key.Subject.Id, EnrollmentCount = x.Where(g => g.Enrollment != null).Distinct().Count() });
At last, we join the three tables above to a list.
//Join three above list to get final list by comparing subject ids var list = (from c in classCount join s in staffCount on c.Id equals s.Id join e in enrollmentCount on c.Id equals e.Id select new SubjectViewModel { Id = c.Id, Name = c.Name, ClassCount = c.ClassCount, StaffCount = s.StaffCount, EnrolmentCount = e.EnrollmentCount }).ToList();
After building the front-end page, we finished our subject list page.
Similarly, we finish the teacher list page, the student list page and the class list page.
** You can download the source code on GitHub