Inheritance - EF Core with ASP.NET Core MVC tutorial (9 of 10)
By Tom Dykstra and Rick Anderson
The Contoso University sample web application demonstrates how to create ASP.NET Core MVC web applications using Entity Framework Core and Visual Studio. For information about the tutorial series, see the first tutorial in the series.
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 facilitate code reuse. 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.
Options for mapping inheritance to database tables
The Instructor and Student classes in the School data model have several properties that are identical:
Suppose you want to eliminate the redundant code for the properties that are shared by the Instructor and Student entities. Or you want to write a service that can format names without caring whether the name came from an instructor or a student. You could create a Person base class that contains only those shared properties, then make the Instructor and Student classes 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.
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 Person, Student, and Instructor classes as shown earlier, the Student and Instructor tables would look no different after implementing inheritance than they did before.
TPC and TPH inheritance patterns generally deliver better performance than TPT inheritance patterns, because TPT patterns can result in complex join queries.
This tutorial demonstrates how to implement TPH inheritance. TPH is the only inheritance pattern that the Entity Framework Core supports. What you’ll do is create a Person class, change the Instructor and Student classes to derive from Person, add the new class to the DbContext, and create a migration.
[!TIP] Consider saving a copy of the project before making the following changes. Then if you run into problems and need to start over, it will be easier to start from the saved project instead of reversing steps done for this tutorial or going back to the beginning of the whole series.
Create the Person class
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: public int ID { get; set; }
9:
10: [Required]
11: [StringLength(50)]
12: [Display(Name = "Last Name")]
13: public string LastName { get; set; }
14: [Required]
15: [StringLength(50, ErrorMessage = "First name cannot be longer than 50 characters.")]
16: [Column("FirstName")]
17: [Display(Name = "First Name")]
18: public string FirstMidName { get; set; }
19:
20: [Display(Name = "Full Name")]
21: public string FullName
22: {
23: get
24: {
25: return LastName + ", " + FirstMidName;
26: }
27: }
28: }
29: }
Make Student and Instructor classes inherit from Person
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: #define AfterInheritance // or BeforeInheritance
2:
3: #if BeforeInheritance
4: #region snippet_BeforeInheritance
5: using System;
6: using System.Collections.Generic;
7: using System.ComponentModel.DataAnnotations;
8: using System.ComponentModel.DataAnnotations.Schema;
9:
10: namespace ContosoUniversity.Models
11: {
12: public class Instructor
13: {
14: public int ID { get; set; }
15:
16: [Required]
17: [Display(Name = "Last Name")]
18: [StringLength(50)]
19: public string LastName { get; set; }
20:
21: [Required]
22: [Column("FirstName")]
23: [Display(Name = "First Name")]
24: [StringLength(50)]
25: public string FirstMidName { get; set; }
26:
27: [DataType(DataType.Date)]
28: [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
29: [Display(Name = "Hire Date")]
30: public DateTime HireDate { get; set; }
31:
32: [Display(Name = "Full Name")]
33: public string FullName
34: {
35: get { return LastName + ", " + FirstMidName; }
36: }
37:
38: public ICollection<CourseAssignment> CourseAssignments { get; set; }
39: public OfficeAssignment OfficeAssignment { get; set; }
40: }
41: }
42: #endregion
43: #elif AfterInheritance
44: #region snippet_AfterInheritance
45: using System;
46: using System.Collections.Generic;
47: using System.ComponentModel.DataAnnotations;
48: using System.ComponentModel.DataAnnotations.Schema;
49:
50: namespace ContosoUniversity.Models
51: {
52: public class Instructor : Person
53: {
54: [DataType(DataType.Date)]
55: [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
56: [Display(Name = "Hire Date")]
57: public DateTime HireDate { get; set; }
58:
59: public ICollection<CourseAssignment> CourseAssignments { get; set; }
60: public OfficeAssignment OfficeAssignment { get; set; }
61: }
62: }
63: #endregion
64: #endif
Make the same changes in Student.cs.
[!code-csharpMain]
1: #define AfterInheritance // or Intro or StringLength or DataType or BeforeInheritance
2:
3: #if Intro
4: #region snippet_Intro
5: using System;
6: using System.Collections.Generic;
7:
8: namespace ContosoUniversity.Models
9: {
10: public class Student
11: {
12: public int ID { get; set; }
13: public string LastName { get; set; }
14: public string FirstMidName { get; set; }
15: public DateTime EnrollmentDate { get; set; }
16:
17: public ICollection<Enrollment> Enrollments { get; set; }
18: }
19: }
20: #endregion
21:
22: #elif DataType
23: #region snippet_DataType
24: using System;
25: using System.Collections.Generic;
26: using System.ComponentModel.DataAnnotations;
27:
28: namespace ContosoUniversity.Models
29: {
30: public class Student
31: {
32: public int ID { get; set; }
33: public string LastName { get; set; }
34: public string FirstMidName { get; set; }
35: [DataType(DataType.Date)]
36: [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
37: public DateTime EnrollmentDate { get; set; }
38:
39: public ICollection<Enrollment> Enrollments { get; set; }
40: }
41: }
42: #endregion
43:
44: #elif StringLength
45: #region snippet_StringLength
46: using System;
47: using System.Collections.Generic;
48: using System.ComponentModel.DataAnnotations;
49:
50: namespace ContosoUniversity.Models
51: {
52: public class Student
53: {
54: public int ID { get; set; }
55: [StringLength(50)]
56: public string LastName { get; set; }
57: [StringLength(50, ErrorMessage = "First name cannot be longer than 50 characters.")]
58: public string FirstMidName { get; set; }
59: [DataType(DataType.Date)]
60: [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
61: public DateTime EnrollmentDate { get; set; }
62:
63: public ICollection<Enrollment> Enrollments { get; set; }
64: }
65: }
66: #endregion
67:
68: #elif Column
69: #region snippet_Column
70: using System;
71: using System.Collections.Generic;
72: using System.ComponentModel.DataAnnotations;
73: using System.ComponentModel.DataAnnotations.Schema;
74:
75: namespace ContosoUniversity.Models
76: {
77: public class Student
78: {
79: public int ID { get; set; }
80: [StringLength(50)]
81: public string LastName { get; set; }
82: [StringLength(50, ErrorMessage = "First name cannot be longer than 50 characters.")]
83: [Column("FirstName")]
84: public string FirstMidName { get; set; }
85: [DataType(DataType.Date)]
86: [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
87: public DateTime EnrollmentDate { get; set; }
88:
89: public ICollection<Enrollment> Enrollments { get; set; }
90: }
91: }
92: #endregion
93:
94:
95: #elif BeforeInheritance
96: #region snippet_BeforeInheritance
97: using System;
98: using System.Collections.Generic;
99: using System.ComponentModel.DataAnnotations;
100: using System.ComponentModel.DataAnnotations.Schema;
101:
102: namespace ContosoUniversity.Models
103: {
104: public class Student
105: {
106: public int ID { get; set; }
107: [Required]
108: [StringLength(50)]
109: [Display(Name = "Last Name")]
110: public string LastName { get; set; }
111: [Required]
112: [StringLength(50, ErrorMessage = "First name cannot be longer than 50 characters.")]
113: [Column("FirstName")]
114: [Display(Name = "First Name")]
115: public string FirstMidName { get; set; }
116: [DataType(DataType.Date)]
117: [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
118: [Display(Name = "Enrollment Date")]
119: public DateTime EnrollmentDate { get; set; }
120: [Display(Name = "Full Name")]
121: public string FullName
122: {
123: get
124: {
125: return LastName + ", " + FirstMidName;
126: }
127: }
128:
129: public ICollection<Enrollment> Enrollments { get; set; }
130: }
131: }
132: #endregion
133: #elif AfterInheritance
134: #region snippet_AfterInheritance
135: using System;
136: using System.Collections.Generic;
137: using System.ComponentModel.DataAnnotations;
138: using System.ComponentModel.DataAnnotations.Schema;
139:
140: namespace ContosoUniversity.Models
141: {
142: public class Student : Person
143: {
144: [DataType(DataType.Date)]
145: [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
146: [Display(Name = "Enrollment Date")]
147: public DateTime EnrollmentDate { get; set; }
148:
149:
150: public ICollection<Enrollment> Enrollments { get; set; }
151: }
152: }
153: #endregion
154: #endif
Add the Person entity type to the data model
Add the Person entity type to SchoolContext.cs. The new lines are highlighted.
[!code-csharpMain]
1: #define AfterInheritance // or Intro or TableNames or BeforeInheritance
2:
3: #if Intro
4: #region snippet_Intro
5: using ContosoUniversity.Models;
6: using Microsoft.EntityFrameworkCore;
7:
8: namespace ContosoUniversity.Data
9: {
10: public class SchoolContext : DbContext
11: {
12: public SchoolContext(DbContextOptions<SchoolContext> options) : base(options)
13: {
14: }
15:
16: public DbSet<Course> Courses { get; set; }
17: public DbSet<Enrollment> Enrollments { get; set; }
18: public DbSet<Student> Students { get; set; }
19: }
20: }
21: #endregion
22:
23: #elif TableNames
24: #region snippet_TableNames
25: using ContosoUniversity.Models;
26: using Microsoft.EntityFrameworkCore;
27:
28: namespace ContosoUniversity.Data
29: {
30: public class SchoolContext : DbContext
31: {
32: public SchoolContext(DbContextOptions<SchoolContext> options) : base(options)
33: {
34: }
35:
36: public DbSet<Course> Courses { get; set; }
37: public DbSet<Enrollment> Enrollments { get; set; }
38: public DbSet<Student> Students { get; set; }
39:
40: protected override void OnModelCreating(ModelBuilder modelBuilder)
41: {
42: modelBuilder.Entity<Course>().ToTable("Course");
43: modelBuilder.Entity<Enrollment>().ToTable("Enrollment");
44: modelBuilder.Entity<Student>().ToTable("Student");
45: }
46: }
47: }
48: #endregion
49:
50: #elif BeforeInheritance
51: #region snippet_BeforeInheritance
52: using ContosoUniversity.Models;
53: using Microsoft.EntityFrameworkCore;
54:
55: namespace ContosoUniversity.Data
56: {
57: public class SchoolContext : DbContext
58: {
59: public SchoolContext(DbContextOptions<SchoolContext> options) : base(options)
60: {
61: }
62:
63: public DbSet<Course> Courses { get; set; }
64: public DbSet<Enrollment> Enrollments { get; set; }
65: public DbSet<Student> Students { get; set; }
66: public DbSet<Department> Departments { get; set; }
67: public DbSet<Instructor> Instructors { get; set; }
68: public DbSet<OfficeAssignment> OfficeAssignments { get; set; }
69: public DbSet<CourseAssignment> CourseAssignments { get; set; }
70:
71: protected override void OnModelCreating(ModelBuilder modelBuilder)
72: {
73: modelBuilder.Entity<Course>().ToTable("Course");
74: modelBuilder.Entity<Enrollment>().ToTable("Enrollment");
75: modelBuilder.Entity<Student>().ToTable("Student");
76: modelBuilder.Entity<Department>().ToTable("Department");
77: modelBuilder.Entity<Instructor>().ToTable("Instructor");
78: modelBuilder.Entity<OfficeAssignment>().ToTable("OfficeAssignment");
79: modelBuilder.Entity<CourseAssignment>().ToTable("CourseAssignment");
80:
81: modelBuilder.Entity<CourseAssignment>()
82: .HasKey(c => new { c.CourseID, c.InstructorID });
83: }
84: }
85: }
86: #endregion
87: #elif AfterInheritance
88: #region snippet_AfterInheritance
89: using ContosoUniversity.Models;
90: using Microsoft.EntityFrameworkCore;
91:
92: namespace ContosoUniversity.Data
93: {
94: public class SchoolContext : DbContext
95: {
96: public SchoolContext(DbContextOptions<SchoolContext> options) : base(options)
97: {
98: }
99:
100: public DbSet<Course> Courses { get; set; }
101: public DbSet<Enrollment> Enrollments { get; set; }
102: public DbSet<Student> Students { get; set; }
103: public DbSet<Department> Departments { get; set; }
104: public DbSet<Instructor> Instructors { get; set; }
105: public DbSet<OfficeAssignment> OfficeAssignments { get; set; }
106: public DbSet<CourseAssignment> CourseAssignments { get; set; }
107: public DbSet<Person> People { get; set; }
108:
109: protected override void OnModelCreating(ModelBuilder modelBuilder)
110: {
111: modelBuilder.Entity<Course>().ToTable("Course");
112: modelBuilder.Entity<Enrollment>().ToTable("Enrollment");
113: modelBuilder.Entity<Student>().ToTable("Student");
114: modelBuilder.Entity<Department>().ToTable("Department");
115: modelBuilder.Entity<Instructor>().ToTable("Instructor");
116: modelBuilder.Entity<OfficeAssignment>().ToTable("OfficeAssignment");
117: modelBuilder.Entity<CourseAssignment>().ToTable("CourseAssignment");
118: modelBuilder.Entity<Person>().ToTable("Person");
119:
120: modelBuilder.Entity<CourseAssignment>()
121: .HasKey(c => new { c.CourseID, c.InstructorID });
122: }
123: }
124: }
125: #endregion
126: #endif
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 Person table in place of the Student and Instructor tables.
Create and customize migration code
Save your changes and build the project. Then open the command window in the project folder and enter the following command:
dotnet ef migrations add InheritanceDon’t run the database update command yet. That command will result in lost data because it will drop the Instructor table and rename the Student table to Person. You need to provide custom code to preserve existing data.
Open *Migrations/<timestamp>_Inheritance.cs* and replace the Up method with the following code:
[!code-csharpMain]
1: using System;
2: using System.Collections.Generic;
3: using Microsoft.EntityFrameworkCore.Migrations;
4: using Microsoft.EntityFrameworkCore.Metadata;
5:
6: namespace ContosoUniversity.Migrations
7: {
8: public partial class Inheritance : Migration
9: {
10: #region snippet_Up
11: protected override void Up(MigrationBuilder migrationBuilder)
12: {
13: migrationBuilder.DropForeignKey(
14: name: "FK_Enrollment_Student_StudentID",
15: table: "Enrollment");
16:
17: migrationBuilder.DropIndex(name: "IX_Enrollment_StudentID", table: "Enrollment");
18:
19: migrationBuilder.RenameTable(name: "Instructor", newName: "Person");
20: migrationBuilder.AddColumn<DateTime>(name: "EnrollmentDate", table: "Person", nullable: true);
21: migrationBuilder.AddColumn<string>(name: "Discriminator", table: "Person", nullable: false, maxLength: 128, defaultValue: "Instructor");
22: migrationBuilder.AlterColumn<DateTime>(name: "HireDate", table: "Person", nullable: true);
23: migrationBuilder.AddColumn<int>(name: "OldId", table: "Person", nullable: true);
24:
25: // Copy existing Student data into new Person table.
26: migrationBuilder.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");
27: // Fix up existing relationships to match new PK's.
28: migrationBuilder.Sql("UPDATE dbo.Enrollment SET StudentId = (SELECT ID FROM dbo.Person WHERE OldId = Enrollment.StudentId AND Discriminator = 'Student')");
29:
30: // Remove temporary key
31: migrationBuilder.DropColumn(name: "OldID", table: "Person");
32:
33: migrationBuilder.DropTable(
34: name: "Student");
35:
36: migrationBuilder.CreateIndex(
37: name: "IX_Enrollment_StudentID",
38: table: "Enrollment",
39: column: "StudentID");
40:
41: migrationBuilder.AddForeignKey(
42: name: "FK_Enrollment_Person_StudentID",
43: table: "Enrollment",
44: column: "StudentID",
45: principalTable: "Person",
46: principalColumn: "ID",
47: onDelete: ReferentialAction.Cascade);
48: }
49: #endregion
50:
51: protected override void Down(MigrationBuilder migrationBuilder)
52: {
53: migrationBuilder.DropForeignKey(
54: name: "FK_CourseAssignment_Person_InstructorID",
55: table: "CourseAssignment");
56:
57: migrationBuilder.DropForeignKey(
58: name: "FK_Department_Person_InstructorID",
59: table: "Department");
60:
61: migrationBuilder.DropForeignKey(
62: name: "FK_Enrollment_Person_StudentID",
63: table: "Enrollment");
64:
65: migrationBuilder.DropForeignKey(
66: name: "FK_OfficeAssignment_Person_InstructorID",
67: table: "OfficeAssignment");
68:
69: migrationBuilder.DropTable(
70: name: "Person");
71:
72: migrationBuilder.CreateTable(
73: name: "Instructor",
74: columns: table => new
75: {
76: ID = table.Column<int>(nullable: false)
77: .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
78: FirstName = table.Column<string>(maxLength: 50, nullable: false),
79: HireDate = table.Column<DateTime>(nullable: false),
80: LastName = table.Column<string>(maxLength: 50, nullable: false)
81: },
82: constraints: table =>
83: {
84: table.PrimaryKey("PK_Instructor", x => x.ID);
85: });
86:
87: migrationBuilder.CreateTable(
88: name: "Student",
89: columns: table => new
90: {
91: ID = table.Column<int>(nullable: false)
92: .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
93: EnrollmentDate = table.Column<DateTime>(nullable: false),
94: FirstName = table.Column<string>(maxLength: 50, nullable: false),
95: LastName = table.Column<string>(maxLength: 50, nullable: false)
96: },
97: constraints: table =>
98: {
99: table.PrimaryKey("PK_Student", x => x.ID);
100: });
101:
102: migrationBuilder.AddForeignKey(
103: name: "FK_CourseAssignment_Instructor_InstructorID",
104: table: "CourseAssignment",
105: column: "InstructorID",
106: principalTable: "Instructor",
107: principalColumn: "ID",
108: onDelete: ReferentialAction.Cascade);
109:
110: migrationBuilder.AddForeignKey(
111: name: "FK_Department_Instructor_InstructorID",
112: table: "Department",
113: column: "InstructorID",
114: principalTable: "Instructor",
115: principalColumn: "ID",
116: onDelete: ReferentialAction.Restrict);
117:
118: migrationBuilder.AddForeignKey(
119: name: "FK_Enrollment_Student_StudentID",
120: table: "Enrollment",
121: column: "StudentID",
122: principalTable: "Student",
123: principalColumn: "ID",
124: onDelete: ReferentialAction.Cascade);
125:
126: migrationBuilder.AddForeignKey(
127: name: "FK_OfficeAssignment_Instructor_InstructorID",
128: table: "OfficeAssignment",
129: column: "InstructorID",
130: principalTable: "Instructor",
131: principalColumn: "ID",
132: onDelete: ReferentialAction.Cascade);
133: }
134: }
135: }
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.
Copies data from the Student table into the Person table. This causes students to get assigned new primary key values.
Fixes foreign key values that point to students.
Re-creates foreign key constraints and indexes, now pointing them to the Person table.
(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 database update command:
dotnet ef database update(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 making schema changes in a database that has existing data. If you get migration errors that you can’t resolve, you can either change the database name in the connection string or delete the database. With a new database, there is no data to migrate, and the update-database command is more likely to complete without errors. To delete the database, use SSOX or run the
database dropCLI command.
Test with inheritance implemented
Run the app and try various pages. Everything works the same as it did before.
In SQL Server Object Explorer, expand Data Connections/SchoolContext and then Tables, and you see that the Student and Instructor tables have been replaced by a Person table. Open the Person table designer 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.
Summary
You’ve implemented table-per-hierarchy inheritance for the Person, Student, and Instructor classes. For more information about inheritance in Entity Framework Core, see Inheritance. In the next tutorial you’ll see how to handle a variety of relatively advanced Entity Framework scenarios.
)
|
|