How to: Define a Model with Table-per-Hierarchy Inheritance

Article
08/29/2011

This topic describes how to manually create a conceptual model that has table-per-hierarchy inheritance. Table-per-hierarchy inheritance uses one database table to maintain data for all of the entity types in an inheritance hierarchy.

Note

The recommended way to define a model with table-per-type inheritance is by using the ADO.NET Entity Data Model Tools. For more information, see Walkthrough: Mapping Inheritance - Table-per-Hierarchy.

The basic steps for manually defining a model with table-per-hierarchy inheritance are as follows:

Define one entity set in the conceptual model that will contain the base entity type and the derived types. For more information, see EntitySet Element (CSDL).
Define derived entity types in the conceptual model by using the BaseType attribute and define only non-inherited properties on the derived types. For more information, see EntityType Element (CSDL).
Choose a column from the underlying database table whose value will be used to discriminate between the base type and derived types. For example, if an Employee table has an integer-valued EmployeeType column, the value of this column could be used to discriminate when an Employee base entity type is one of the following derived entity types: HourlyEmployee or SalariedEmployee. For more information, see the following example.

The discriminator column is mapped as part of a condition, so there can be no corresponding property on any entity types in the hierarchy. The exception to this rule is when the condition uses an Is Null or Is Not Null comparison. In this case, the discriminator column can have a corresponding property on an entity type.

If the discriminator column can have more than two values (an integer type, for example), the column must be nullable or have a default value. This ensures that when a new type is created and saved to the database, the column can be null or has some value.

Conditions must be used to map each derived type in the hierarchy, and conditions may be used to map the base type. If the base type is abstract, no mapping or condition is allowed.
Map the base entity type and derived types in the same EntitySetMapping element in the mapping specification language (MSL). Map inherited properties to table columns where appropriate. Use the IsTypeOf syntax when setting the value of the TypeName attribute on derived types. Use a mapping condition to discriminate between types in the hierarchy. For more information, see EntitySetMapping Element (MSL) and Condition 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.

To create the storage model

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

<?xml version="1.0" encoding="utf-8" ?>
<Schema Namespace="AdventureWorksModel.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="AdventureWorksModelStoreContainer">
    <EntitySet Name="Product" EntityType="AdventureWorksModel.Store.Product"
               store:Type="Tables" Schema="Production" />
  </EntityContainer>
  <EntityType Name="Product">
    <Key>
      <PropertyRef Name="ProductID" />
    </Key>
    <Property Name="ProductID" Type="int" Nullable="false"
              StoreGeneratedPattern="Identity" />
    <Property Name="Name" Type="nvarchar" Nullable="false" MaxLength="50" />
    <Property Name="ProductNumber" Type="nvarchar" Nullable="false" MaxLength="25" />
    <Property Name="MakeFlag" Type="bit" Nullable="false" />
    <Property Name="FinishedGoodsFlag" Type="bit" Nullable="false" />
    <Property Name="Color" Type="nvarchar" MaxLength="15" />
    <Property Name="SafetyStockLevel" Type="smallint" Nullable="false" />
    <Property Name="ReorderPoint" Type="smallint" Nullable="false" />
    <Property Name="StandardCost" Type="money" Nullable="false" />
    <Property Name="ListPrice" Type="money" Nullable="false" />
    <Property Name="Size" Type="nvarchar" MaxLength="5" />
    <Property Name="SizeUnitMeasureCode" Type="nchar" MaxLength="3" />
    <Property Name="WeightUnitMeasureCode" Type="nchar" MaxLength="3" />
    <Property Name="Weight" Type="decimal" Precision="8" Scale="2" />
    <Property Name="DaysToManufacture" Type="int" Nullable="false" />
    <Property Name="ProductLine" Type="nchar" MaxLength="2" />
    <Property Name="Class" Type="nchar" MaxLength="2" />
    <Property Name="Style" Type="nchar" MaxLength="2" />
    <Property Name="ProductSubcategoryID" Type="int" />
    <Property Name="ProductModelID" Type="int" />
    <Property Name="SellStartDate" Type="datetime" Nullable="false" />
    <Property Name="SellEndDate" Type="datetime" />
    <Property Name="DiscontinuedDate" Type="datetime" />
    <Property Name="rowguid" Type="uniqueidentifier" Nullable="false" />
    <Property Name="ModifiedDate" Type="datetime" Nullable="false" />
  </EntityType>
</Schema>

To create the conceptual model

