Walkthrough: Mapping Inheritance - Table-per-Hierarchy (Entity Data Model Tools)

Article
02/21/2011

This topic shows how to implement table-per-hierarchy inheritance by altering the conceptual model defined in an .edmx file. Table-per-hierarchy inheritance uses one database table to maintain data for all of the entity types in an inheritance hierarchy.

In this walkthrough, you will implement table-per-hierarchy inheritance by modifying the conceptual model used in the CourseManager application (for more information, see the Prerequisites section, later in this topic).

In the CourseManager conceptual model, the Person entity type has two properties, HireDate and EnrollmentDate, which can belong to new entity types (Instructor and Student respectively) that inherit from Person. The following steps summarize how to implement table-per-hierarchy inheritance in this case. The procedures in this walkthrough provide more details.

Create two new entity types, Instructor and Student.
Set the base type for each new entity type to Person.
Move the HireDate property from Person to Instructor and move the EnrollmentDate property from Person to Student.
Make the Person entity type an abstract type.
Map the Instructor entity type to the Person table with two conditions: HireDate Is Not Null and EnrollmentDate Is Null.
Map the Student entity type to the Person table with two conditions: EnrollmentDate Is Not Null and HireDate Is Null.

Prerequisites

To complete this walkthrough, you must build the CourseManager application. For more information and instructions, see the Entity Framework Quickstart. After you build this application, you will implement table-per-hierarchy inheritance by modifying its conceptual model and mappings. You will then extend the application's functionality to display the enrollment for a selected course.

Note
Because many of the walkthrough topics in this documentation use the CourseManager application as a starting point, we recommend that you use a copy of the CourseManager application for this walkthrough, instead of editing the original CourseManager code.

This walkthrough assumes that the reader has basic competency with Visual Studio, the .NET Framework, and programming in either Visual C# or Visual Basic.

Implementing Table-per-Hierarchy Inheritance

In this procedure, you will alter the SchoolModel to implement table-per-hierarchy inheritance. To watch a video presentation of the following procedure, see How Do I: Model Table-per-Hierarchy Inheritance with the Entity Framework Tools.

To implement table-per-hierarchy inheritance

Open the CourseManager solution in Visual Studio.
In Solution Explorer, double-click the School.edmx file.

The School.edmx file opens in the ADO.NET Entity Data Model Designer (Entity Designer).
Right-click on an empty space of the design surface of the Entity Designer, point to Add, and click Entity.

The New Entity dialog box appears.
Type Instructor for the Entity name and select Person from the drop-down list for the Base type.
Click OK.

A new entity type is created and displayed on the design surface.
Repeat steps 3 through 5, but type Student for the Entity name in the second step.

You now have two new entity types, Instructor and Student, displayed on the design surface. Arrows point from the new entity types to the Person entity type; this indicates that Person is the base type for the new entity types.
Right-click the HireDate property (under Scalar Properties) of the Person entity type. Select Cut.
Right-click Scalar Properties of the Instructor entity type and select Paste.
Right-click the HireDate property and select Properties.
In the Properties window, set the Nullable property to false.
Repeat steps 7 through 10, but cut the EnrollmentDate property of the Person entity type and paste it into the Student entity type.
Select the Person entity type. In the Properties window, set its Abstract property to true.

You do not have to use abstract types to model table-per-hierarchy scenarios in general. Abstract types are used in this example to demonstrate their use in a conceptual model.

Note

The remaining steps in this procedure require the Mapping Details window. If this window is not visible, right-click the design surface and select Mapping Details.
Select the Instructor entity type and click <Add a Table or View> in the Mapping Details window.

The <Add a Table or View> field becomes a drop-down list of tables or views to which the selected entity can be mapped.
Select Person from the drop-down list.

The Mapping Details window is updated with default column mappings and an option for adding a condition.
Click on <Add a Condition>.

The <Add a Condition> field becomes a drop-down list of columns for which conditions can be set.
Select HireDate from the drop-down list.

Another <Add a Condition> field appears.
In the Operator column of the Mapping Details window, select Is from the drop-down list.
In the Property/Value column, select Not Null.
Click on <Add a Condition>.
Select EnrollmentDate from the drop-down list.
In the Operator column, select Is from the drop-down list.
In the Property/Value column, select Null.

Note

If a value/property is used in a condition it cannot also be an entity property, unless the condition uses an Is Null or Is Not Null comparison.
Repeat steps 13 through 22 for the Student entity type, but make the conditions HireDate Is Null and EnrollmentDate Is Not Null.

Note
The remaining steps in this procedure require the Mapping Details window. If this window is not visible, right-click the design surface and select Mapping Details.

