Walkthrough: Mapping Inheritance - Table-per-Hierarchy

Article
03/06/2009

This topic shows how to implement table-per-hierarchy inheritance by altering the conceptual model in an Entity Data Model (EDM). Table-per-hierarchy inheritance uses one database table to maintain data for all of the entity types in an inheritance hierarchy. For more information about implementing inheritance with an EDM, see Inheritance (EDM).

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

In the CourseManager EDM, 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 modify its EDM by implementing table-per-hierarchy inheritance. 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 conceptual part of the SchoolModel EDM 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.

A message box appears to tell you that defining an entity type as abstract will remove all existing function mappings for that type. Click OK.

Note

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 an EDM.

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.

Table-per-hierarchy inheritance is now implemented.

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 EDM

In this procedure, you will query the EDM 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 EDM

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 to reference the EDM namespace.

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 SchoolEntity.
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.Department.Include("Course") _
    .OrderBy("it.Name")

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

    // 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.Department.Include("Course")
                                .OrderBy("it.Name");

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

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.DisplayMember = "Title"
courseList.DataSource = department.Course

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

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

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 CourseGrade objects for the supplied CourseID
Dim studentQuery As Course = schoolContext.Course.Where _
     ("it.CourseID = @courseID", New ObjectParameter _
      ("courseID", courseId)).Include("CourseGrade"). _
      FirstOrDefault()

' Get all students for each CourseGrade
For Each cg As CourseGrade In studentQuery.CourseGrade
    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.Course. _
     Where("it.CourseID = @courseID", New ObjectParameter _
           ("courseID", courseId)).Include("Person") _
           .FirstOrDefault()

' Display each instructor for the specified Course
For Each Instructor As Instructor In instructorQuery.Person
    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 CourseGrade types for the supplied CourseID
Course studentQuery = schoolContext.Course.Where(
    "it.CourseID = @courseID", new ObjectParameter
        ("courseID", courseId)).Include("CourseGrade").
        FirstOrDefault();

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

studentList.Items.Add(" ");

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

// Display each instructor for the specified course.
foreach (Instructor instructor in instructorQuery.Person.
    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.Department.Include("Course") _
            .OrderBy("it.Name")

        ' Bind the ComboBox control to the query, which is 
        ' executed during data binding.
        Me.departmentList.DisplayMember = "Name"
        Me.departmentList.DataSource = departmentQuery _
        .Execute(MergeOption.OverwriteChanges)
    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.DisplayMember = "Title"
        courseList.DataSource = department.Course
    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 CourseGrade objects for the supplied CourseID
        Dim studentQuery As Course = schoolContext.Course.Where _
             ("it.CourseID = @courseID", New ObjectParameter _
              ("courseID", courseId)).Include("CourseGrade"). _
              FirstOrDefault()

        ' Get all students for each CourseGrade
        For Each cg As CourseGrade In studentQuery.CourseGrade
            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.Course. _
             Where("it.CourseID = @courseID", New ObjectParameter _
                   ("courseID", courseId)).Include("Person") _
                   .FirstOrDefault()

        ' Display each instructor for the specified Course
        For Each Instructor As Instructor In instructorQuery.Person
            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 SchoolEntity.
        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.Department.Include("Course")
                                        .OrderBy("it.Name");

            // Bind the ComboBox control to the query, which is
            // executed during data binding.
        this.departmentList.DisplayMember = "Name";
            this.departmentList.DataSource = departmentQuery
        .Execute(MergeOption.OverwriteChanges);
        }

        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 Course properties of 
            // the selected Department.
            courseList.DisplayMember = "Title";
            courseList.DataSource = department.Course;
        }

        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 CourseGrade types for the supplied CourseID
            Course studentQuery = schoolContext.Course.Where(
                "it.CourseID = @courseID", new ObjectParameter
                    ("courseID", courseId)).Include("CourseGrade").
                    FirstOrDefault();

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

            studentList.Items.Add(" ");

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

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

Next Steps

You have successfully implemented table-per-hierarchy inheritance in an EDM. For more information about how to define an EDM 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 Programming Guide (Entity Framework).

Share via