Walkthrough: Mapping an Entity to Multiple Tables (Entity Data Model Tools)

Article
02/21/2011

This topic shows how to map an entity type to two tables by modifying a conceptual model. You can map an entity to multiple tables when the tables share a common key. The concepts that apply to mapping an entity type to two tables are easily extended to mapping an entity type to more than two tables.

In this walkthrough you will map an entity type to two tables by modifying the conceptual model used in the CourseManager application (for more information, see the Prerequisites section, later in this topic). You will create a new entity type (Instructor, derived from the Person entity type) and map it to the Person and OfficeAssignment tables. The following steps summarize the process (the procedures later in this document provide more detail):

Create a new entity type, Instructor.

Set the base type for Instructor to Person.

Note
You do not have to create an inheritance hierarchy to map an entity type to multiple tables. An inheritance hierarchy is used in this example to make it more realistic (in a school, only an instructor would have an office assignment). It would be possible to map the Person entity type to both the Person and OfficeAssignment tables without creating an Instructor entity type.

Move the HireDate property from Person to Instructor.
Move the Location and Timestamp properties from OfficeAssignment to Instructor.
Map the Instructor entity to the Person table with the condition HireDate Is Not Null. Map the HireDate column to the HireDate property.
Map the Instructor entity to the OfficeAssignment table without a condition. Map the InstructorID column to the PersonID property and the Location column to the Location property.
Delete the OfficeAssignment entity type.

Prerequisites

To complete this walkthrough you must first build the CourseManager application. For more information, see the Entity Framework Quickstart. You will modify the conceptual model used in the CourseManager application by adding a new entity type and mapping it to two tables. You will then extend the application's functionality to display instructors' office assignments.

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 using either Visual C# or Visual Basic.

Mapping an Entity to Two Tables

In this procedure, you will modify the CSDL and mapping content of School.edmx file by creating a new entity type (Instructor) and mapping it to two tables (Person and OfficeAssignment).

To map an entity to two tables

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

The School.edmx file is opened in the Entity Data Model Designer (Entity Designer).
Right-click an empty area 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.
Right-click the HireDate property (under Scalar Properties) of the Person entity type and select Cut.
Right-click Scalar Properties of the Instructor entity type and select Paste.
Right-click the HireDate property and select Properties.

The Properties window appears.
In the Properties window, set the Nullable property to false.
Right-click the Location property of the OfficeAssignment entity type and select Cut.
Right-click Scalar Properties of the Instructor entity type and select Paste.
Right-click the Location property and select Properties.
In the Properties window, set the Nullable property to false.
Repeat steps 10 through 13 for the Timestamp property of the OfficeAssignment type.

Note

The next steps require the Mapping Details window. If you cannot see this window, 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 <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.
In the Operator column of the Mapping Details window, select Is from the drop-down list.
In the Property/Value column of the Mapping Details window, select Not Null.
Click <Add a Table or View> and select OfficeAssignment from the drop-down list.

The Mapping Details window is updated with default column mappings.
Click on the Property/Value field that corresponds to the InstructorID column and select PersonID from the drop-down list.
Right-click the OfficeAssignment entity type and select Delete.

Note
The next steps require the Mapping Details window. If you cannot see this window, right-click the design surface and select Mapping Details.

The Instructor entity type is now mapped to the Person and OfficeAssignment tables.

Constructing the User Interface

Next, you will add a button to the CourseViewer form that loads and displays the CourseAssignmentForm form. Then, you will add a DataGridView control to the form for displaying Instructor office locations. Finally, you will add a button to the CourseAssignmentForm that saves updates to the database.

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 OfficeAssignment.vb or OfficeAssignment.cs, 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 OfficeAssignment and the text is set to OfficeAssignment.
Drag a DataGridView control to the form and set its name to officeGridView.
Click the smart tag of the DataGridView and clear the Enable Adding and Enable Deleting options.
Drag a Button control to the form and set its name property to saveChanges and its text property to Update.
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 viewOffices and set the text of the button to View Offices.
Double click the viewOffices Button.

The code-behind file for the CourseViewer opens.

Add the following code to the viewOffices_click event handler:

Dim officeForm As New OfficeAssignment()
officeForm.Visible = True

OfficeAssignment officeForm = new OfficeAssignment();
officeForm.Visible = true;

The user interface for this form is now complete.

Querying the Conceptual Model

This application displays data by binding a DataGridView control to a query. Information displayed in the DataGridView control can be edited and the changes can be saved to the database. For more information about binding objects to controls, see Binding Objects to Controls (Entity Framework).

To query the conceptual model

With the OfficeAssignment form open in the form designer, double-click the body of the form.

