Language

Implementing Inheritance with the Entity Framework in an ASP.NET MVC Application (8 of 10)

By Tom Dykstra|

Download Completed Project

The Contoso University sample web application demonstrates how to create ASP.NET MVC 4 applications using the Entity Framework 5 Code First and Visual Studio 2012. For information about the tutorial series, see the first tutorial in the series. You can start the tutorial series from the beginning or download a starter project for this chapter and start here.

If you run into a problem you can’t resolve, download the completed chapter and try to reproduce your problem. You can generally find the solution to the problem by comparing your code to the completed code. For some common errors and how to solve them, see Errors and Workarounds.

In the previous tutorial you handled concurrency exceptions. This tutorial will show you how to implement inheritance in the data model.

In object-oriented programming, you can use inheritance to eliminate redundant code. In this tutorial, you'll change the Instructor and Student classes so that they derive from a Person base class which contains properties such as LastName that are common to both instructors and students. You won't add or change any web pages, but you'll change some of the code and those changes will be automatically reflected in the database.

Table-per-Hierarchy versus Table-per-Type Inheritance

In object-oriented programming, you can use inheritance to make it easier to work with related classes. For example, the Instructor and Student classes in the School data model share several properties, which results in redundant code:

Student_and_Instructor_classes

Suppose you want to eliminate the redundant code for the properties that are shared by the Instructor and Student entities. You could create a Person base class which contains only those shared properties, then make the Instructor and Student entities inherit from that base class, as shown in the following illustration:

Student_and_Instructor_classes_deriving_from_Person_class

There are several ways this inheritance structure could be represented in the database. You could have a Person table that includes information about both students and instructors in a single table. Some of the columns could apply only to instructors (HireDate), some only to students (EnrollmentDate), some to both (LastName, FirstName). Typically, you'd have a discriminator column to indicate which type each row represents. For example, the discriminator column might have "Instructor" for instructors and "Student" for students.

Table-per-hierarchy_example

This pattern of generating an entity inheritance structure from a single database table is called table-per-hierarchy (TPH) inheritance.

An alternative is to make the database look more like the inheritance structure. For example, you could have only the name fields in the Person table and have separate Instructor and Student tables with the date fields.

Table-per-type_inheritance

This pattern of making a database table for each entity class is called table per type (TPT) inheritance.

TPH inheritance patterns generally deliver better performance in the Entity Framework than TPT inheritance patterns, because TPT patterns can result in complex join queries. This tutorial demonstrates how to implement TPH inheritance. You'll do that by performing the following steps:

  • Create a Person class and change the Instructor and Student classes to derive from Person.
  • Add model-to-database mapping code to the database context class.
  • Change InstructorID and StudentID references throughout the project to PersonID.

Creating the Person Class

Note: You won't be able to compile the project after creating the classes below until you update the controllers that uses these classes.

In the Models folder, create Person.cs and replace the template code with the following code:

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace ContosoUniversity.Models
{
   public abstract class Person
   {
      [Key]
      public int PersonID { get; set; }

      [RegularExpression(@"^[A-Z]+[a-zA-Z''-'\s]*$")]
      [StringLength(50, MinimumLength = 1)]
      [Display(Name = "Last Name")]
      public string LastName { get; set; }

      [Column("FirstName")]
      [Display(Name = "First Name")]
      [StringLength(50, MinimumLength = 2, ErrorMessage = "First name must be between 2 and 50 characters.")]
      public string FirstMidName { get; set; }

      public string FullName
      {
         get
         {
            return LastName + ", " + FirstMidName;
         }
      }
   }
}

In Instructor.cs, derive the Instructor class from the Person class and remove the key and name fields. The code will look like the following example:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;

namespace ContosoUniversity.Models
{
    public class Instructor : Person
    {
        [DataType(DataType.Date)]
        [Display(Name = "Hire Date")]
        public DateTime HireDate { get; set; }

        public virtual ICollection<Course> Courses { get; set; }
        public virtual OfficeAssignment OfficeAssignment { get; set; }
    }
}

Make similar changes to Student.cs. The Student class will look like the following example:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;

namespace ContosoUniversity.Models
{
    public class Student : Person
    {
        [DataType(DataType.Date)]
        [Display(Name = "Enrollment Date")]
        public DateTime EnrollmentDate { get; set; }

        public virtual ICollection<Enrollment> Enrollments { get; set; }
    }
}

Adding the Person Entity Type to the Model

In SchoolContext.cs, add a DbSet property for the Person entity type:

        public DbSet<Person> People { get; set; }

This is all that the Entity Framework needs in order to configure table-per-hierarchy inheritance. As you'll see, when the database is re-created, it will have a Person table in place of the Student and Instructor tables.

Changing InstructorID and StudentID to PersonID

In SchoolContext.cs, in the Instructor-Course mapping statement, change MapRightKey("InstructorID") to MapRightKey("PersonID"):

