How to: Define a Model with a Single Entity Mapped to Two Tables

This topic describes how to manually create a conceptual model in which an entity type is mapped to two tables in the underlying database. You can use these same concepts to map an entity type to more than two tables.

Note

The recommended way to define a model with an entity type mapped to multiple tables is by using the ADO.NET Entity Data Model Tools. For more information, see Walkthrough: Mapping an Entity to Multiple Tables.

You should only map an entity type to multiple tables if the following conditions are true:

• The tables to which you are mapping share a common key.

• The entity type that is being mapped has entries in each underlying table. In other words, the entity type represents data that has a one-to-one correspondence between the two tables; the entity type represents an inner join of the two tables.

The basic steps for manually defining a model with an entity mapped to two tables are as follows:

1. Define an entity type with properties that correspond to columns in each of the underlying tables. For more information, see EntityType Element (CSDL).

2. Define the mapping for the entity type by using a MappingFragment element for each underlying table. For more information, see EntityTypeMapping Element (MSL) and MappingFragment Element (MSL).

The following example assumes that you have installed the School sample database and that you have manually configured your project to use the Entity Framework. For more information, see Creating the School Sample Database and Configuring the Entity Framework.

Note

The example below will map an entity type to the Person and OfficeAssignement tables in the School sample database. Note that the conditions for mapping an entity type to these tables are met. Namely, an inner join of the two tables will result in all people that have an office assignment.

To create the storage model

1. Add the following XML file to your project and name it School.ssdl.

   <Schema Namespace="SchoolModel.Store" Alias="Self" Provider="System.Data.SqlClient" 
           ProviderManifestToken="2008" 
           xmlns:store="https://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" 
           xmlns="https://schemas.microsoft.com/ado/2009/02/edm/ssdl">
     <EntityContainer Name="SchoolModelStoreContainer">
       <EntitySet Name="OfficeAssignment" EntityType="SchoolModel.Store.OfficeAssignment" 
                  store:Type="Tables" Schema="dbo" />
       <EntitySet Name="Person" EntityType="SchoolModel.Store.Person" 
                  store:Type="Tables" Schema="dbo" />
     </EntityContainer>
     <EntityType Name="OfficeAssignment">
       <Key>
         <PropertyRef Name="InstructorID" />
       </Key>
       <Property Name="InstructorID" Type="int" Nullable="false" />
       <Property Name="Location" Type="nvarchar" Nullable="false" MaxLength="50" />
       <Property Name="Timestamp" Type="timestamp" Nullable="false" 
                 StoreGeneratedPattern="Computed" />
     </EntityType>
     <EntityType Name="Person">
       <Key>
         <PropertyRef Name="PersonID" />
       </Key>
       <Property Name="PersonID" Type="int" Nullable="false" 
                 StoreGeneratedPattern="Identity" />
       <Property Name="LastName" Type="nvarchar" Nullable="false" MaxLength="50" />
       <Property Name="FirstName" Type="nvarchar" Nullable="false" MaxLength="50" />
       <Property Name="HireDate" Type="datetime" />
       <Property Name="EnrollmentDate" Type="datetime" />
     </EntityType>
   </Schema>
   

To create the conceptual model

1. Add the following XML file to your project and name it School.csdl. Note the following:

   • The Instructor entity type has properties that map to all columns if the Person and OfficeAssignment tables (see the storage model above for details about the tables).

     Note:
     Because the Instructor entity type is an inherited type, it inherits all the properties from its base type, Person. Together with the properties that are unique to the Instructor type, the Instructor entity type as properties that map to all columns of the underlying tables.

   <Schema Namespace="SchoolModel" Alias="Self" 
           xmlns:annotation="https://schemas.microsoft.com/ado/2009/02/edm/annotation" 
           xmlns="https://schemas.microsoft.com/ado/2008/09/edm">
     <EntityContainer Name="SchoolEntities" annotation:LazyLoadingEnabled="true">
       <EntitySet Name="People" EntityType="SchoolModel.Person" />
     </EntityContainer>
     <EntityType Name="Person">
       <Key>
         <PropertyRef Name="PersonID" />
       </Key>
       <Property Type="Int32" Name="PersonID" Nullable="false" 
                 annotation:StoreGeneratedPattern="Identity" />
       <Property Type="String" Name="LastName" Nullable="false" 
                 MaxLength="50" FixedLength="false" Unicode="true" />
       <Property Type="String" Name="FirstName" Nullable="false" 
                 MaxLength="50" FixedLength="false" Unicode="true" />
       <Property Type="DateTime" Name="EnrollmentDate" />
     </EntityType>
     <EntityType Name="Instructor" BaseType="SchoolModel.Person" >
       <Property Type="DateTime" Name="HireDate" Nullable="false" />
       <Property Type="Binary" Name="Timestamp" Nullable="false" 
                 MaxLength="8" FixedLength="true" 
                 annotation:StoreGeneratedPattern="Computed" />
       <Property Type="String" Name="Location" Nullable="false" 
                 MaxLength="50" FixedLength="false" Unicode="true" />
     </EntityType>
   </Schema>
   

To define the mapping between the conceptual model and the storage model

1. Add the following XML file to your project and name it School.msl. Note the following:

   • Within the EntityTypeMapping element for the Instructor entity type, separate MappingFragment elements are used to map properties to the appropriate tables.

   <Mapping Space="C-S" xmlns="https://schemas.microsoft.com/ado/2008/09/mapping/cs">
       <EntityContainerMapping StorageEntityContainer="SchoolModelStoreContainer" 
                               CdmEntityContainer="SchoolEntities">
         <EntitySetMapping Name="People">
           <EntityTypeMapping TypeName="IsTypeOf(SchoolModel.Instructor)">
             <MappingFragment StoreEntitySet="Person">
               <ScalarProperty Name="PersonID" ColumnName="PersonID" />
               <ScalarProperty Name="HireDate" ColumnName="HireDate" />
               <Condition ColumnName="HireDate" IsNull="false" />
             </MappingFragment>
             <MappingFragment StoreEntitySet="OfficeAssignment">
               <ScalarProperty Name="PersonID" ColumnName="InstructorID" />
               <ScalarProperty Name="Timestamp" ColumnName="Timestamp" />
               <ScalarProperty Name="Location" ColumnName="Location" />
             </MappingFragment>
           </EntityTypeMapping>
           <EntityTypeMapping TypeName="IsTypeOf(SchoolModel.Person)">
             <MappingFragment StoreEntitySet="Person">
               <ScalarProperty Name="PersonID" ColumnName="PersonID" />
               <ScalarProperty Name="LastName" ColumnName="LastName" />
               <ScalarProperty Name="FirstName" ColumnName="FirstName" />
               <ScalarProperty Name="EnrollmentDate" ColumnName="EnrollmentDate" />
             </MappingFragment>
           </EntityTypeMapping>
         </EntitySetMapping>
       </EntityContainerMapping>
   </Mapping>
   

See Also

Other Resources

CSDL, SSDL, and MSL Specifications
Defining Advanced Data Models