The code-behind file for the OfficeAssignment form opens.
Add the following using (C#) or Imports (Visual Basic) statements to reference the model that you created from the School database and the entity namespace.
```
Imports System.Data.Objects
Imports System.Data.Objects.DataClasses
```
```
using System.Data.Objects;
using System.Data.Objects.DataClasses;
```

Add a property representing the data context to the OfficeAssignment class:

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

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

In the OfficeAssignment_Load event handler, add code to initialize the object context and bind the DataGridView control to a query that returns Instructor information.

' Initialize the ObjectContext.
schoolContext = New SchoolEntities()

' Get Persons of type Instructor.
Dim instructorQuery As ObjectQuery(Of Instructor) = _
    schoolContext.People.OfType(Of Instructor)()

' Bind the query results to the GridView control.
' Display only location and name.
officeGridView.DataSource = instructorQuery _
    .Execute(MergeOption.OverwriteChanges)
officeGridView.Columns("HireDate").Visible = False
officeGridView.Columns("PersonID").Visible = False
officeGridView.Columns("Timestamp").Visible = False
officeGridView.Columns("EnrollmentDate").Visible = False
officeGridView.Columns("StudentGrades").Visible = False
officeGridView.Columns("Courses").Visible = False

schoolContext = new SchoolEntities();

// Get Persons of type Instructor.
ObjectQuery<Instructor> instructorQuery = schoolContext
    .People.OfType<Instructor>();

// Bind the query results to the GridView control.
// Display only location and name.
officeGridView.DataSource = instructorQuery
    .Execute(MergeOption.OverwriteChanges);
officeGridView.Columns["HireDate"].Visible = false;
officeGridView.Columns["Timestamp"].Visible = false;
officeGridView.Columns["PersonID"].Visible = false;
officeGridView.Columns["EnrollmentDate"].Visible = false;
officeGridView.Columns["StudentGrades"].Visible = false;
officeGridView.Columns["Courses"].Visible = false;

Return to the design view of the OfficeAssignment form and double-click the saveChanges Button control.

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

Add code to the event handler to save any changes that were made in the DataGridView control to the database.

' Save object changes to the database, 
' display a message, and refresh the form.
schoolContext.SaveChanges()
MessageBox.Show("Change(s) saved to the database.")
Me.Refresh()

// Save object changes to the database, 
// display a message, and refresh the form.
schoolContext.SaveChanges();
MessageBox.Show("Change(s) saved to the database.");
this.Refresh();

The application is now complete. Press Ctrl+F5 to run the application. Click the View Offices button to load the OfficeAssignments form. Instructor names and office locations are displayed. The information displayed can be edited and you can save the changes to the database by clicking the Update button.

Code Listing

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

Imports System.Data.Objects
Imports System.Data.Objects.DataClasses
Public Class OfficeAssignment
    ' Create an ObjectContext instance based on SchoolEntity.
    Private schoolContext As SchoolEntities

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

        ' Get Persons of type Instructor.
        Dim instructorQuery As ObjectQuery(Of Instructor) = _
            schoolContext.People.OfType(Of Instructor)()

        ' Bind the query results to the GridView control.
        ' Display only location and name.
        officeGridView.DataSource = instructorQuery _
            .Execute(MergeOption.OverwriteChanges)
        officeGridView.Columns("HireDate").Visible = False
        officeGridView.Columns("PersonID").Visible = False
        officeGridView.Columns("Timestamp").Visible = False
        officeGridView.Columns("EnrollmentDate").Visible = False
        officeGridView.Columns("StudentGrades").Visible = False
        officeGridView.Columns("Courses").Visible = False
    End Sub

    Private Sub saveChanges_Click(ByVal sender As System.Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles saveChanges.Click
        ' Save object changes to the database, 
        ' display a message, and refresh the form.
        schoolContext.SaveChanges()
        MessageBox.Show("Change(s) saved to the database.")
        Me.Refresh()
    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 OfficeAssignment : Form
    {
        // Create an ObjectContext instance based on SchoolEntity.
        private SchoolEntities schoolContext;

        public OfficeAssignment()
        {
            InitializeComponent();
        }

        private void OfficeAssignment_Load(object sender, EventArgs e)
        {
            schoolContext = new SchoolEntities();

            // Get Persons of type Instructor.
            ObjectQuery<Instructor> instructorQuery = schoolContext
                .People.OfType<Instructor>();

            // Bind the query results to the GridView control.
            // Display only location and name.
            officeGridView.DataSource = instructorQuery
                .Execute(MergeOption.OverwriteChanges);
            officeGridView.Columns["HireDate"].Visible = false;
            officeGridView.Columns["Timestamp"].Visible = false;
            officeGridView.Columns["PersonID"].Visible = false;
            officeGridView.Columns["EnrollmentDate"].Visible = false;
            officeGridView.Columns["StudentGrades"].Visible = false;
            officeGridView.Columns["Courses"].Visible = false;
        }

        private void saveChanges_Click(object sender, EventArgs e)
        {
            // Save object changes to the database, 
            // display a message, and refresh the form.
            schoolContext.SaveChanges();
            MessageBox.Show("Change(s) saved to the database.");
            this.Refresh();
        }
    }
}

Next Steps

You have successfully mapped an entity to multiple tables. For more information about how to create a model with an entity mapped to multiple tables, see How to: Define a Model with a Single Entity Mapped to Two Tables. For more information about how to build applications that use the Entity Framework, see ADO.NET Entity Framework.