modelBuilder.Entity<Course>()
    .HasMany(c => c.Instructors).WithMany(i => i.Courses)
    .Map(t => t.MapLeftKey("CourseID")
    .MapRightKey("PersonID")
    .ToTable("CourseInstructor"));

This change isn't required; it just changes the name of the InstructorID column in the many-to-many join table. If you left the name as InstructorID, the application would still work correctly. Here is the completed SchoolContext.cs:

using ContosoUniversity.Models;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;

namespace ContosoUniversity.DAL
{
   public class SchoolContext : DbContext
   {
      public DbSet<Course> Courses { get; set; }
      public DbSet<Department> Departments { get; set; }
      public DbSet<Enrollment> Enrollments { get; set; }
      public DbSet<Instructor> Instructors { get; set; }
      public DbSet<Student> Students { get; set; }
      public DbSet<OfficeAssignment> OfficeAssignments { get; set; }
      public DbSet<Person> People { get; set; }

      protected override void OnModelCreating(DbModelBuilder modelBuilder)
      {
         modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();

         modelBuilder.Entity<Course>()
             .HasMany(c => c.Instructors).WithMany(i => i.Courses)
             .Map(t => t.MapLeftKey("CourseID")
                 .MapRightKey("PersonID")
                 .ToTable("CourseInstructor"));
      }
   }
}

Next you need to change InstructorID to PersonID and StudentID to PersonID throughout the project except in the time-stamped migrations files in the  Migrations folder. To do that you'll find and open only the files that need to be changed, then perform a global change on the opened files.  The only file in the Migrations folder you should change is Migrations\Configuration.cs.

  1. Important:  Begin by closing all the open files in Visual Studio.

  2. Click Find and Replace -- Find all Files in the Edit menu, and then search for all files in the project that contain InstructorID.


  3. Open each file in the Find Results window except the <time-stamp>_.cs  migration files in the  Migrations folder, by double-clicking one line for each file.

  4. Open the Replace in Files dialog and change Look in to All Open Documents.

  5. Use the Replace in Files dialog to change all InstructorID to PersonID.

  6. Find all the files in the project that contain StudentID.
  7. Open each file in the Find Results window except the <time-stamp>_*.cs  migration files in the  Migrations folder, by double-clicking one line for each file.



  8. Open the Replace in Files dialog and change Look in to All Open Documents.

  9. Use the Replace in Files dialog to change all StudentID to PersonID.


  10. Build the project.

(Note that this demonstrates a disadvantage of the classnameID pattern for naming primary keys. If you had named primary keys ID without prefixing the class name, no renaming would be necessary now.)

Create and Update a Migrations File

In the Package Manager Console (PMC),  enter the following command:

Add-Migration Inheritance

Run the Update-Database command in the PMC. The command will fail at this point because we have existing data that migrations doesn't know how to handle. You get the following error:

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_dbo.Department_dbo.Person_PersonID". The conflict occurred in database "ContosoUniversity", table "dbo.Person", column 'PersonID'.

Open Migrations\<timestamp>_Inheritance.cs and replace the Up method with the following code:

