Saturday, February 11, 2012

Entity Framework Code First - Relationship between Entities

This is the second post of our discussion about Entity Framework Code First. In this post we will be discussing how we can create relationship between two entities. Keeping to the code first ideology, code first entities would still be based on simple POCO classes. The relationship between entities are used by entity framework libraries to build the model and hence generate database if required.

As an example, we would be developing on top of our previous example. We have simply discussed how to create a simple application with Entity Framework Code First with one simple entity. Let's now expand this example by introducing courses in our institute. Students are assigned to these courses. Any number of students can be assigned to a course. These courses are offered at different campuses of the institute. The locations have physical presence and have a specific name. Since institute's parking space is limited so each student is allowed to register one vehicle. Each student belongs to a certain department.

Based on the above requirements, we can create following entities in our system.

  1. Student
  2. Vehicle
  3. Course
  4. CourseLocation
  5. Department

These are not stand alone entities but the requirements suggest that there are relationships between these entities.

  1. One-to-One: Since a Student is allowed to register only one vehicle, there is one-to-one relationship between the two.
  2. One-to-Many: A Department can enroll any number of students but a student only belongs to a single department. In this post, we will keep it simple by defining a zero-to-many relationship but we will see how we can fix it in the next post.
  3. Many-to-Many: Any number of students can be enrolled in a course. Similarly, a student can be enrolled in multiple courses. Likewise, a course can be offered at multiple locations and a location can host multiple courses.

Entities

Now let us define our entities. You can see that these are POCO based. They would definitely be backed by database objects when the application is running. But for a simple example, like this, we don't need to worry about that. And this is why Code First is great.

Student

namespace EFCodeFirstDatabaseCreation.Entities
{
    using System.Collections.Generic;

    class Student
    {
        public int StudentId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public int GradePointAverage { get; set; }
        public bool IsOutStanding { get; set; }

        public virtual ICollection<Course> StudentCourses { get; set; }
        public virtual Vehicle VehicleDetails { get; set; }
        public virtual Department StudentDepartment { get; set; }
    }
}

Vehicle

namespace EFCodeFirstDatabaseCreation.Entities
{
    class Vehicle
    {
        public int VehicleId { get; set; }
        public string Year { get; set; }
        public string Make { get; set; }
        public string Model { get; set; }
    }
}
Course
namespace EFCodeFirstDatabaseCreation.Entities
{
    using System.Collections.Generic;

    class Course
    {
        public int CourseId { get; set; }
        public string CourseName { get; set; }
        public ICollection<Student> CourseStudents { get; set; }
        public virtual ICollection<CourseLocation> CourseOfferedAt { get; set; }
    }
}
Department
namespace EFCodeFirstDatabaseCreation.Entities
{
    using System.Collections.Generic;

    class Department
    {
        public int DepartmentId { get; set; }
        public string DepartmentName { get; set; }
        public virtual ICollection<Student> Students { get; set; }
    }
}
CourseLocation
namespace EFCodeFirstDatabaseCreation.Entities
{
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;

    class CourseLocation
    {
        public int CourseLocationId { get; set; }
        public string LocationName { get; set; }
        public virtual LocationAddress Address { get; set; }
        public virtual ICollection<Course> CoursesOffered { get; set; }
    }

    [ComplexType]
    class LocationAddress
    {
        public string StreetAddress { get; set; }
        public string Apartment { get; set; }
        public string City { get; set; }
        public string StateProvince { get; set; }
        public string ZipCode { get; set; }
    }
}

Defining InstituteEntities [DbContext]

Now we need to define the DbContext to query these entities from the database. As you can see we don't need to add all the entities details in the DbContext. We only need to add those entities which are to be accessed directly using the DbContext instance. The other entities which have a relationship with these entities would still have a corresponding table in the database. It is just that they would be accessed to their corresponding relationship tables starting from the DbContext type.

namespace EFCodeFirstDatabaseCreation.Entities
{
    using System.Data.Entity;

    class InstituteEntities : DbContext
    {
        public DbSet<Student> Students { get; set; }
        public DbSet<Course> Courses { get; set; }
        public DbSet<Department> Departments { get; set; }       
    }
}

Initializing Entities with Data

Let us initialize these entities with some data. Remember that this would result in actual data inserted in the tables mapped to these entities by Entity Framework.

namespace EFCodeFirstDatabaseCreation
{
    using EFCodeFirstDatabaseCreation.Entities;
    using System.Data.Entity;
    using System.Collections.Generic;

