// GET: List of Students public ActionResult Index() { using(var db = new AucklandHighSchoolEntities()) { //Declear list of students List<StudentViewModel> list = new List<StudentViewModel>(); //Connect to database and get list list = (from s in db.Students join e in db.Enrollments on s.Id equals e.StudentId into box from b in box.DefaultIfEmpty() select new { Student = s, Enrollment = b }).GroupBy(x => new { x.Student }).Select(x => new StudentViewModel { Name = x.Key.Student.FirstName + " " + x.Key.Student.LastName, Gender = x.Key.Student.Gender, EnrolmentCount = x.Where(g => g.Enrollment != null).Distinct().Count() }).ToList(); //Pass list to front end view return View(list); } }
// GET: List of Teachers public ActionResult Index() { using (var db = new AucklandHighSchoolEntities()) { //Declear list of teachers List<TeacherViewModel> list = new List<TeacherViewModel>(); //Get number of classes each teacher teaches var classCount = (from t in db.Teachers join c in db.Classes on t.Id equals c.TeacherId into box from b in box.DefaultIfEmpty() select new { Teacher = t, Class = b }).GroupBy(x => new { x.Teacher }).Select(x => new { Id = x.Key.Teacher.Id, Name = x.Key.Teacher.FirstName + " " + x.Key.Teacher.LastName, Gender = x.Key.Teacher.Gender, ClassCount = x.Where(g => g.Class != null).Distinct().Count() }); //Get number of subjects each teacher teaches var subjectCount = (from t in db.Teachers join c in db.Classes on t.Id equals c.TeacherId into box from b in box.DefaultIfEmpty() join s in db.Subjects on b.SubjectId equals s.Id into otherBox from o in otherBox.DefaultIfEmpty() select new { Teacher = t, Subject = o }).GroupBy(x => new { x.Teacher }).Select(x => new { Id = x.Key.Teacher.Id, SubjectCount = x.Where(g => g.Subject != null).Distinct().Count() }); //Join two above list to get final list by comparing teacher ids list = (from c in classCount join s in subjectCount on c.Id equals s.Id select new TeacherViewModel { Name = c.Name, Gender = c.Gender, SubjectCount = s.SubjectCount, ClassCount = c.ClassCount }).ToList(); //Pass list to front end view return View(list); } }
// GET: List of Classes public ActionResult Index() { using (var db = new AucklandHighSchoolEntities()) { //Declear list of classes List<ClassViewModel> list = new List<ClassViewModel>(); //Get list of subject names var subjectName = (from c in db.Classes join s in db.Subjects on c.SubjectId equals s.Id into box from b in box.DefaultIfEmpty() select new { Id = c.Id, ClassName = c.Name, SubjectName = b == null ? "" : b.Name }); //Get list of teacher names var teacherName = (from c in db.Classes join t in db.Teachers on c.TeacherId equals t.Id into box from b in box.DefaultIfEmpty() select new { Id = c.Id, TeacherName = (b == null) ? "" : b.FirstName + " " + b.LastName }); //Get number of enrolments belong to each class var enrollmentCount = (from c in db.Classes join e in db.Enrollments on c.Id equals e.ClassId into box from b in box.DefaultIfEmpty() select new { Id = c.Id, Enrollment = b }).GroupBy(x => new { x.Id }).Select(x => new { Id = x.Key.Id, EnrollmentCount = x.Where(g => g.Enrollment != null).Distinct().Count() }); //Join three above list to get final list by comparing class ids list = (from s in subjectName join e in enrollmentCount on s.Id equals e.Id join t in teacherName on s.Id equals t.Id select new ClassViewModel { ClassName = s.ClassName, TeacherName = t.TeacherName, SubjectName = s.SubjectName, EnrolmentCount = e.EnrollmentCount }).ToList(); //Pass list to front end view return View(list); } }
// GET: List of Subjects public ActionResult Index() { using (var db = new AucklandHighSchoolEntities()) { //Declear list of subjects List<SubjectViewModel> list = new List<SubjectViewModel>(); //Get number of classes belong to each subject var classCount = (from s in db.Subjects join c in db.Classes on s.Id equals c.SubjectId into box from b in box.DefaultIfEmpty() select new { Subject = s, Class = b }).GroupBy(x => new { x.Subject }).Select(x => new { Id = x.Key.Subject.Id, Name = x.Key.Subject.Name, ClassCount = x.Where(g => g.Class != null).Distinct().Count() }); //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() }); //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() }); //Join three above list to get final list by comparing subject ids 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 { Name = c.Name, ClassCount = c.ClassCount, StaffCount = s.StaffCount, EnrolmentCount = e.EnrollmentCount }).ToList(); //Pass list to front end view return View(list); } }