Add the following XML file to your project and name it AdventureWorks.csdl. Note the following:
- Only one entity set, Products, is defined for the two entity types Product and DiscontinuedProduct.
- The DiscontinuedProduct entity type is a derived type as indicated by the BaseType attribute in its definition.
- The properties defined on the DiscontinuedProduct entity type are only non-inherited properties.
- The MakeFlag column in the storage model (see the AdventureWorks.ssdl file above) does not appear as a property on the base type or the derived type. The value of this column will be used to discriminate between the types in the hierarchy as is mapped as part of a condition (see the AdventureWorks.msl file below).
Note

If the column is used in an Is Null or Is Not Null condition, then the corresponding property can appear on an entity type.

<?xml version="1.0" encoding="utf-8" ?>
<Schema Namespace="AdventureWorksModel" Alias="Self"
              xmlns:annotation="https://schemas.microsoft.com/ado/2009/02/edm/annotation"
              xmlns="https://schemas.microsoft.com/ado/2008/09/edm">
  <EntityContainer Name="AdventureWorksEntities" annotation:LazyLoadingEnabled="true">
    <EntitySet Name="Products" EntityType="AdventureWorksModel.Product" />
  </EntityContainer>
  <EntityType Name="Product">
    <Key>
      <PropertyRef Name="ProductID" />
    </Key>
    <Property Type="Int32" Name="ProductID" Nullable="false" />
    <Property Type="String" Name="Name" Nullable="false" MaxLength="50"
              FixedLength="false" Unicode="true" />
    <Property Type="String" Name="ProductNumber" Nullable="false" MaxLength="25"
              FixedLength="false" Unicode="true" />
    <Property Type="Boolean" Name="FinishedGoodsFlag" Nullable="false" />
    <Property Type="String" Name="Color" MaxLength="15" FixedLength="false"
              Unicode="true" />
    <Property Type="Int16" Name="SafetyStockLevel" Nullable="false" />
    <Property Type="Int16" Name="ReorderPoint" Nullable="false" />
    <Property Type="Decimal" Name="StandardCost" Nullable="false"
              Precision="19" Scale="4" />
    <Property Type="Decimal" Name="ListPrice" Nullable="false"
              Precision="19" Scale="4" />
    <Property Type="String" Name="Size" MaxLength="5" FixedLength="false"
              Unicode="true" />
    <Property Type="String" Name="SizeUnitMeasureCode" MaxLength="3"
              FixedLength="true" Unicode="true" />
    <Property Type="String" Name="WeightUnitMeasureCode" MaxLength="3"
              FixedLength="true" Unicode="true" />
    <Property Type="Decimal" Name="Weight" Precision="8" Scale="2" />
    <Property Type="Int32" Name="DaysToManufacture" Nullable="false" />
    <Property Type="String" Name="ProductLine" MaxLength="2"
              FixedLength="true" Unicode="true" />
    <Property Type="String" Name="Class" MaxLength="2" FixedLength="true"
              Unicode="true" />
    <Property Type="String" Name="Style" MaxLength="2" FixedLength="true"
              Unicode="true" />
    <Property Type="Int32" Name="ProductSubcategoryID" />
    <Property Type="Int32" Name="ProductModelID" />
    <Property Type="DateTime" Name="SellStartDate" Nullable="false" />
    <Property Type="DateTime" Name="SellEndDate" />
    <Property Type="Guid" Name="rowguid" Nullable="false" />
    <Property Type="DateTime" Name="ModifiedDate" Nullable="false" />
  </EntityType>
  <EntityType Name="DiscontinuedProduct" BaseType="AdventureWorksModel.Product" >
    <Property Type="DateTime" Name="DiscontinuedDate" />
  </EntityType>
</Schema>

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

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

The mapping for the Product and DiscontinuedProduct entity types are defined in the same EntitySetMapping element.
The inherited properties for the DiscontinuedProduct are mapped to the corresponding columns in the underlying database table.
The IsTypeOf syntax is used to indicate the type of the derived DiscontinuedProduct type.
The discriminator column, MakeFlag, is mapped in a Condition element for each entity type in the hierarchy.

