Nicky's Tech Blog

Auckland High School is a basic exercise for understanding more on LINQ and ASP.NET MVC.

You can visit the site from http://www.nickyli.net/.

The source code can be downloaded  from GitHub.

The contents of the exercise (italic) are from MVP Studio.

Scenario

You are going to design a database and a website based on that database for an educational organisation called ‘Auckland High School’

Auckland High School has five properties:

• Subject includes name only, each subject may have multiple classes or none
• Teacher includes teacher information (first name, last name, and gender). Each teacher may teach multiple classes or none
• Student also includes student information (first name, last name, and gender). Each student may enroll in multiple classes or none
• Class includes class name, teacher information, and subject information. Each class should be taken by single teacher only
• Enrollment includes class information and student information

The EDM model is as follows (Click to enlarge the diagram).

Task 1:

• Design a database based on given information and define any necessary relationships or keys
• Create a MVC project and connect with the existed database by using entity framework
• Create a main page which has four links to link to four different categories (Subject, Class, Student, and Teacher)
• Create four different pages which show a list of each category (Subject, Class, Student, and Teacher)

Note: We need at least 6 different subjects, 8 different students (4 males and 4 females), 4 different teachers (2 males and 2 females) and 4 different classes. You can choose whatever names you like.

Task One is just the preparation of the whole website. Firstly I created the Database. Then I use Database First mode, creating EDM model and the POCO entities. At last I use the scaffolding feature of VS to create the four pages that show the related lists.

Here is the student list.

Task 2:

• Subject page: show number of staffs, classes and enrollments belong to each subject
• Teacher page: show number of classes and subjects belong to each teacher
• Student page: show number of enrollments each student has enrolled in
• Class page: show which subject each class belong to, who is teaching that class and its enrollment count.

Note: Be careful when you perform joins between different tables.

Task Two is a bit tricky. I prefer using navigation property. So for subject page:

public ActionResult SubjectList()
{
    using (AucklandHighSchoolEntities db = new AucklandHighSchoolEntities())
    {
        var list = db.Subjects.Select(x => new SubjectViewModel
        {
            Id = x.SubjectID,
            Name = x.Name,
            ClassCount = x.Classes.Select(y => y.ClassID).Distinct().Count(),
            TeacherCount = x.Classes.Select(y => y.TeacherID).Distinct().Count(),
            EnrollmentsCount = x.Classes.SelectMany(y => y.Enrollments.Select(z => z.EnrollmentID)).Distinct().Count()
        }).ToList();

        return View(list);
    }       
}

I use SelectMany because we need to put the enrollments of each classes in one array, not in a nested array that group by classes. i.e.{1,2,3,4,5,6,7,8} instead of {{1,2,3},{4,5,6},{7,8,9}}.

For teacher page:

public ActionResult TeacherList()
{
    using (AucklandHighSchoolEntities db = new AucklandHighSchoolEntities())
    {
        var list = db.Teachers.Select(x => new TeacherViewModel
        {
            Id = x.TeacherID,
            Name = x.FirstName + " " + x.LastName,
            Gender = x.Gender,
            ClassCount = x.Classes.Select(y => y.ClassID).Distinct().Count(),
            SubjectCount = x.Classes.Select(y => y.SubjectID).Distinct().Count()
        }).ToList();

        return View(list);
    }
}

For student page:

public ActionResult StudentList()
{
    using (AucklandHighSchoolEntities db = new AucklandHighSchoolEntities())
    {
        var list = db.Students.Select(x => new StudentViewModel
        {
            Id = x.StudentID,
            Name = x.FirstName + " " + x.LastName,
            Gender = x.Gender,
            EnrollmentCount = x.Enrollments.Select(y => y.EnrollmentID).Distinct().Count()
        }).ToList();

        return View(list);
    }
}

For class page

public ActionResult ClassList()
{
    using (AucklandHighSchoolEntities db = new AucklandHighSchoolEntities())
    {
        var list = db.Classes.Select(x => new ClassViewModel
        {
            Id = x.ClassID,
            Name = x.Name,
            Subject = x.Subject.Name,
            Teacher = x.Teacher.FirstName + " " + x.Teacher.LastName,
            EnrollmentsCount = x.Enrollments.Select(y => y.EnrollmentID).Distinct().Count()
        }).ToList();

        return View(list);
    }
}

There is another way we can make it, the join statement in LINQ. We will have a deep look into it in next post.

** You can download the source code on GitHub