MVC 5 with EF 6 in Visual Basic - Implementing Inheritance

5 (3 votes)

This tutorial is the eleventh in a series of 12 that teach you how to build MVC 5 applications using Entity Framework for data access and Visual Basic. 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.

The original tutorial series, produced by Tom Dykstra and Rick Anderson ( @RickAndMSFT ) was written using the C# language. My versions keep as close to the originals as possible, changing only the coding language. The narrative text is largely unchanged from the original and is used with permission from Microsoft.

This tutorial series teaches you how to create ASP.NET MVC 5 applications using the Entity Framework 6 and Visual Studio 2013 Express for Web. This series uses the Code First workflow. For information about how to choose between Code First, Database First, and Model First, see Entity Framework Development Workflows.

The tutorial series comprises 12 sections in total. They are intended to be followed sequentially as each section builds on the knowledge imparted in the previous sections. Progress through the sections is reflected in a Visual Studio Express for Web project download that accompanies each section which features the web application that you build through the series.

Download the code

The code for this section is available here. Save the .zip file to a convenient location and then extract the contents. Make sure you have an edition of Visual Studio 2013 installed (Express for Web, Professional, Premium or Ultimate) and double click the .sln file. Once the project is opened in your IDE, press Shift+Ctrl+B to build the solution. This will ensure that all packages are restored from Nuget and may take a while depending on your Internet connection speed.

The navigation path through the series is as follows:

  1. Creating an Entity Framework Data Model
  2. Implementing Basic CRUD Functionality
  3. Sorting, Filtering and Paging
  4. Connection Resiliency and Command Interception
  5. Code First Migrations and Deployment
  6. Creating a More Complex Data Model
  7. Reading Related Data
  8. Updating Related Data
  9. Async and Stored Procedures
  10. Handling Concurrency
  11. Implementing-Inheritance
  12. Advanced Entity Framework Scenarios

Options for mapping inheritance to database tables

The Instructor and Student classes in the School data model have several properties that are identical:

MVC5 With EF6

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 which contains only those shared properties, then make the Instructor and Student entities inherit from that base class, as shown in the following illustration:

MVC5 With EF6

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.

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 PersonStudent, 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 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 Person class, change the Instructor and Student classes to derive from Person, add the new class to the DbContext, and create a migration. (For information about how to implement the other inheritance patterns, see Mapping the Table-Per-Type (TPT) Inheritance and Mapping the Table-Per-Concrete Class (TPC) Inheritance in the MSDN Entity Framework documentation.)

Create the Person class

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

Imports System.ComponentModel.DataAnnotations
Imports System.ComponentModel.DataAnnotations.Schema

Namespace Models
    Public Class Person
        Public Property ID As Integer
        <Required>
        <StringLength(50)>
        <Display(Name:="Last Name")>
        Public Property LastName As String
        <Required>
        <StringLength(50, ErrorMessage:="First name cannot be longer than 50 characters.")>
        <Column("FirstName")>
        <Display(Name:="First Name")>
        Public Property FirstMidName As String

        <Display(Name:="Full Name")>
        Public Property FullName() As String
            Get
                Return String.Format("{0}, {1}", LastName, FirstMidName)
            End Get
            Set(value As String)

            End Set
        End Property
    End Class
End Namespace

Make Student and Instructor classes inherit from Person

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

 

Imports System.ComponentModel.DataAnnotations
Imports System.ComponentModel.DataAnnotations.Schema
Namespace Models
    Public Class Instructor
        Inherits Person
        
        <DataType(DataType.Date)>
        <DisplayFormat(DataFormatString:="{0:yyyy-MM-dd}", ApplyFormatInEditMode:=True)>
        <Display(Name:="Hire Date")>
        Public Property HireDate As DateTime

        Public Overridable Property Courses As ICollection(Of Course)
        Public Overridable Property OfficeAssignment As OfficeAssignment
    End Class
End Namespace

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

Imports System.ComponentModel.DataAnnotations
Imports System.ComponentModel.DataAnnotations.Schema