<?xml version="1.0" encoding="utf-8" ?>
<Mapping Space="C-S" xmlns="https://schemas.microsoft.com/ado/2008/09/mapping/cs">
  <EntityContainerMapping StorageEntityContainer="AdventureWorksModelStoreContainer"
                          CdmEntityContainer="AdventureWorksEntities">
    <EntitySetMapping Name="Products">
      <EntityTypeMapping TypeName="AdventureWorksModel.Product">
        <MappingFragment StoreEntitySet="Product">
          <ScalarProperty Name="ProductID" ColumnName="ProductID" />
          <ScalarProperty Name="ModifiedDate" ColumnName="ModifiedDate" />
          <ScalarProperty Name="rowguid" ColumnName="rowguid" />
          <ScalarProperty Name="SellEndDate" ColumnName="SellEndDate" />
          <ScalarProperty Name="SellStartDate" ColumnName="SellStartDate" />
          <ScalarProperty Name="ProductModelID" ColumnName="ProductModelID" />
          <ScalarProperty Name="ProductSubcategoryID" ColumnName="ProductSubcategoryID" />
          <ScalarProperty Name="Style" ColumnName="Style" />
          <ScalarProperty Name="Class" ColumnName="Class" />
          <ScalarProperty Name="ProductLine" ColumnName="ProductLine" />
          <ScalarProperty Name="DaysToManufacture" ColumnName="DaysToManufacture" />
          <ScalarProperty Name="Weight" ColumnName="Weight" />
          <ScalarProperty Name="WeightUnitMeasureCode" ColumnName="WeightUnitMeasureCode" />
          <ScalarProperty Name="SizeUnitMeasureCode" ColumnName="SizeUnitMeasureCode" />
          <ScalarProperty Name="Size" ColumnName="Size" />
          <ScalarProperty Name="ListPrice" ColumnName="ListPrice" />
          <ScalarProperty Name="StandardCost" ColumnName="StandardCost" />
          <ScalarProperty Name="ReorderPoint" ColumnName="ReorderPoint" />
          <ScalarProperty Name="SafetyStockLevel" ColumnName="SafetyStockLevel" />
          <ScalarProperty Name="Color" ColumnName="Color" />
          <ScalarProperty Name="FinishedGoodsFlag" ColumnName="FinishedGoodsFlag" />
          <ScalarProperty Name="ProductNumber" ColumnName="ProductNumber" />
          <ScalarProperty Name="Name" ColumnName="Name" />
          <Condition ColumnName="MakeFlag" Value="0" />
        </MappingFragment>
      </EntityTypeMapping>
      <EntityTypeMapping TypeName="IsTypeOf(AdventureWorksModel.DiscontinuedProduct)">
        <MappingFragment StoreEntitySet="Product">
          <ScalarProperty Name="ModifiedDate" ColumnName="ModifiedDate" />
          <ScalarProperty Name="rowguid" ColumnName="rowguid" />
          <ScalarProperty Name="SellEndDate" ColumnName="SellEndDate" />
          <ScalarProperty Name="SellStartDate" ColumnName="SellStartDate" />
          <ScalarProperty Name="ProductModelID" ColumnName="ProductModelID" />
          <ScalarProperty Name="ProductSubcategoryID" ColumnName="ProductSubcategoryID" />
          <ScalarProperty Name="Style" ColumnName="Style" />
          <ScalarProperty Name="Class" ColumnName="Class" />
          <ScalarProperty Name="ProductLine" ColumnName="ProductLine" />
          <ScalarProperty Name="DaysToManufacture" ColumnName="DaysToManufacture" />
          <ScalarProperty Name="Weight" ColumnName="Weight" />
          <ScalarProperty Name="WeightUnitMeasureCode" ColumnName="WeightUnitMeasureCode" />
          <ScalarProperty Name="SizeUnitMeasureCode" ColumnName="SizeUnitMeasureCode" />
          <ScalarProperty Name="Size" ColumnName="Size" />
          <ScalarProperty Name="ListPrice" ColumnName="ListPrice" />
          <ScalarProperty Name="StandardCost" ColumnName="StandardCost" />
          <ScalarProperty Name="ReorderPoint" ColumnName="ReorderPoint" />
          <ScalarProperty Name="SafetyStockLevel" ColumnName="SafetyStockLevel" />
          <ScalarProperty Name="Color" ColumnName="Color" />
          <ScalarProperty Name="FinishedGoodsFlag" ColumnName="FinishedGoodsFlag" />
          <ScalarProperty Name="ProductNumber" ColumnName="ProductNumber" />
          <ScalarProperty Name="Name" ColumnName="Name" />
          <ScalarProperty Name="ProductID" ColumnName="ProductID" />
          <ScalarProperty Name="DiscontinuedDate" ColumnName="DiscontinuedDate" />
          <Condition ColumnName="MakeFlag" Value="1" />
        </MappingFragment>
      </EntityTypeMapping>
    </EntitySetMapping>
  </EntityContainerMapping>
</Mapping>

How to: Define a Model with Table-per-Hierarchy Inheritance

To create the storage model

To create the conceptual model

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

See Also

Tasks

Other Resources

Additional resources