Entity Framework Code First - Relationship between Entities II

This is part of the series of discussions about entity framework. In the last post, we touched the basics about relationship between entities. We discussed how the relational database concepts of different relationship types can be mapped to object oriented entities. We are progressively working on an Institute scenario discussing the various entity framework features.

In this post we will be adding on to the previous post by discussing the relationship between entities further. We will discuss how we can define one-to-many relationships. In the previous post we defined a relationship between Departments and Students entities. This relationship was defined such that a student might not belong to a department. If we look at the database table created, we see that there is a foreign key added in the Students table. It is allowing null values so a student can exist without belonging to any department.

In order to change the relationship to one-to-many, we need to specify it when the model is being built. We can do it by overriding OnModelCreating method of InstituteEntities (DbContext). Here we are specifying that it is mandatory for a student to belong to a department. Entity framework would not allow a student to be saved to the database without belonging to a department.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<Student>().HasRequired(s => s.StudentDepartment);
}

This would result in generating the Students table as follows:

So the similar Id has been generated to hold foreign key for department but it is now specified as not null which changes the relationship to one-to-many from previous zero-to-many relationship. Basically EF Code First uses conventions for keys, either primary or foreign keys. Just adding a non-nullable primitive key for holding Department table's foreign key would update the table definition to have a non-nullable foreign key. If we keep the name as DepartmentId then based on convention based approach, the run-time would realize that this is to hold foreign key of Department and would make it non-nullable. Let us update Student's entity as follows:

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; }
        
        //Foreign key of Department
        public int DepartmentId { get; set; }

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

Just updating the above code would result in the updated Student's table in database as follows:

Please note that the name of foreign key is updated to be DepartmentId. It has also been updated as non-null. This would stop us from adding student without specifying department information. If we had needed the foreign key to be picked up based on convention but we still needed to keep the relationship as zero-to-many then we could just use a nullable type and framework would take care of that. Let's update DepartmentId as nullable int [int?] in Student table as follows:

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; }
        
        //Foreign key of Department
        public int? DepartmentId { get; set; }

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

This would result in the following Student's table in the database.

If we had used a name for Department's Id which is not based on convention then the framework obviously would not recognize the significance and would treat that field as just entity's property and would just create a column for the field in the database table. Let us update DepartmentId as DepartmentInfoId.

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; }
        
        //Foreign key of Department
        public int DepartmentInfoId { get; set; }

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

Now run the application. You can notice that the Student's table is created as follows:

Since the new key is not based on EF convention, the framework has not recognized it. In order to help the framework, we can specify this in OnModelCreating method of the DbContext as follows:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<Student>().HasRequired(s => s.StudentDepartment)
                                  .WithMany(d => d.Students)
                                  .HasForeignKey(s => s.DepartmentInfoId);
}

Now the framework would realize that the DepartmentInfoId should be used to define foreign key relationship between Student and Department.

If we had needed a zero-to-many relationship, we would need to update DepartmentInfoId to allow null values.

//Foreign key of Department
public int? DepartmentInfoId { get; set; }

We also need to update OnModelCreating method in InstituteEntities as follows:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<Student>().HasOptional(s => s.StudentDepartment)
                                  .WithMany(d => d.Students)
                                  .HasForeignKey(s => s.DepartmentInfoId);
}

The framework would still use DepartmentInfoId as Foreign key for Department table in Student table. It would just allow null for this allowing zero-to-many relationship.

Please remember that this would only be possible if we define DepartmentInfoId as nullable [int?]. If we just change it as follows then model validation would fail. Let us do that and change Student POCO as follows:

//Foreign key of Department
public int DepartmentInfoId { get; set; }

Now we run the application and see it crashing.

Download Code