Entity Framework: Model First, Inheritance?

Question

I have a Person table, an Employee table, and a Contractor table. All Employees are people, all Contractors are people and every Person is either an employee or a Contractor. Like so:

How would I be able to accomplish this concept using Model First? Inheritance?

Answer 1

You've got three options:

1 - Table-Per Hierarchy: Good for performance, as one physical table is required. You'll need to add a discriminator field to Person - such as "PersonType". Problem with this approach (what I have found), is you end up will lots of nullable fields, and navigational properties between derived types are difficult (in my experience).

2 - Table-Per Type: Requires separate tables, but good for flexibility if you want to another another "Person" type.

3 - Table-Per Concrete Type: Don't have experience with this, so can't really comment on it. AFAIK it's very similar to TPT.

I'd probably go with TPT, just because it's easier IMO.

Having said that though, the field in the "Contractor" and "Employee" tables are of the same type, so you could generalize this as a single field with TPH. But i'm guessing that's not the complete model.

Steps for Model-First:

1. Add those three entities to a blank EDMX.
2. Mark "Person" as abstract
3. Set "Contractor" and "Employee" as deriving from "Person". (Add -> Inheritance)
4. Remove the ID fields from the "Employee" and "Contractor" entities (not required - it will inherit the ID from "Person").
5. Generate database from model.

Answer 2

That table structure + inheritance == TPT. In the designer it will look something like this:

