Entity Framework: first model, inheritance?

Question

I have a Person table, an Employee table and a Contractor table. All employees are people, all Contractors are people, and each Person is either an employee or a Contractor. For example:

How can I fulfill this concept using the First model? Inheritance?

Answer 1

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

... and raw EDMX for entities and mappings with these 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> <!-- CS mapping content --> <edmx:Mappings> <Mapping xmlns="http://schemas.microsoft.com/ado/2008/09/mapping/cs" Space="CS"> <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 various types of inheritance 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

Answer 2

You have three options:

1 - Table behind the hierarchy : Good performance, as one physical table is required. You need to add the discriminator field to Person - for example, "PersonType". The problem with this approach (what I found) is that you will end up with a lot of fields with a null value, and navigational properties between derived types are complex (in my experience).

2 - Type of table : Requires separate tables, but suitable for flexibility if you want a different type of "Person".

3 - Table-Per Concrete Type . You have no experience with this, so he cannot comment on it. AFAIK is very similar to TPT.

I would probably go with TPT, simply because it makes IMO easier.

Having said that, the field in the Contractor and Employee tables is of the same type, so you can generalize this as one field with TPH. But I guess that is not a complete model.

Steps for the first model:

• Add these three objects to an empty EDMX.
• Mark "Face" as abstract
• Set Contractor and Employee to Face. (Add → Inheritance)
• Remove the identifier fields from the "Employee" and "Contractor" objects (optional - it inherits the identifier from "Person").
• Generate a database from the model.