public override void Up()
{
    DropForeignKey("dbo.Department", "InstructorID", "dbo.Instructor");
    DropForeignKey("dbo.OfficeAssignment", "InstructorID", "dbo.Instructor");
    DropForeignKey("dbo.Enrollment", "StudentID", "dbo.Student");
    DropForeignKey("dbo.CourseInstructor", "InstructorID", "dbo.Instructor");
    DropIndex("dbo.Department", new[] { "InstructorID" });
    DropIndex("dbo.OfficeAssignment", new[] { "InstructorID" });
    DropIndex("dbo.Enrollment", new[] { "StudentID" });
    DropIndex("dbo.CourseInstructor", new[] { "InstructorID" });
    RenameColumn(table: "dbo.Department", name: "InstructorID", newName: "PersonID");
    RenameColumn(table: "dbo.OfficeAssignment", name: "InstructorID", newName: "PersonID");
    RenameColumn(table: "dbo.Enrollment", name: "StudentID", newName: "PersonID");
    RenameColumn(table: "dbo.CourseInstructor", name: "InstructorID", newName: "PersonID");
    CreateTable(
        "dbo.Person",
        c => new
            {
                PersonID = c.Int(nullable: false, identity: true),
                LastName = c.String(maxLength: 50),
                FirstName = c.String(maxLength: 50),
                HireDate = c.DateTime(),
                EnrollmentDate = c.DateTime(),
                Discriminator = c.String(nullable: false, maxLength: 128),
                OldId = c.Int(nullable: false)
            })
        .PrimaryKey(t => t.PersonID);

    // Copy existing Student and Instructor data into new Person table.
    Sql("INSERT INTO dbo.Person (LastName, FirstName, HireDate, EnrollmentDate, Discriminator, OldId) SELECT LastName, FirstName, null AS HireDate, EnrollmentDate, 'Student' AS Discriminator, StudentId AS OldId FROM dbo.Student");
    Sql("INSERT INTO dbo.Person (LastName, FirstName, HireDate, EnrollmentDate, Discriminator, OldId) SELECT LastName, FirstName, HireDate, null AS EnrollmentDate, 'Instructor' AS Discriminator, InstructorId AS OldId FROM dbo.Instructor");

    // Fix up existing relationships to match new PK's.
    Sql("UPDATE dbo.Enrollment SET PersonId = (SELECT PersonId FROM dbo.Person WHERE OldId = Enrollment.PersonId AND Discriminator = 'Student')");
    Sql("UPDATE dbo.Department SET PersonId = (SELECT PersonId FROM dbo.Person WHERE OldId = Department.PersonId AND Discriminator = 'Instructor')");
    Sql("UPDATE dbo.OfficeAssignment SET PersonId = (SELECT PersonId FROM dbo.Person WHERE OldId = OfficeAssignment.PersonId AND Discriminator = 'Instructor')");
    Sql("UPDATE dbo.CourseInstructor SET PersonId = (SELECT PersonId FROM dbo.Person WHERE OldId = CourseInstructor.PersonId AND Discriminator = 'Instructor')");

    // Remove temporary key
    DropColumn("dbo.Person", "OldId");

    AddForeignKey("dbo.Department", "PersonID", "dbo.Person", "PersonID");
    AddForeignKey("dbo.OfficeAssignment", "PersonID", "dbo.Person", "PersonID");
    AddForeignKey("dbo.Enrollment", "PersonID", "dbo.Person", "PersonID", cascadeDelete: true);
    AddForeignKey("dbo.CourseInstructor", "PersonID", "dbo.Person", "PersonID", cascadeDelete: true);
    CreateIndex("dbo.Department", "PersonID");
    CreateIndex("dbo.OfficeAssignment", "PersonID");
    CreateIndex("dbo.Enrollment", "PersonID");
    CreateIndex("dbo.CourseInstructor", "PersonID");
    DropTable("dbo.Instructor");
    DropTable("dbo.Student");
}

Run the update-database command again.

Note: It's possible to get other errors when migrating data and making schema changes. If you get migration errors you can't resolve, you can continue with the tutorial by changing the connection string in the Web.config file or deleting the database. The simplest approach is to rename the database in the Web.config file. For example, change the database name to CU_test as shown in the following example:

<add name="SchoolContext" connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=CU_Test;
      Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\CU_Test.mdf" 
      providerName="System.Data.SqlClient" />

With a new database, there is no data to migrate, and the update-database command is much more likely to complete without errors. For instructions on how to delete the database, see How to Drop a Database from Visual Studio 2012. If you take this approach in order to continue with the tutorial, skip the deployment step at the end of this tutorial, since the deployed site would get the same error when it runs migrations automatically. If you want to troubleshoot a migrations error, the best resource is one of the Entity Framework forums or StackOverflow.com.

Testing

Run the site and try various pages. Everything works the same as it did before.

In Server Explorer, expand SchoolContext and then Tables, and you see that the Student and Instructor tables have been replaced by a Person table. Expand the Person table and you see that it has all of the columns that used to be in the Student and Instructor tables.

Server_Explorer_showing_Person_table

Right-click the Person table, and then click Show Table Data to see the discriminator column.

The following diagram illustrates the structure of the new School database:

School_database_diagram

Deploy to Windows Azure

This section requires you to have completed the optional Deploying the app to Windows Azure section in Part 3, Sorting, Filtering, and Paging of this tutorial series. If you had migrations errors that you resolved by deleting the database in your local project, skip this step.

  1. In Visual Studio, right-click the project in Solution Explorer and select Publish from the context menu.

    Publish in project context menu
  2. Click Publish.

    publish

    The Web app will open in your default browser.

  3. Test the application to verify it's working.

    The first time you run a page that accesses the database, the Entity Framework runs all of the migrations Up methods required to bring the database up to date with the current data model.

    Note: The web app doesn't implement security, so anyone who finds the URL can change the data. For instructions on how to secure the web site, see Deploy a Secure ASP.NET MVC app with Membership, OAuth, and SQL Database to a Windows Azure Web Site. You can prevent other people from using the site by using the Windows Azure Management Portal or Server Explorer in Visual Studio to stop the site.

Summary

Table-per-hierarchy inheritance has now been implemented for the Person, Student, and Instructor classes. For more information about this and other inheritance structures, see Inheritance Mapping Strategies on Morteza Manavi's blog. In the next tutorial you'll see some ways to implement the repository and unit of work patterns.

Links to other Entity Framework resources can be found in the ASP.NET Data Access Content Map.

Author Information

Tom Dykstra

Tom Dykstra – Tom Dykstra is a Senior Programming Writer on Microsoft's Web Platform & Tools Content Team...