...and the raw EDMX for the entities and mappings to those tables:

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="2.0" xmlns:edmx="http://schemas.microsoft.com/ado/2008/10/edmx" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns:ssdl="http://schemas.microsoft.com/ado/2009/02/edm/ssdl" xmlns:edm="http://schemas.microsoft.com/ado/2008/09/edm" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" xmlns:map="http://schemas.microsoft.com/ado/2008/09/mapping/cs" xmlns:codegen="http://schemas.microsoft.com/ado/2006/04/codegeneration" xmlns:huagati="http://www.huagati.com/edmxtools/annotations">
  <!--Updated by Huagati EDMX Tools version 2.16.4007.30259 on 2010-12-23 09:13:27-->
  <!-- EF Runtime content -->
  <edmx:Runtime>
    <!-- SSDL content -->
    <edmx:StorageModels>
      <Schema xmlns="http://schemas.microsoft.com/ado/2009/02/edm/ssdl" Namespace="Model1.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2008">
        <EntityContainer Name="Model1TargetContainer">
          <EntitySet Name="Person" store:Type="Tables" Schema="dbo" Table="Person" store:Name="Person" EntityType="Model1.Store.Person" />
          <EntitySet Name="Employee" store:Type="Tables" Schema="dbo" Table="Employee" store:Name="Employee" EntityType="Model1.Store.Employee" />
          <AssociationSet Name="FK_Employee_Person" Association="Model1.Store.FK_Employee_Person">
            <End Role="Person" EntitySet="Person" />
            <End Role="Employee" EntitySet="Employee" />
          </AssociationSet>
          <EntitySet Name="Contractor" store:Type="Tables" Schema="dbo" Table="Contractor" store:Name="Contractor" EntityType="Model1.Store.Contractor" />
          <AssociationSet Name="FK_Contractor_Person" Association="Model1.Store.FK_Contractor_Person">
            <End Role="Person" EntitySet="Person" />
            <End Role="Contractor" EntitySet="Contractor" />
          </AssociationSet>
        </EntityContainer>
        <EntityType Name="Person">
          <Documentation />
          <Key>
            <PropertyRef Name="PersonId" />
          </Key>
          <Property Name="PersonId" Type="bigint" Nullable="false" StoreGeneratedPattern="Identity" />
          <Property Name="Name" Type="nvarchar" Nullable="true" MaxLength="50" />
        </EntityType>
        <EntityType Name="Employee">
          <Documentation />
          <Key>
            <PropertyRef Name="EmployeeId" />
          </Key>
          <Property Name="EmployeeId" Type="bigint" Nullable="false" />
          <Property Name="EmployeeNumber" Type="nvarchar" Nullable="true" MaxLength="50" />
        </EntityType>
        <Association Name="FK_Employee_Person">
          <End Multiplicity="1" Role="Person" Type="Model1.Store.Person" />
          <End Multiplicity="0..1" Role="Employee" Type="Model1.Store.Employee" />
          <ReferentialConstraint>
            <Principal Role="Person">
              <PropertyRef Name="PersonId" />
            </Principal>
            <Dependent Role="Employee">
              <PropertyRef Name="EmployeeId" />
            </Dependent>
          </ReferentialConstraint>
        </Association>
        <EntityType Name="Contractor">
          <Documentation />
          <Key>
            <PropertyRef Name="ContractorId" />
          </Key>
          <Property Name="ContractorId" Type="bigint" Nullable="false" />
          <Property Name="ContractorNumber" Type="nvarchar" Nullable="true" MaxLength="50" />
        </EntityType>
        <Association Name="FK_Contractor_Person">
          <End Multiplicity="1" Role="Person" Type="Model1.Store.Person" />
          <End Multiplicity="0..1" Role="Contractor" Type="Model1.Store.Contractor" />
          <ReferentialConstraint>
            <Principal Role="Person">
              <PropertyRef Name="PersonId" />
            </Principal>
            <Dependent Role="Contractor">
              <PropertyRef Name="ContractorId" />
            </Dependent>
          </ReferentialConstraint>
        </Association>
      </Schema>
    </edmx:StorageModels>
    <!-- CSDL content -->
    <edmx:ConceptualModels>
      <Schema xmlns="http://schemas.microsoft.com/ado/2008/09/edm" xmlns:cg="http://schemas.microsoft.com/ado/2006/04/codegeneration" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" Namespace="Model1" Alias="Self" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation">
        <EntityContainer Name="Model1Container" annotation:LazyLoadingEnabled="true">
          <EntitySet Name="Person" EntityType="Model1.Person" huagati:InheritanceStrategy="TPT" />
        </EntityContainer>
        <EntityType Name="Person">
          <Key>
            <PropertyRef Name="PersonId" />
          </Key>
          <Property Type="Int64" Name="PersonId" Nullable="false" annotation:StoreGeneratedPattern="Identity" />
          <Property Type="String" Name="Name" Unicode="true" MaxLength="50" Nullable="true" />
        </EntityType>
        <EntityType Name="Employee" BaseType="Model1.Person">
          <Property Type="String" Name="EmployeeNumber" Unicode="true" MaxLength="50" Nullable="true" />
        </EntityType>
        <EntityType Name="Contractor" BaseType="Model1.Person">
          <Property Type="String" Name="ContractorNumber" Nullable="true" Unicode="true" MaxLength="50" />
        </EntityType>
      </Schema>
    </edmx:ConceptualModels>
    <!-- C-S mapping content -->
    <edmx:Mappings>
      <Mapping xmlns="http://schemas.microsoft.com/ado/2008/09/mapping/cs" Space="C-S">
        <Alias Key="Model" Value="Model1" />
        <Alias Key="Target" Value="Model1.Store" />
        <EntityContainerMapping CdmEntityContainer="Model1Container" StorageEntityContainer="Model1TargetContainer">
          <EntitySetMapping Name="Person">
            <EntityTypeMapping TypeName="IsTypeOf(Model1.Person)">
              <MappingFragment StoreEntitySet="Person">
                <ScalarProperty Name="PersonId" ColumnName="PersonId" />
                <ScalarProperty Name="Name" ColumnName="Name" />
              </MappingFragment>
            </EntityTypeMapping>
            <EntityTypeMapping TypeName="IsTypeOf(Model1.Employee)">
              <MappingFragment StoreEntitySet="Employee">
                <ScalarProperty Name="PersonId" ColumnName="EmployeeId" />
                <ScalarProperty Name="EmployeeNumber" ColumnName="EmployeeNumber" />
              </MappingFragment>
            </EntityTypeMapping>
            <EntityTypeMapping TypeName="IsTypeOf(Model1.Contractor)">
              <MappingFragment StoreEntitySet="Contractor">
                <ScalarProperty Name="PersonId" ColumnName="ContractorId" />
                <ScalarProperty Name="ContractorNumber" ColumnName="ContractorNumber" />
              </MappingFragment>
            </EntityTypeMapping>
          </EntitySetMapping>
        </EntityContainerMapping>
      </Mapping>
    </edmx:Mappings>
  </edmx:Runtime>
  <!-- EF Designer content (DO NOT EDIT MANUALLY BELOW HERE) -->
  <edmx:Designer xmlns="http://schemas.microsoft.com/ado/2008/10/edmx">
    <edmx:Connection>
      <DesignerInfoPropertySet>
        <DesignerProperty Name="MetadataArtifactProcessing" Value="EmbedInOutputAssembly" />
      </DesignerInfoPropertySet>
    </edmx:Connection>
    <edmx:Options>
      <DesignerInfoPropertySet>
        <DesignerProperty Name="ValidateOnBuild" Value="true" />
        <DesignerProperty Name="EnablePluralization" Value="True" />
      </DesignerInfoPropertySet>
    </edmx:Options>
    <!-- Diagram content (shape and connector positions) -->
    <edmx:Diagrams>
      <Diagram Name="Model1">
        <EntityTypeShape EntityType="Model1.Person" Width="1.5" PointX="5.125" PointY="2.125" Height="1.4033821614583331" />
        <EntityTypeShape EntityType="Model1.Employee" Width="1.5" PointX="3.875" PointY="4" Height="1.2110807291666665" />
        <InheritanceConnector EntityType="Model1.Employee">
          <ConnectorPoint PointX="5.875" PointY="3.5283821614583331" />
          <ConnectorPoint PointX="5.875" PointY="3.76" />
          <ConnectorPoint PointX="4.625" PointY="3.76" />
          <ConnectorPoint PointX="4.625" PointY="4" />
        </InheritanceConnector>
        <EntityTypeShape EntityType="Model1.Contractor" Width="2" PointX="5.875" PointY="4" Height="1.2110807291666665" />
        <InheritanceConnector EntityType="Model1.Contractor" ManuallyRouted="false">
          <ConnectorPoint PointX="5.875" PointY="3.5283821614583331" />
          <ConnectorPoint PointX="5.875" PointY="3.76" />
          <ConnectorPoint PointX="6.875" PointY="3.76" />
          <ConnectorPoint PointX="6.875" PointY="4" />
        </InheritanceConnector>
      </Diagram>
    </edmx:Diagrams>
  </edmx:Designer>
</edmx:Edmx>

For a more detailed description of the different inheritance types and how they relate to physical db tables, see:
http://huagati.blogspot.com/2010/10/mixing-inheritance-strategies-in-entity.html
...and...
http://blogs.msdn.com/b/adonet/archive/2010/10/25/inheritance-mapping-a-walkthrough-guide-for-beginners.aspx