Implementing Inheritance with the Entity Framework in an ASP.NET MVC Application (8 of 10)
by Tom Dykstra
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.
[!NOTE]
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:
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:
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.
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.
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
Personclass and change theInstructorandStudentclasses to derive fromPerson. - Add model-to-database mapping code to the database context class.
- Change
InstructorIDandStudentIDreferences throughout the project toPersonID.
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:
[!code-csharpMain]
1: using System.ComponentModel.DataAnnotations;
2: using System.ComponentModel.DataAnnotations.Schema;
3:
4: namespace ContosoUniversity.Models
5: {
6: public abstract class Person
7: {
8: [Key]
9: public int PersonID { get; set; }
10:
11: [RegularExpression(@"^[A-Z]+[a-zA-Z''-'\s]*$")]
12: [StringLength(50, MinimumLength = 1)]
13: [Display(Name = "Last Name")]
14: public string LastName { get; set; }
15:
16: [Column("FirstName")]
17: [Display(Name = "First Name")]
18: [StringLength(50, MinimumLength = 2, ErrorMessage = "First name must be between 2 and 50 characters.")]
19: public string FirstMidName { get; set; }
20:
21: public string FullName
22: {
23: get
24: {
25: return LastName + ", " + FirstMidName;
26: }
27: }
28: }
29: }
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:
[!code-csharpMain]
1: using System;
2: using System.Collections.Generic;
3: using System.ComponentModel.DataAnnotations;
4:
5: namespace ContosoUniversity.Models
6: {
7: public class Instructor : Person
8: {
9: [DataType(DataType.Date)]
10: [Display(Name = "Hire Date")]
11: public DateTime HireDate { get; set; }
12:
13: public virtual ICollection<Course> Courses { get; set; }
14: public virtual OfficeAssignment OfficeAssignment { get; set; }
15: }
16: }
Make similar changes to Student.cs. The Student class will look like the following example:
[!code-csharpMain]
1: using System;
2: using System.Collections.Generic;
3: using System.ComponentModel.DataAnnotations;
4:
5: namespace ContosoUniversity.Models
6: {
7: public class Student : Person
8: {
9: [DataType(DataType.Date)]
10: [Display(Name = "Enrollment Date")]
11: public DateTime EnrollmentDate { get; set; }
12:
13: public virtual ICollection<Enrollment> Enrollments { get; set; }
14: }
15: }
Adding the Person Entity Type to the Model
In SchoolContext.cs, add a DbSet property for the Person entity type:
[!code-csharpMain]
1: 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"):
[!code-csharpMain]
1: modelBuilder.Entity<Course>()
2: .HasMany(c => c.Instructors).WithMany(i => i.Courses)
3: .Map(t => t.MapLeftKey("CourseID")
4: .MapRightKey("PersonID")
5: .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:
[!code-csharpMain]
1: using ContosoUniversity.Models;
2: using System.Data.Entity;
3: using System.Data.Entity.ModelConfiguration.Conventions;
4:
5: namespace ContosoUniversity.DAL
6: {
7: public class SchoolContext : DbContext
8: {
9: public DbSet<Course> Courses { get; set; }
10: public DbSet<Department> Departments { get; set; }
11: public DbSet<Enrollment> Enrollments { get; set; }
12: public DbSet<Instructor> Instructors { get; set; }
13: public DbSet<Student> Students { get; set; }
14: public DbSet<OfficeAssignment> OfficeAssignments { get; set; }
15: public DbSet<Person> People { get; set; }
16:
17: protected override void OnModelCreating(DbModelBuilder modelBuilder)
18: {
19: modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
20:
21: modelBuilder.Entity<Course>()
22: .HasMany(c => c.Instructors).WithMany(i => i.Courses)
23: .Map(t => t.MapLeftKey("CourseID")
24: .MapRightKey("PersonID")
25: .ToTable("CourseInstructor"));
26: }
27: }
28: }
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.cs.
[!IMPORTANT] Begin by closing all the open files in Visual Studio.
Click Find and Replace – Find all Files in the Edit menu, and then search for all files in the project that contain
InstructorID.
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.
- Open the Replace in Files dialog and change Look in to All Open Documents.
Use the Replace in Files dialog to change all
InstructorIDtoPersonID.
- Find all the files in the project that contain
StudentID. 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.
- Open the Replace in Files dialog and change Look in to All Open Documents.
Use the Replace in Files dialog to change all
StudentIDtoPersonID.
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:
[!code-csharpMain]
1: public override void Up()
2: {
3: DropForeignKey("dbo.Department", "InstructorID", "dbo.Instructor");
4: DropForeignKey("dbo.OfficeAssignment", "InstructorID", "dbo.Instructor");
5: DropForeignKey("dbo.Enrollment", "StudentID", "dbo.Student");
6: DropForeignKey("dbo.CourseInstructor", "InstructorID", "dbo.Instructor");
7: DropIndex("dbo.Department", new[] { "InstructorID" });
8: DropIndex("dbo.OfficeAssignment", new[] { "InstructorID" });
9: DropIndex("dbo.Enrollment", new[] { "StudentID" });
10: DropIndex("dbo.CourseInstructor", new[] { "InstructorID" });
11: RenameColumn(table: "dbo.Department", name: "InstructorID", newName: "PersonID");
12: RenameColumn(table: "dbo.OfficeAssignment", name: "InstructorID", newName: "PersonID");
13: RenameColumn(table: "dbo.Enrollment", name: "StudentID", newName: "PersonID");
14: RenameColumn(table: "dbo.CourseInstructor", name: "InstructorID", newName: "PersonID");
15: CreateTable(
16: "dbo.Person",
17: c => new
18: {
19: PersonID = c.Int(nullable: false, identity: true),
20: LastName = c.String(maxLength: 50),
21: FirstName = c.String(maxLength: 50),
22: HireDate = c.DateTime(),
23: EnrollmentDate = c.DateTime(),
24: Discriminator = c.String(nullable: false, maxLength: 128),
25: OldId = c.Int(nullable: false)
26: })
27: .PrimaryKey(t => t.PersonID);
28:
29: // Copy existing Student and Instructor data into new Person table.
30: 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");
31: 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");
32:
33: // Fix up existing relationships to match new PK's.
34: Sql("UPDATE dbo.Enrollment SET PersonId = (SELECT PersonId FROM dbo.Person WHERE OldId = Enrollment.PersonId AND Discriminator = 'Student')");
35: Sql("UPDATE dbo.Department SET PersonId = (SELECT PersonId FROM dbo.Person WHERE OldId = Department.PersonId AND Discriminator = 'Instructor')");
36: Sql("UPDATE dbo.OfficeAssignment SET PersonId = (SELECT PersonId FROM dbo.Person WHERE OldId = OfficeAssignment.PersonId AND Discriminator = 'Instructor')");
37: Sql("UPDATE dbo.CourseInstructor SET PersonId = (SELECT PersonId FROM dbo.Person WHERE OldId = CourseInstructor.PersonId AND Discriminator = 'Instructor')");
38:
39: // Remove temporary key
40: DropColumn("dbo.Person", "OldId");
41:
42: AddForeignKey("dbo.Department", "PersonID", "dbo.Person", "PersonID");
43: AddForeignKey("dbo.OfficeAssignment", "PersonID", "dbo.Person", "PersonID");
44: AddForeignKey("dbo.Enrollment", "PersonID", "dbo.Person", "PersonID", cascadeDelete: true);
45: AddForeignKey("dbo.CourseInstructor", "PersonID", "dbo.Person", "PersonID", cascadeDelete: true);
46: CreateIndex("dbo.Department", "PersonID");
47: CreateIndex("dbo.OfficeAssignment", "PersonID");
48: CreateIndex("dbo.Enrollment", "PersonID");
49: CreateIndex("dbo.CourseInstructor", "PersonID");
50: DropTable("dbo.Instructor");
51: DropTable("dbo.Student");
52: }
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:
[!code-xmlMain]
1: <add name="SchoolContext" connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=CU_Test;2: Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\CU_Test.mdf"3: providerName="System.Data.SqlClient" />With a new database, there is no data to migrate, and the
update-databasecommand 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.
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:
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.
)
|
|