Note
If a value/property is used in a condition it cannot also be an entity property, unless the condition uses an Is Null or Is Not Null comparison.

Table-per-hierarchy inheritance is now implemented.

Creating an Association Between Instructor and OfficeAssignment

In this section you will change the association between Person and OfficeAssignment so that it is between Instructor and OfficeAssignment. Changing the association in this way is consistent with the intent of the model.

To create an association between Instructor and OfficeAssignment

Right-click the association between Person and OfficeAssignment and select Delete.
Right-click an empty space on the design surface of the Entity Designer, point to Add, and select Association.
For one End of the association, select Instructor from the Entity drop-down list, and select 1 (One) from the Multiplicity drop-down list.
For the other End of the association, select OfficeAssignment from the Entity drop-down list, and select 0..1 (Zero or One) from the Multiplicity drop-down list.
Click OK.
Right-click the new association between Instructor and OfficeAssignment and selcet Properties.
In the Properties window, click Referential Constraint, then click the ellipses button (…) that appears in the value column.

The Referential Constraint dialog box appears.
Select Instructor from the Principal drop-down list.
Click OK.

You have now added an association between Instructor and OfficeAssignment.

Constructing the User Interface

Next, you will add a button to the CourseViewer form that loads and displays the Enrollment form. Then, you will add two ComboBox controls and a ListBox control to the Enrollment form. The first ComboBox allows you to select a department. The second ComboBox allows you to select a course based on the department selected in the first ComboBox. A list of students and instructors for the selected course is displayed in the ListBox control.

To construct the user interface

Right-click the CourseManager project in the Solution Explorer, point to Add, and select New Item.

The Add New Item dialog box appears.
Select Windows Form, set the name of the form to Enrollment.vb or Enrollment.cs (depending on which language you are using), and click Add.

A new form is added to the project and opens in the form designer. The name of the form is set to Enrollment and the text is set to Enrollment.
Drag a ComboBox control from the Toolbox to the form and set its name to departmentList in the Properties window.
Drag another ComboBox control to the form and set its name to courseList.
Drag a Listbox control from the Toolbox to the form. Set its name to studentList in the Properties window.
In the Solution Explorer, double click CourseViewer.cs or CourseViewer.vb.

The design view of the CourseViewer form appears.
Drag a Button control from the Toolbox to the CourseViewer form.
In the Properties window, set the name of the button to viewEnrollment and set the text of the button to View Enrollment.
Double click the viewEnrollment button.

The code-behind file for the CourseViewer form opens.

Add the following code to the viewEnrollment_click event handler:

Dim enrollmentForm As New Enrollment()
enrollmentForm.Visible = True

Enrollment enrollmentForm = new Enrollment();
enrollmentForm.Visible = true;

The user interface is now complete.

Querying the Conceptual Model

In this procedure, you will query the conceptual model and bind the results to Windows Forms controls. For more information about binding objects to controls, see Binding Objects to Controls (Entity Framework).

To query the conceptual model

With the Enrollment form open in the form designer, double-click the Enrollment form.

The code-behind file for the Enrollment form opens.

