// 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);
}
}