    class InstituteDatabaseInitializer : DropCreateDatabaseAlways<InstituteEntities>
    {        
        protected override void Seed(InstituteEntities context)
        {

            Student student1 = new Student()
            {
                FirstName = "Muhammad",
                LastName = "Siddiqi",
                IsOutStanding = true,
                GradePointAverage = 3
            };

            Vehicle vehicle = new Vehicle() { VehicleId = 1, Make = "Bugatti", Model = "", Year = "2012" };
            
            Department department1 = 
                new Department() { DepartmentId = 1, DepartmentName = "Computer Systems Engineering", Students = new List<Student>() };
            Department department2 = 
                new Department() { DepartmentId = 2, DepartmentName = "Electrical Engineering", Students = new List<Student>() };

            Student student2 = new Student()
            {
                FirstName = "Chattan",
                LastName = "Shah",
                IsOutStanding = true,
                GradePointAverage = 4, 
                VehicleDetails = vehicle
            };

            Student student3 = new Student()
            {
                FirstName = "Imran",
                LastName = "Ashraf",
                IsOutStanding = true,
                GradePointAverage = 4
            };

            Student student4 = new Student()
            {
                FirstName = "Jawad",
                LastName = "Qureshi",
                IsOutStanding = true,
                GradePointAverage = 4
            };

            CourseLocation courseLocation1 =
                new CourseLocation()
                    {
                        LocationName = "Karachi Campus",
                        Address = new LocationAddress() 
                                        {
                                            StreetAddress = "XYZ I.I. Chundrigar Road",
                                            City = "Karachi",
                                            StateProvince = "Sindh",
                                            ZipCode = "YYYYY"
                                        }
                    };

            CourseLocation courseLocation2 =
                new CourseLocation()
                {
                    LocationName = "Manhattan Campus",
                    Address = new LocationAddress()
                    {
                        StreetAddress = "6th Street",
                        City = "New York City",
                        StateProvince = "New York",
                        ZipCode = "ZZZZZ"
                    }
                };

            Course course1 = new Course()
                                    {
                                        CourseName = "Engineering Mechanics",
                                        CourseStudents = new List<Student> { student1, student2 },
                                        CourseOfferedAt = new List<CourseLocation> {courseLocation1, courseLocation2 }
                                    };


            Course course2 = new Course()
                                    {
                                        CourseName = "Fault Tolerence & Reliable System Design",
                                        CourseStudents = new List<Student> { student3, student4 },
                                        CourseOfferedAt = new List<CourseLocation>{courseLocation1}
                                    };

            Student student5 = new Student()
                                    {
                                        FirstName = "Ali",
                                        LastName = "Khan",
                                        GradePointAverage = 3,
                                        IsOutStanding = true
                                    };

            course1.CourseStudents.Add(student5);
            
            department1.Students.Add(student1);
            department1.Students.Add(student2);
            department1.Students.Add(student3);

            department2.Students.Add(student4);
            department2.Students.Add(student5);

            context.Courses.Add(course1);
            context.Courses.Add(course2);
            
            context.Departments.Add(department1);
            context.Departments.Add(department2);

            base.Seed(context);
        }
    }
}

Test Code:

Now let us write some test code to see if the entities are generated as expected. The code below would also test if the data has been inserted in those entities. Here we are setting database initializer for InstituteEntities. Then we are just writing the data to the console testing if the entities are initialized properly.

namespace EFCodeFirstDatabaseCreation
{
    using System.Linq;
    using EFCodeFirstDatabaseCreation.Entities;
    using System.Data.Entity;
    using System;

    class Program
    {
        static void Main(string[] args)
        {
            //Initialize Database entities
            Database.SetInitializer<InstituteEntities>(new InstituteDatabaseInitializer());

            InstituteEntities instituteEntites = new InstituteEntities();
           
            foreach (var course in instituteEntites.Courses
                                        .Include("CourseStudents")
                                        .Include("CourseStudents.VehicleDetails")
                                        .Include("CourseStudents.StudentDepartment")
                                        .Include("CourseOfferedAt"))
            {
                string location = course.CourseOfferedAt == null ? string.Empty :
                    string.Join(", ", course.CourseOfferedAt.Select(l => l.LocationName).ToArray<string>());
                
                Console.WriteLine("***************************");
                Console.WriteLine("Course : {0}, Offered At: {1}", course.CourseName, location);

                foreach (var student in course.CourseStudents)
                {
                    Console.WriteLine(
                                string.Format("Student : {0} {1}, GPA : {2}",
                                               student.FirstName,
                                               student.LastName,
                                               student.GradePointAverage));

                    Console.WriteLine("   Department : {0}", student.StudentDepartment.DepartmentName);

                    if (student.VehicleDetails != null)
                    {
                        Console.WriteLine("   Vehicle: {0} {1} {2}",
                            student.VehicleDetails.Year, student.VehicleDetails.Make, student.VehicleDetails.Model);
                    }
                }
                
                Console.WriteLine("***************************");
            }

            Console.ReadLine();
        }
    }
}

Database Relationship:

You might be wondering how these relationships are created in the database.

One-to-One & One-to-Many:

These types of relationship result in adding the foreign key on one of these entities. In our case, the relationship between Vehicle and Student is one-to-one. A student is supposed to register only one vehicle in the system. On the other hand, the relationship between Department and Student is One-to-Many as a department could have more than student.

Many-to-Many

This is similar to generating Database from and Entity Relationship Diagram. This results in adding a relationship table in the Database. In our case the relationship between Student & Courses is many-to-many. This would result in generating a new table in database containing primary keys of both the entities. For our case, the name of this table is CourseStudents. Similarly, a course can be offered at multiple locations i.e. the relationship between Course and CourseLocation is also many-to-many. This results in creating CourseLocationCourses table in Database.

Complex Type

A complex type is represented in the database by adding all properties of complex type to the entity itself. By default, the names of these properties are preceded by the name of variant used for ComplexType instance in the entity.

Output:

When we run the project, it runs in the following output.

Download Code:

No comments: