1 11_Implementing Inheritance with the Entity Framework 6 in an ASP.NET MVC 5 Application Mon Jul 06, 2015 8:15 am
Admin
Admin
In object-oriented programming, you can use inheritance to facilitate code reuse. In this tutorial, you'll change the
- Code:
Instructor
- Code:
Student
- Code:
Person
- Code:
LastName
Options for mapping inheritance to database tables
The
- Code:
Instructor
- Code:
Student
- Code:
School
Suppose you want to eliminate the redundant code for the properties that are shared by the
- Code:
Instructor
- Code:
Student
- Code:
Person
- Code:
Instructor
- Code:
Student
There are several ways this inheritance structure could be represented in the database. You could have a
- Code:
Person
- Code:
HireDate
- Code:
EnrollmentDate
- Code:
LastName
- Code:
FirstName
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
- Code:
Person
- Code:
Instructor
- Code:
Student
This pattern of making a database table for each entity class is called table per type (TPT) inheritance.
Yet another option is to map all non-abstract types to individual tables. All properties of a class, including inherited properties, map to columns of the corresponding table. This pattern is called Table-per-Concrete Class (TPC) inheritance. If you implemented TPC inheritance for the
- Code:
Person
- Code:
Student
- Code:
Instructor
- Code:
Student
- Code:
Instructor
TPC and 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. TPH is the default inheritance pattern in the Entity Framework, so all you have to do is create a
- Code:
Person
- Code:
Instructor
- Code:
Student
- Code:
Person
- Code:
DbContext
Create the Person class
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
{
public int ID { get; set; }
[Required]
[StringLength(50)]
[Display(Name = "Last Name")]
public string LastName { get; set; }
[Required]
[StringLength(50, ErrorMessage = "First name cannot be longer than 50 characters.")]
[Column("FirstName")]
[Display(Name = "First Name")]
public string FirstMidName { get; set; }
[Display(Name = "Full Name")]
public string FullName
{
get
{
return LastName + ", " + FirstMidName;
}
}
}
}
Make Student and Instructor classes inherit from Person
In Instructor.cs, derive the
- Code:
Instructor
- Code:
Person
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace ContosoUniversity.Models
{
public class Instructor : Person
{
[DataType(DataType.Date)]
[DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
[Display(Name = "Hire Date")]
public DateTime HireDate { get; set; }
public virtual ICollection
public virtual OfficeAssignment OfficeAssignment { get; set; }
}
}
Make similar changes to Student.cs. The
- Code:
Student
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace ContosoUniversity.Models
{
public class Student : Person
{
[DataType(DataType.Date)]
[DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
[Display(Name = "Enrollment Date")]
public DateTime EnrollmentDate { get; set; }
public virtual ICollection
}
}
Add the Person Entity Type to the Model
In SchoolContext.cs, add a
- Code:
DbSet
- Code:
Person
public DbSet
This is all that the Entity Framework needs in order to configure table-per-hierarchy inheritance. As you'll see, when the database is updated, it will have a
- Code:
Person
- Code:
Student
- Code:
Instructor
Create and Update a Migrations File
In the Package Manager Console (PMC), enter the following command:
- Code:
Add-Migration Inheritance
Run the
- Code:
Update-Database
Could not drop object 'dbo.Instructor' because it is referenced by a FOREIGN KEY constraint.
Open Migrations\
- Code:
Up
public override void Up()
{
// Drop foreign keys and indexes that point to tables we're going to drop.
DropForeignKey("dbo.Enrollment", "StudentID", "dbo.Student");
DropIndex("dbo.Enrollment", new[] { "StudentID" });
RenameTable(name: "dbo.Instructor", newName: "Person");
AddColumn("dbo.Person", "EnrollmentDate", c => c.DateTime());
AddColumn("dbo.Person", "Discriminator", c => c.String(nullable: false, maxLength: 128, defaultValue: "Instructor"));
AlterColumn("dbo.Person", "HireDate", c => c.DateTime());
AddColumn("dbo.Person", "OldId", c => c.Int(nullable: true));
// Copy existing Student 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, ID AS OldId FROM dbo.Student");
// Fix up existing relationships to match new PK's.
Sql("UPDATE dbo.Enrollment SET StudentId = (SELECT ID FROM dbo.Person WHERE OldId = Enrollment.StudentId AND Discriminator = 'Student')");
// Remove temporary key
DropColumn("dbo.Person", "OldId");
DropTable("dbo.Student");
// Re-create foreign keys and indexes pointing to new table.
AddForeignKey("dbo.Enrollment", "StudentID", "dbo.Person", "ID", cascadeDelete: true);
CreateIndex("dbo.Enrollment", "StudentID");
}
This code takes care of the following database update tasks:
- Removes foreign key constraints and indexes that point to the Student table.
- Renames the Instructor table as Person and makes changes needed for it to store Student data:
- Adds nullable EnrollmentDate for students.
- Adds Discriminator column to indicate whether a row is for a student or an instructor.
- Makes HireDate nullable since student rows won't have hire dates.
- Adds a temporary field that will be used to update foreign keys that point to students. When you copy students into the Person table they'll get new primary key values.
(If you had used GUID instead of integer as the primary key type, the student primary key values wouldn't have to change, and several of these steps could have been omitted.)
Run the
- Code:
update-database
(In a production system you would make corresponding changes to the Down method in case you ever had to use that to go back to the previous database version. For this tutorial you won't be using the Down method.)
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 by deleting the database. The simplest approach is to rename the database in the Web.config file. For example, change the database name to ContosoUniversity2 as shown in the following example:
providerName="System.Data.SqlClient" />
With a new database, there is no data to migrate, and the
- Code:
update-database
Testing
Run the site and try various pages. Everything works the same as it did before.
In Server Explorer, expand Data Connections\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:
Deploy to Azure
This section requires you to have completed the optional Deploying the app to 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; or create a new site and database, and deploy to the new environment.
[list defaultattr=]
[*]In Visual Studio, right-click the project in Solution Explorer and select Publish from the context menu.
[*]Click Publish.
The Web app will open in your default browser.
[*]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
- Code:
Up
[/list]
Summary
You've implemented table-per-hierarchy inheritance for the
- Code:
Person
- Code:
Student
- Code:
Instructor
Links to other Entity Framework resources can be found in the ASP.NET Data Access - Recommended Resources.