Nicky's Tech Blog

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