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