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

  • Article

When using legacy data in the Entity Data Model (EDM), it is sometimes useful to be able to map a single entity to two tables in the database. This can be done when two tables share a common key, as is the case with the Customer and Store tables of the AdventureWorks sample database that ships with SQL Server 2005.

Create an EDM that uses the Customer and Store tables of the AdventureWorks database and modify the *.edmx file as shown in the following sections. Use the code demonstrated in the topic: How to: Create and Execute Object Queries using an Entity Mapped to Separate Tables to test this data model.

To implement the conceptual schema definition language (CSDL) requirements

  1. Locate the <edmx:ConceptualModels> section of the *.edmx file.

  2. Remove the <EntityType> tags representing the Customer entity.

  3. Remove the <Key> tags, the <PropertyRef> tags, and the name and identifier StoreID of the now deleted Customer entity.

  4. Move the properties of the deleted Customer entity inside the <EntityType> tags for the Store entity.

  5. Rename the rowguid property of the now deleted Customer entity Cust_rowguid.

  6. Rename the ModifiedDate property of the now deleted Customer entity Cust_ModifiedDate.

  7. The conceptual schema is shown.

   <edmx:ConceptualModels>
      <Schema Namespace="AdventureWorksModel" Alias="Self"
            xmlns="https://schemas.microsoft.com/ado/2006/04/edm">
        <EntityContainer Name="AdventureWorksEntities">
          
          <EntitySet Name="Store"
              EntityType="AdventureWorksModel.Store" />
        </EntityContainer>
        
        <EntityType Name="Store">
          <Key>
            <PropertyRef Name="CustomerID" />
          </Key>
          <Property Name="CustomerID" Type="Int32" Nullable="false" />
          <Property Name="Name" Type="String" 
                  Nullable="false" MaxLength="50" />
          <Property Name="SalesPersonID" Type="Int32" />
          <Property Name="Demographics" Type="String"
                     MaxLength="1073741823" />
          <Property Name="rowguid" Type="Guid" Nullable="false" />
          <Property Name="ModifiedDate" Type="DateTime"
                     Nullable="false" />
          
          <Property Name="TerritoryID" Type="Int32" />
          <Property Name="AccountNumber" Type="String" 
                  Nullable="false" 
                    MaxLength="10" Unicode="false" />
          <Property Name="CustomerType" Type="String" 
                  Nullable="false" 
                    MaxLength="1" FixedLength="true" />
          <Property Name="Cust_rowguid" Type="Guid" 
                  Nullable="false" />
          <Property Name="Cust_ModifiedDate" Type="DateTime"
                  Nullable="false" />
        </EntityType>
        
      </Schema>
    </edmx:ConceptualModels>