Namespace Models
    Public Class Student
        Inherits Person
        
        <DataType(DataType.Date)>
        <DisplayFormat(DataFormatString:="{0:yyyy-MM-dd}", ApplyFormatInEditMode:=True)>
        <Display(Name:="Enrollment Date")>
        Public Property EnrollmentDate As DateTime

        Public Overridable Property Enrollments As ICollection(Of Enrollment)
    End Class
End Namespace

Add the Person Entity Type to the Model

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

Public Property People As DbSet(Of  Person)

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 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 an error message like the following one:

Could not drop object 'dbo.Instructor' because it is referenced by a FOREIGN KEY constraint.

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

    
Public Overrides Sub 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 String() {"StudentID"})

    RenameTable(name:="dbo.Instructor", newName:="Person")
    AddColumn("dbo.Person", "EnrollmentDate", Function(c) c.DateTime())
    AddColumn("dbo.Person", "Discriminator", Function(c) c.String(nullable:=False, maxLength:=128, defaultValue:="Instructor"))
    AlterColumn("dbo.Person", "HireDate", Function(c) c.DateTime())
    AddColumn("dbo.Person", "OldId", Function(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")
End Sub

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 update-database command again.

(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:

<add name="SchoolContext" connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=ContosoUniversity2;Integrated Security=SSPI;" 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 or deploy to a new site and database. If you deploy an update to the same site you've been deploying to already, EF will get the same error there 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 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.

MVC5 With EF6

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

MVC5 With EF6

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; or create a new site and database, and deploy to the new environment.

  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.

Summary

You've implemented table-per-hierarchy inheritance for the Person, Student, and Instructor classes. For more information about this and other inheritance structures, see TPT Inheritance Pattern and TPH Inheritance Pattern on MSDN. In the next tutorial you'll see some ways to implement the repository and unit of work patterns.

You might also like...

Date Posted:
Last Updated:
Posted by:
Total Views to date: 12690

4 Comments

- Jim Parzych

in lesson ll under this heading:

Add the Person Entity Type to the Model

you are missing the line of code to add to SchoolContext.vb:

Public Property People As DbSet(Of Person)

- Mike

@Jim,

Thanks for pointing that out I eventually got round to fixing it.

- Manie Verster

Sorry Mike but on my project when run it does not show the hire date and/or enrollment date. I followed the tutorial precisely and did all you said to do but I could have made a mistake. I just don't know what.

- Mike

@Manie,

Try downloading the project that comes with this article and comparing it to what you have.

Recent Comments

Obinna Okafor 26/05/2017 16:16
In response to Razor Pages - The Elevator Pitch
Thank you for this wonderful piece, Mike. I need more of these...Keep them coming - anything more...

Cyrus 26/05/2017 06:00
In response to Razor Pages - The Elevator Pitch
There are some concern about razor pages performance. Is it faster or slower than MVC? would you a a...

Cyrus 26/05/2017 05:44
In response to Razor Pages - Understanding Handler Methods
well done, thank you....

Satyabrata Mohapatra 23/05/2017 11:41
In response to Razor Pages - Understanding Handler Methods
Nice and easy !! Great post....

Cyrus 16/05/2017 19:55
In response to Razor Pages - Getting Started With The Preview
There is something wrong related to microsoft.dotnetcore.mvc.taghelpers! if you remove it from page...

Cyrus 16/05/2017 10:18
In response to Razor Pages - Getting Started With The Preview
well done mike, it was very useful, I really appreciate that....

Satyabrata Mohapatra 16/05/2017 07:21
In response to Razor Pages - Getting Started With The Preview
Finally!!!! web pages in asp.net core!!! Super excited !!!! Thank u sir for sharing.....Awaiting on...

Daniele 14/03/2017 10:24
In response to Working With Zip Files In ASP.NET MVC
is it possible give to the user a progress bar of the zipping process? Thanks in advance. ...

Suraj 13/03/2017 22:20
In response to Working With Zip Files In ASP.NET MVC
Very nice article. Thanks....

Satyabrata Mohapatra 19/02/2017 03:01
In response to Free SSL Certificates On IIS With LetsEncrypt
Thanks for sharing. Learned a lot !!...