Add the following using (C#) or Imports (Visual Basic) statements.

Imports System.Data.Objects
Imports System.Data.Objects.DataClasses

using System.Data.Objects;
using System.Data.Objects.DataClasses;

Add a property to the Enrollment class that represents the data context:

' Create an ObjectContext instance based on SchoolEntity.
Private schoolContext As SchoolEntities

// Create an ObjectContext instance based on SchoolEntities.
private SchoolEntities schoolContext;

In the Enrollment_Load event handler, add code to initialize the object context and bind the ComboBox control to a query that returns all departments and related course information.

      ' Initialize the ObjectContext.
      schoolContext = New SchoolEntities()

      ' Define a query that returns all Department objects and 
      ' related Course objects, ordered by name.
      Dim departmentQuery As ObjectQuery(Of Department) = _
          schoolContext.Departments.Include("Courses") _
          .OrderBy("it.Name")

      ' Bind the ComboBox control to the query, which is 
      ' executed during data binding.
      Me.departmentList.DataSource = departmentQuery _
.Execute(MergeOption.OverwriteChanges)
      Me.departmentList.DisplayMember = "Name"

// Initialize the ObjectContext.
schoolContext = new SchoolEntities();

// Define a query that returns all Department objects
// and related Course objects, ordered by name.
ObjectQuery<Department> departmentQuery =
    schoolContext.Departments.Include("Courses")
                            .OrderBy("it.Name");

// Bind the ComboBox control to the query.
this.departmentList.DataSource = departmentQuery
    .Execute(MergeOption.OverwriteChanges);
this.departmentList.DisplayMember = "Name";

Return to the design view of the Enrollment form and double-click the departmentList ComboBox control.

The departmentList_SelectedIndexChanged event handler is created in the code behind file.

Add code to the event handler to bind the courseList ComboBox control to Course properties of the selected Department.

' Get the object for the selected department
Dim department As Department = CType(Me.departmentList _
        .SelectedItem, Department)

' Bind the ComboBox control Course properties of
' the selected Department.
courseList.DataSource = department.Courses
courseList.DisplayMember = "Title"

// Get the object for the selected department.
Department department =
        (Department)this.departmentList.SelectedItem;

// Bind the ComboBox control to the Course  
// properties of the selected Department.
courseList.DataSource = department.Courses;
courseList.DisplayMember = "Title";

Return to the design view of the Enrollment form and double-click the courseList ComboBox control.

The courseList_SelectedIndexChanged event handler is created in the code-behind file.

Add code to the event handler to display a list of students in the ListBox.

Me.studentList.Items.Clear()

' Get the CourseID from the selected Course.
Dim course As Course = CType(Me.courseList.SelectedItem,  _
     Course)
Dim courseId As Integer = course.CourseID

' Get all StudentGrade objects for the supplied CourseID
Dim studentQuery As Course = schoolContext.Courses.Where _
     ("it.CourseID = @courseID", New ObjectParameter _
      ("courseID", courseId)).Include("StudentGrades"). _
      FirstOrDefault()

' Get all students for each StudentGrade
For Each cg As StudentGrade In studentQuery.StudentGrades
    cg.PersonReference.Load()
    studentList.Items.Add(cg.Person.LastName + ", " + _
                        cg.Person.FirstName)
Next

studentList.Items.Add(" ")

' Get all Instructor types for the supplied CourseID
Dim instructorQuery As Course = schoolContext.Courses. _
     Where("it.CourseID = @courseID", New ObjectParameter _
           ("courseID", courseId)).Include("People") _
           .FirstOrDefault()

' Display each instructor for the specified Course
For Each Instructor As Instructor In instructorQuery.People
    Me.studentList.Items.Add("Instructor: " + Instructor. _
                    LastName + ", " + Instructor.FirstName)
Next

studentList.Items.Clear();

// Get the CourseID from the selected Course.
Course course = (Course)courseList.SelectedItem;
int courseId = course.CourseID;

// Get all StudentGrade types for the supplied CourseID
Course studentQuery = schoolContext.Courses.Where(
    "it.CourseID = @courseID", new ObjectParameter
        ("courseID", courseId)).Include("StudentGrades").
        FirstOrDefault();

// Get all students for each StudentGrade
foreach (StudentGrade sg in studentQuery.StudentGrades)
{
    sg.PersonReference.Load();
    studentList.Items.Add(sg.Person.LastName + ", " +
        sg.Person.FirstName);
}

studentList.Items.Add(" ");

// Get all Instructor types for the supplied CourseID
Course instructorQuery = schoolContext.Courses.Where(
    "it.CourseID = @courseID", new ObjectParameter
        ("courseID", courseId)).Include("People").
        FirstOrDefault();

// Display each instructor for the specified course.
foreach (Instructor instructor in instructorQuery.People.
    OfType<Instructor>())
{
    studentList.Items.Add("Instructor: " + instructor.
        LastName + ", " + instructor.FirstName);
}

The application is now complete. Press Ctrl+F5 to run the application. Click the View Enrollment button to load the Enrollment form. Course enrollment and instructors for the selected course are displayed in the ListBox.

Code Listing

This section lists the final version of the code-behind file for the Enrollment form.

Imports System.Data.Objects
Imports System.Data.Objects.DataClasses

Public Class Enrollment

    ' Create an ObjectContext instance based on SchoolEntity.
    Private schoolContext As SchoolEntities

    Private Sub Enrollment_Load(ByVal sender As System.Object, _
                ByVal e As System.EventArgs) Handles MyBase.Load
        ' Initialize the ObjectContext.
        schoolContext = New SchoolEntities()

        ' Define a query that returns all Department objects and 
        ' related Course objects, ordered by name.
        Dim departmentQuery As ObjectQuery(Of Department) = _
            schoolContext.Departments.Include("Courses") _
            .OrderBy("it.Name")

        ' Bind the ComboBox control to the query, which is 
        ' executed during data binding.
        Me.departmentList.DataSource = departmentQuery _
  .Execute(MergeOption.OverwriteChanges)
        Me.departmentList.DisplayMember = "Name"
    End Sub

    Private Sub departmentList_SelectedIndexChanged(ByVal sender _
            As System.Object, ByVal e As System.EventArgs) Handles _
            departmentList.SelectedIndexChanged
        ' Get the object for the selected department
        Dim department As Department = CType(Me.departmentList _
                .SelectedItem, Department)

        ' Bind the ComboBox control Course properties of
        ' the selected Department.
        courseList.DataSource = department.Courses
        courseList.DisplayMember = "Title"
    End Sub

    Private Sub courseList_SelectedIndexChanged(ByVal sender As  _
            System.Object, ByVal e As System.EventArgs) Handles _
            courseList.SelectedIndexChanged
        Me.studentList.Items.Clear()

        ' Get the CourseID from the selected Course.
        Dim course As Course = CType(Me.courseList.SelectedItem,  _
             Course)
        Dim courseId As Integer = course.CourseID

        ' Get all StudentGrade objects for the supplied CourseID
        Dim studentQuery As Course = schoolContext.Courses.Where _
             ("it.CourseID = @courseID", New ObjectParameter _
              ("courseID", courseId)).Include("StudentGrades"). _
              FirstOrDefault()

        ' Get all students for each StudentGrade
        For Each cg As StudentGrade In studentQuery.StudentGrades
            cg.PersonReference.Load()
            studentList.Items.Add(cg.Person.LastName + ", " + _
                                cg.Person.FirstName)
        Next

        studentList.Items.Add(" ")

        ' Get all Instructor types for the supplied CourseID
        Dim instructorQuery As Course = schoolContext.Courses. _
             Where("it.CourseID = @courseID", New ObjectParameter _
                   ("courseID", courseId)).Include("People") _
                   .FirstOrDefault()

        ' Display each instructor for the specified Course
        For Each Instructor As Instructor In instructorQuery.People
            Me.studentList.Items.Add("Instructor: " + Instructor. _
                            LastName + ", " + Instructor.FirstName)
        Next
    End Sub
End Class

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Objects;
using System.Data.Objects.DataClasses;

namespace CourseManager
{
    public partial class Enrollment : Form
    {
        // Create an ObjectContext instance based on SchoolEntities.
        private SchoolEntities schoolContext;

        public Enrollment()
        {
            InitializeComponent();
        }

        private void Enrollment_Load(object sender, EventArgs e)
        {
            // Initialize the ObjectContext.
            schoolContext = new SchoolEntities();

            // Define a query that returns all Department objects
            // and related Course objects, ordered by name.
            ObjectQuery<Department> departmentQuery =
                schoolContext.Departments.Include("Courses")
                                        .OrderBy("it.Name");

            // Bind the ComboBox control to the query.
            this.departmentList.DataSource = departmentQuery
                .Execute(MergeOption.OverwriteChanges);
            this.departmentList.DisplayMember = "Name";
        }

        private void departmentList_SelectedIndexChanged(object sender,
            EventArgs e)
        {
            // Get the object for the selected department.
            Department department =
                    (Department)this.departmentList.SelectedItem;

            // Bind the ComboBox control to the Course  
            // properties of the selected Department.
            courseList.DataSource = department.Courses;
            courseList.DisplayMember = "Title";
        }

        private void courseList_SelectedIndexChanged(object sender,
            EventArgs e)
        {
            studentList.Items.Clear();

            // Get the CourseID from the selected Course.
            Course course = (Course)courseList.SelectedItem;
            int courseId = course.CourseID;

            // Get all StudentGrade types for the supplied CourseID
            Course studentQuery = schoolContext.Courses.Where(
                "it.CourseID = @courseID", new ObjectParameter
                    ("courseID", courseId)).Include("StudentGrades").
                    FirstOrDefault();

            // Get all students for each StudentGrade
            foreach (StudentGrade sg in studentQuery.StudentGrades)
            {
                sg.PersonReference.Load();
                studentList.Items.Add(sg.Person.LastName + ", " +
                    sg.Person.FirstName);
            }

            studentList.Items.Add(" ");

            // Get all Instructor types for the supplied CourseID
            Course instructorQuery = schoolContext.Courses.Where(
                "it.CourseID = @courseID", new ObjectParameter
                    ("courseID", courseId)).Include("People").
                    FirstOrDefault();

            // Display each instructor for the specified course.
            foreach (Instructor instructor in instructorQuery.People.
                OfType<Instructor>())
            {
                studentList.Items.Add("Instructor: " + instructor.
                    LastName + ", " + instructor.FirstName);
            }
        }
    }
}

Next Steps

You have successfully implemented table-per-hierarchy inheritance in a conceptual model. For more information about how to define a conceptual model with table-per-hierarchy inheritance, see How to: Define a Model with Table-per-Hierarchy Inheritance (Entity Framework). For more information about how to build applications with the Entity Framework, see ADO.NET Entity Framework.