To implement the store schema definition language (SSDL) requirements

  1. Locate the <edmx:StorageModels> section of the *.edmx file.

  2. Leave the SSDL schema unchanged, as shown:

    <edmx:StorageModels>
      <Schema Namespace="AdventureWorksModel.Store" Alias="Self"
          Provider="System.Data.SqlClient"
          ProviderManifestToken="2005"
          xmlns="https://schemas.microsoft.com/ado/2006/04/edm/ssdl">
        <EntityContainer Name="Sales">
          <EntitySet Name="Customer"
                EntityType="AdventureWorksModel.Store.Customer" />
          <EntitySet Name="Store"
              EntityType="AdventureWorksModel.Store.Store" />
          <AssociationSet Name="FK_Store_Customer_CustomerID"
  Association="AdventureWorksModel.Store.FK_Store_Customer_CustomerID">
            <End Role="Customer" EntitySet="Customer" />
            <End Role="Store" EntitySet="Store" />
          </AssociationSet>
        </EntityContainer>
        <EntityType Name="Customer">
          <Key>
            <PropertyRef Name="CustomerID" />
          </Key>
          <Property Name="CustomerID" Type="int" 
                Nullable="false" StoreGeneratedPattern="Identity" />
          <Property Name="TerritoryID" Type="int" />
          <Property Name="AccountNumber" Type="varchar"
                           Nullable="false" MaxLength="10" />
          <Property Name="CustomerType" Type="nchar" 
                Nullable="false" MaxLength="1" />
          <Property Name="rowguid" 
                    Type="uniqueidentifier" Nullable="false" />
          <Property Name="ModifiedDate" Type="datetime"
                Nullable="false" />
        </EntityType>
        <EntityType Name="Store">
          <Key>
            <PropertyRef Name="CustomerID" />
          </Key>
          <Property Name="CustomerID" Type="int" Nullable="false" />
          <Property Name="Name" Type="nvarchar" 
                Nullable="false" MaxLength="50" />
          <Property Name="SalesPersonID" Type="int" />
          <Property Name="Demographics" Type="xml" />
          <Property Name="rowguid" Type="uniqueidentifier"
                Nullable="false" />
          <Property Name="ModifiedDate" Type="datetime"
                         Nullable="false" />
        </EntityType>
        <Association Name="FK_Store_Customer_CustomerID">
          <End Role="Customer"
        Type="AdventureWorksModel.Store.Customer" Multiplicity="1" />
          <End Role="Store" Type="AdventureWorksModel.Store.Store"
                Multiplicity="0..1" />
          <ReferentialConstraint>
            <Principal Role="Customer">
              <PropertyRef Name="CustomerID" />
            </Principal>
            <Dependent Role="Store">
              <PropertyRef Name="CustomerID" />
            </Dependent>
          </ReferentialConstraint>
        </Association>
      </Schema>
    </edmx:StorageModels>

To implement the mapping specification language (MSL) requirements

  1. Locate the <edmx:Mappings> section of the *.edmx file.

  2. Remove the <EntitySetMapping> tags for the Customer entity set from the mapping specification.

  3. Remove the <EntityTypeMapping> tags for the Customer entity type from the mapping specification.

  4. Move the <MappingFragment> for the Customer StoreEntitySet inside the EntityType mapping for the StoreEntitySet.

  5. The mapping specification is shown.

    <edmx:Mappings>
      <Mapping Space="C-S"
          xmlns="urn:schemas-microsoft-com:windows:storage:mapping:CS">
        <EntityContainerMapping StorageEntityContainer="Sales"
          CdmEntityContainer="AdventureWorksEntities">
          
          <EntitySetMapping Name="Store">
            <EntityTypeMapping
                 TypeName="IsTypeOf(AdventureWorksModel.Store)">
              <MappingFragment StoreEntitySet="Store">
                <ScalarProperty Name="CustomerID"
                           ColumnName="CustomerID" />
                <ScalarProperty Name="Name" ColumnName="Name" />
                <ScalarProperty Name="SalesPersonID"
                           ColumnName="SalesPersonID" />
                <ScalarProperty Name="Demographics"
                           ColumnName="Demographics" />
                <ScalarProperty Name="rowguid"
                           ColumnName="rowguid" />
                <ScalarProperty Name="ModifiedDate"
                           ColumnName="ModifiedDate" />
              </MappingFragment>
              <MappingFragment StoreEntitySet="Customer">
                <ScalarProperty Name="CustomerID"
                           ColumnName="CustomerID" />
                <ScalarProperty Name="TerritoryID"
                           ColumnName="TerritoryID" />
                <ScalarProperty Name="AccountNumber"
                           ColumnName="AccountNumber" />
                <ScalarProperty Name="CustomerType"
                           ColumnName="CustomerType" />
                <ScalarProperty Name="Cust_rowguid"
                           ColumnName="rowguid" />
                <ScalarProperty Name="Cust_ModifiedDate"
                           ColumnName="ModifiedDate" />
              </MappingFragment>
            </EntityTypeMapping>
          </EntitySetMapping>
          
        </EntityContainerMapping>
      </Mapping>
    </edmx:Mappings>

See Also

Tasks

How to: Create and Execute Object Queries using an Entity Mapped to Separate Tables