ADO EF - Errors Mapping Associations between Derived Types in TPH

Question

Background

I am writing a data access library using the ADO Entity Framework in Visual Studio 2008 SP1 using the .NET Framework 3.5 SP1. I am trying to create associations between two entities that are both derived from an abstract type. I am representing both entity inheritance hierarchies using Table Per Hierarchy (TPH) which means there are just two tables - one for each entity inheritance hierarchy.

NOTE You can use Table Per Type (TPT) to avoid this problem, but it comes with it's own drawbacks. See here and here for more details when choosing between inheritance persistence models.

Here is a screenshot of the Designer view of the Entity Model:

And here is a screenshot of the database schema:

Assumptions

When you create associations in the ADO Entity Framework Designer between derived types modeled with TPH using Visual Studio 2008 SP1 and the .NET Framework 3.5 SP1 you are likely to receive the following an "Error 3034: Two entities with different keys are mapped to the same row. Ensure these two mapping fragments do no map two groups of entities with overlapping keys to the same group of rows."

Based on what I have read online, in order to resolve this issue, you must add a Condition to the association on the Foreign Key like so:

<Condition ColumnName="Endpoint1" IsNull="false" />

Here's a screenshot of this edit for the PersonPersonToPerson1 association:

Constraints

• The base classes of each hierarchy (i.e. Node and Link) must be abstract.
• The navigation properties from the associations between two derived types must be distinguishable by link type (e.g. PersonToPerson and PersonToLocation). This means you cannot create the associations between the Link and Node abstract base classes.

Problem:

When I create the Entity Model as described above and add the Conditions to the AssociationMappings as described in the Assumptions above, I receive an "Error 3023" when I build/validate the model.

Error   1   Error 3023: Problem in Mapping Fragments starting at lines 146, 153, 159, 186, 195, 204, 213: Column Link.Endpoint1 has no default value and is not nullable. A column value is required to store entity data.
An Entity with Key (PK) will not round-trip when:
((PK is NOT in 'LinkSet' EntitySet OR PK does NOT play Role 'PersonToPerson' in AssociationSet 'PersonPersonToPerson1') AND (PK is in 'LinkSet' EntitySet OR PK plays Role 'PersonToPerson' in AssociationSet 'PersonPersonToPerson1' OR PK plays Role 'PersonToPerson' in AssociationSet 'PersonPersonToPerson'))
C:\Documents and Settings\Demo\My Documents\Visual Studio 2008\Projects\GraphExample2.BusinessEntities\GraphExample2.BusinessEntities\GraphModel.edmx   147 15  GraphExample2.BusinessEntities

The thing that the Entity Framework is getting hung up on in the above scenario is that there are two properties being mapped on to the same foreign keys. For example, the column and foreign key for Endpoint1 is mapped to the Person property in the PersonToLocation derived type and it's mapped to the Leader property in the PersonToPerson derived type.

I don't understand why this is an issue. Since the Leader/Follower Properties are only in the PersonToPerson derived type - not any other derived type or base type - and the same is true of the Person/Location property, why isn't the TypeDiscriminator field sufficient for the EF to figure out which set a given row belongs in?

To me it seems like, if you are dealing with an object where TypeDiscriminator = 1, you place Endpoint1 in Leader and Endpoint2 in Follower. Likewise, if you are dealing with an object where TypeDiscriminator = 2, you place Endpoint1 in Person and Endpoint2 in Location.

Question:

How do you resolve the Error 3023 to allow these associations to occur ?

OR

How do you create the type of associations in the ADO Entity Framework that I have described above?

References:

• MSDN Forum - Error 3034: Problem in Mapping Fragments starting at lines ...
• Zeeshan Hirani's Blog - Contributions to Entity framework community
• Zeeshan Hirani's Entity Framework learning guide (this is an Excellent 514 page step-by-step guide to numerous concepts and gotchas with ADO EF. HIGHLY recommended!)

Code

SQL:

USE [GraphExample2]
GO
/****** Object:  Table [dbo].[Node]    Script Date: 02/17/2009 14:36:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Node](
    [NodeID] [int] NOT NULL,
    [NodeTypeDiscriminator] [int] NOT NULL,
    [Name] [varchar](255) NOT NULL,
    [Description] [varchar](1023) NULL,
 CONSTRAINT [PK_Node] PRIMARY KEY CLUSTERED 
(
    [NodeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Link]    Script Date: 02/17/2009 14:36:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Link](
    [LinkID] [int] NOT NULL,
    [LinkTypeDiscriminator] [int] NOT NULL,
    [Endpoint1] [int] NOT NULL,
    [Endpoint2] [int] NOT NULL,
    [Name] [varchar](255) NULL,
    [Description] [varchar](1023) NULL,
 CONSTRAINT [PK_Link] PRIMARY KEY CLUSTERED 
(
    [LinkID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  ForeignKey [FK_Link_Node_Endpoint1]    Script Date: 02/17/2009 14:36:12 ******/
ALTER TABLE [dbo].[Link]  WITH CHECK ADD  CONSTRAINT [FK_Link_Node_Endpoint1] FOREIGN KEY([Endpoint1])
REFERENCES [dbo].[Node] ([NodeID])
GO
ALTER TABLE [dbo].[Link] CHECK CONSTRAINT [FK_Link_Node_Endpoint1]
GO
/****** Object:  ForeignKey [FK_Link_Node_Endpoint2]    Script Date: 02/17/2009 14:36:12 ******/
ALTER TABLE [dbo].[Link]  WITH CHECK ADD  CONSTRAINT [FK_Link_Node_Endpoint2] FOREIGN KEY([Endpoint2])
REFERENCES [dbo].[Node] ([NodeID])
GO
ALTER TABLE [dbo].[Link] CHECK CONSTRAINT [FK_Link_Node_Endpoint2]
GO

EDMX:

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="1.0" xmlns:edmx="http://schemas.microsoft.com/ado/2007/06/edmx">
  <!-- EF Runtime content -->
  <edmx:Runtime>
    <!-- SSDL content -->
    <edmx:StorageModels>
      <Schema Namespace="GraphModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2005" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2006/04/edm/ssdl">
        <EntityContainer Name="GraphModelStoreContainer">
          <EntitySet Name="Link" EntityType="GraphModel.Store.Link" store:Type="Tables" Schema="dbo" />
          <EntitySet Name="Node" EntityType="GraphModel.Store.Node" store:Type="Tables" Schema="dbo" />
          <AssociationSet Name="FK_Link_Node_Endpoint1" Association="GraphModel.Store.FK_Link_Node_Endpoint1">
            <End Role="Node" EntitySet="Node" />
            <End Role="Link" EntitySet="Link" />
          </AssociationSet>
          <AssociationSet Name="FK_Link_Node_Endpoint2" Association="GraphModel.Store.FK_Link_Node_Endpoint2">
            <End Role="Node" EntitySet="Node" />
            <End Role="Link" EntitySet="Link" />
          </AssociationSet>
        </EntityContainer>
        <EntityType Name="Link">
          <Key>
            <PropertyRef Name="LinkID" />
          </Key>
          <Property Name="LinkID" Type="int" Nullable="false" />
          <Property Name="LinkTypeDiscriminator" Type="int" Nullable="false" />
          <Property Name="Endpoint1" Type="int" Nullable="false" />
          <Property Name="Endpoint2" Type="int" Nullable="false" />
          <Property Name="Name" Type="varchar" MaxLength="255" />
          <Property Name="Description" Type="varchar" MaxLength="1023" />
        </EntityType>
        <EntityType Name="Node">
          <Key>
            <PropertyRef Name="NodeID" />
          </Key>
          <Property Name="NodeID" Type="int" Nullable="false" />
          <Property Name="NodeTypeDiscriminator" Type="int" Nullable="false" />
          <Property Name="Name" Type="varchar" Nullable="false" MaxLength="255" />
          <Property Name="Description" Type="varchar" MaxLength="1023" />
        </EntityType>
        <Association Name="FK_Link_Node_Endpoint1">
          <End Role="Node" Type="GraphModel.Store.Node" Multiplicity="1" />
          <End Role="Link" Type="GraphModel.Store.Link" Multiplicity="*" />
          <ReferentialConstraint>
            <Principal Role="Node">
              <PropertyRef Name="NodeID" />
            </Principal>
            <Dependent Role="Link">
              <PropertyRef Name="Endpoint1" />
            </Dependent>
          </ReferentialConstraint>
        </Association>
        <Association Name="FK_Link_Node_Endpoint2">
          <End Role="Node" Type="GraphModel.Store.Node" Multiplicity="1" />
          <End Role="Link" Type="GraphModel.Store.Link" Multiplicity="*" />
          <ReferentialConstraint>
            <Principal Role="Node">
              <PropertyRef Name="NodeID" />
            </Principal>
            <Dependent Role="Link">
              <PropertyRef Name="Endpoint2" />
            </Dependent>
          </ReferentialConstraint>
        </Association>
      </Schema>
    </edmx:StorageModels>
    <!-- CSDL content -->
    <edmx:ConceptualModels>
      <Schema xmlns="http://schemas.microsoft.com/ado/2006/04/edm" Namespace="GraphModel" Alias="Self">
        <EntityContainer Name="GraphModelContainer" >
          <EntitySet Name="NodeSet" EntityType="GraphModel.Node" />
          <EntitySet Name="LinkSet" EntityType="GraphModel.Link" />
          <AssociationSet Name="PersonPersonToPerson" Association="GraphModel.PersonPersonToPerson">
            <End Role="Person" EntitySet="NodeSet" />
            <End Role="PersonToPerson" EntitySet="LinkSet" />
          </AssociationSet>
          <AssociationSet Name="PersonPersonToPerson1" Association="GraphModel.PersonPersonToPerson1">
            <End Role="Person" EntitySet="NodeSet" />
            <End Role="PersonToPerson" EntitySet="LinkSet" />
          </AssociationSet>
          <AssociationSet Name="Person_PersonToLocation" Association="GraphModel.Person_PersonToLocation">
            <End Role="Person" EntitySet="NodeSet" />
            <End Role="PersonToLocation" EntitySet="LinkSet" />
          </AssociationSet>
          <AssociationSet Name="Location_PersonToLocation" Association="GraphModel.Location_PersonToLocation">
            <End Role="Location" EntitySet="NodeSet" />
            <End Role="PersonToLocation" EntitySet="LinkSet" />
          </AssociationSet>
        </EntityContainer>
        <EntityType Name="Node" Abstract="true">
          <Key>
            <PropertyRef Name="NodeId" />
          </Key>
          <Property Name="NodeId" Type="Int32" Nullable="false" />
          <Property Name="Name" Type="String" Nullable="false" />
          <Property Name="Description" Type="String" Nullable="true" />
        </EntityType>
        <EntityType Name="Person" BaseType="GraphModel.Node" >
          <NavigationProperty Name="Leaders" Relationship="GraphModel.PersonPersonToPerson" FromRole="Person" ToRole="PersonToPerson" />
          <NavigationProperty Name="Followers" Relationship="GraphModel.PersonPersonToPerson1" FromRole="Person" ToRole="PersonToPerson" />
          <NavigationProperty Name="Locations" Relationship="GraphModel.Person_PersonToLocation" FromRole="Person" ToRole="PersonToLocation" />
        </EntityType>
        <EntityType Name="Location" BaseType="GraphModel.Node" >
          <NavigationProperty Name="Visitors" Relationship="GraphModel.Location_PersonToLocation" FromRole="Location" ToRole="PersonToLocation" />
        </EntityType>
        <EntityType Name="Link" Abstract="true">
          <Key>
            <PropertyRef Name="LinkId" />
          </Key>
          <Property Name="LinkId" Type="Int32" Nullable="false" />
          <Property Name="Name" Type="String" Nullable="true" />
          <Property Name="Description" Type="String" Nullable="true" />
        </EntityType>
        <EntityType Name="PersonToPerson" BaseType="GraphModel.Link" >
          <NavigationProperty Name="Leader" Relationship="GraphModel.PersonPersonToPerson" FromRole="PersonToPerson" ToRole="Person" />
          <NavigationProperty Name="Follower" Relationship="GraphModel.PersonPersonToPerson1" FromRole="PersonToPerson" ToRole="Person" />
        </EntityType>
        <EntityType Name="PersonToLocation" BaseType="GraphModel.Link" >
          <NavigationProperty Name="Person" Relationship="GraphModel.Person_PersonToLocation" FromRole="PersonToLocation" ToRole="Person" />
          <NavigationProperty Name="Location" Relationship="GraphModel.Location_PersonToLocation" FromRole="PersonToLocation" ToRole="Location" />
        </EntityType>
        <Association Name="PersonPersonToPerson">
          <End Type="GraphModel.Person" Role="Person" Multiplicity="1" />
          <End Type="GraphModel.PersonToPerson" Role="PersonToPerson" Multiplicity="*" />
        </Association>
        <Association Name="PersonPersonToPerson1">
          <End Type="GraphModel.Person" Role="Person" Multiplicity="1" />
          <End Type="GraphModel.PersonToPerson" Role="PersonToPerson" Multiplicity="*" />
        </Association>
        <Association Name="Person_PersonToLocation">
          <End Type="GraphModel.Person" Role="Person" Multiplicity="1" />
          <End Type="GraphModel.PersonToLocation" Role="PersonToLocation" Multiplicity="*" />
        </Association>
        <Association Name="Location_PersonToLocation">
          <End Type="GraphModel.Location" Role="Location" Multiplicity="1" />
          <End Type="GraphModel.PersonToLocation" Role="PersonToLocation" Multiplicity="*" />
        </Association>
      </Schema>
    </edmx:ConceptualModels>
    <!-- C-S mapping content -->
    <edmx:Mappings>
      <Mapping xmlns="urn:schemas-microsoft-com:windows:storage:mapping:CS" Space="C-S">
        <Alias Key="Model" Value="GraphModel" />
        <Alias Key="Target" Value="GraphModel.Store" />
        <EntityContainerMapping CdmEntityContainer="GraphModelContainer" StorageEntityContainer="GraphModelStoreContainer">
          <EntitySetMapping Name="LinkSet">
            <EntityTypeMapping TypeName="IsTypeOf(GraphModel.Link)">
              <MappingFragment StoreEntitySet="Link">
                <ScalarProperty Name="Description" ColumnName="Description" />
                <ScalarProperty Name="Name" ColumnName="Name" />
                <ScalarProperty Name="LinkId" ColumnName="LinkID" />
              </MappingFragment>
            </EntityTypeMapping>
            <EntityTypeMapping TypeName="IsTypeOf(GraphModel.PersonToPerson)">
              <MappingFragment StoreEntitySet="Link" >
                <ScalarProperty Name="LinkId" ColumnName="LinkID" />
                <Condition ColumnName="LinkTypeDiscriminator" Value="1" />
              </MappingFragment>
            </EntityTypeMapping>
            <EntityTypeMapping TypeName="IsTypeOf(GraphModel.PersonToLocation)">
              <MappingFragment StoreEntitySet="Link" >
                <ScalarProperty Name="LinkId" ColumnName="LinkID" />
                <Condition ColumnName="LinkTypeDiscriminator" Value="2" />
              </MappingFragment>
            </EntityTypeMapping>
          </EntitySetMapping>
          <EntitySetMapping Name="NodeSet">
            <EntityTypeMapping TypeName="IsTypeOf(GraphModel.Node)">
              <MappingFragment StoreEntitySet="Node">
                <ScalarProperty Name="Description" ColumnName="Description" />
                <ScalarProperty Name="Name" ColumnName="Name" />
                <ScalarProperty Name="NodeId" ColumnName="NodeID" />
              </MappingFragment>
            </EntityTypeMapping>
            <EntityTypeMapping TypeName="IsTypeOf(GraphModel.Person)">
              <MappingFragment StoreEntitySet="Node" >
                <ScalarProperty Name="NodeId" ColumnName="NodeID" />
                <Condition ColumnName="NodeTypeDiscriminator" Value="1" />
              </MappingFragment>
            </EntityTypeMapping>
            <EntityTypeMapping TypeName="IsTypeOf(GraphModel.Location)">
              <MappingFragment StoreEntitySet="Node" >
                <ScalarProperty Name="NodeId" ColumnName="NodeID" />
                <Condition ColumnName="NodeTypeDiscriminator" Value="2" />
              </MappingFragment>
            </EntityTypeMapping>
          </EntitySetMapping>
          <AssociationSetMapping Name="PersonPersonToPerson1" TypeName="GraphModel.PersonPersonToPerson1" StoreEntitySet="Link">
            <EndProperty Name="Person">
              <ScalarProperty Name="NodeId" ColumnName="Endpoint1" />
            </EndProperty>
            <EndProperty Name="PersonToPerson">
              <ScalarProperty Name="LinkId" ColumnName="LinkID" />
            </EndProperty>
            <Condition ColumnName="Endpoint1" IsNull="false" />
          </AssociationSetMapping>
          <AssociationSetMapping Name="PersonPersonToPerson" TypeName="GraphModel.PersonPersonToPerson" StoreEntitySet="Link">
            <EndProperty Name="Person">
              <ScalarProperty Name="NodeId" ColumnName="Endpoint2" />
            </EndProperty>
            <EndProperty Name="PersonToPerson">
              <ScalarProperty Name="LinkId" ColumnName="LinkID" />
            </EndProperty>
            <Condition ColumnName="Endpoint2" IsNull="false" />
          </AssociationSetMapping>
          <AssociationSetMapping Name="Person_PersonToLocation" TypeName="GraphModel.Person_PersonToLocation" StoreEntitySet="Link">
            <EndProperty Name="Person">
              <ScalarProperty Name="NodeId" ColumnName="Endpoint1" />
            </EndProperty>
            <EndProperty Name="PersonToLocation">
              <ScalarProperty Name="LinkId" ColumnName="LinkID" />
            </EndProperty>
            <Condition ColumnName="Endpoint1" IsNull="false" />
          </AssociationSetMapping>
          <AssociationSetMapping Name="Location_PersonToLocation" TypeName="GraphModel.Location_PersonToLocation" StoreEntitySet="Link">
            <EndProperty Name="Location">
              <ScalarProperty Name="NodeId" ColumnName="Endpoint2" />
            </EndProperty>
            <EndProperty Name="PersonToLocation">
              <ScalarProperty Name="LinkId" ColumnName="LinkID" />
            </EndProperty>
            <Condition ColumnName="Endpoint2" IsNull="false" />
          </AssociationSetMapping>
        </EntityContainerMapping>
      </Mapping>
    </edmx:Mappings>
  </edmx:Runtime>
  <!-- EF Designer content (DO NOT EDIT MANUALLY BELOW HERE) -->
  <edmx:Designer xmlns="http://schemas.microsoft.com/ado/2007/06/edmx">
    <edmx:Connection>
      <DesignerInfoPropertySet>
        <DesignerProperty Name="MetadataArtifactProcessing" Value="EmbedInOutputAssembly" />
      </DesignerInfoPropertySet>
    </edmx:Connection>
    <edmx:Options>
      <DesignerInfoPropertySet>
        <DesignerProperty Name="ValidateOnBuild" Value="true" />
      </DesignerInfoPropertySet>
    </edmx:Options>
    <!-- Diagram content (shape and connector positions) -->
    <edmx:Diagrams>
      <Diagram Name="GraphModel" ZoomLevel="114" >
        <EntityTypeShape EntityType="GraphModel.Node" Width="1.5" PointX="5.875" PointY="1.375" Height="1.427958984375" />
        <EntityTypeShape EntityType="GraphModel.Person" Width="1.5" PointX="5.875" PointY="3.25" Height="1.4279589843749996" />
        <EntityTypeShape EntityType="GraphModel.Location" Width="1.5" PointX="7.75" PointY="4.625" Height="1.0992643229166665" />
        <InheritanceConnector EntityType="GraphModel.Location">
          <ConnectorPoint PointX="7.375" PointY="2.0889794921875" />
          <ConnectorPoint PointX="8.5" PointY="2.0889794921875" />
          <ConnectorPoint PointX="8.5" PointY="4.625" />
        </InheritanceConnector>
        <EntityTypeShape EntityType="GraphModel.Link" Width="1.5" PointX="2.875" PointY="1.375" Height="1.427958984375" />
        <EntityTypeShape EntityType="GraphModel.PersonToPerson" Width="1.75" PointX="2.625" PointY="3.125" Height="0.9349169921875" />
        <InheritanceConnector EntityType="GraphModel.PersonToPerson">
          <ConnectorPoint PointX="3.625" PointY="2.802958984375" />
          <ConnectorPoint PointX="3.625" PointY="3.125" />
        </InheritanceConnector>
        <InheritanceConnector EntityType="GraphModel.Person">
          <ConnectorPoint PointX="6.625" PointY="2.802958984375" />
          <ConnectorPoint PointX="6.625" PointY="3.25" />
        </InheritanceConnector>
        <EntityTypeShape EntityType="GraphModel.PersonToLocation" Width="1.875" PointX="0.75" PointY="4.625" Height="1.2636116536458326" />
        <InheritanceConnector EntityType="GraphModel.PersonToLocation">
          <ConnectorPoint PointX="2.875" PointY="2.0889794921875" />
          <ConnectorPoint PointX="1.65625" PointY="2.0889794921875" />
          <ConnectorPoint PointX="1.65625" PointY="4.625" />
        </InheritanceConnector>
        <AssociationConnector Association="GraphModel.PersonPersonToPerson">
          <ConnectorPoint PointX="5.875" PointY="3.8193058268229163" />
          <ConnectorPoint PointX="4.375" PointY="3.8193058268229163" />
        </AssociationConnector>
        <AssociationConnector Association="GraphModel.PersonPersonToPerson1">
          <ConnectorPoint PointX="5.875" PointY="3.4721529134114579" />
          <ConnectorPoint PointX="4.375" PointY="3.4721529134114579" />
        </AssociationConnector>
        <AssociationConnector Association="GraphModel.Person_PersonToLocation">
          <ConnectorPoint PointX="6.625" PointY="4.677958984375" />
          <ConnectorPoint PointX="6.625" PointY="5.1875" />
          <ConnectorPoint PointX="2.625" PointY="5.1875" />
        </AssociationConnector>
        <AssociationConnector Association="GraphModel.Location_PersonToLocation">
          <ConnectorPoint PointX="7.75" PointY="5.4791666666666661" />
          <ConnectorPoint PointX="2.625" PointY="5.4791666666666661" />
        </AssociationConnector>
      </Diagram>
    </edmx:Diagrams>
  </edmx:Designer>
</edmx:Edmx>

Cotemporary

Associations btwn derived types may not be possible at all: blogs.msdn.com/adonet/archive/2007/02/12/… "Any associations using a derived type must be declared on the base type because the derived type does not declare an entity set in the entity container." – Cotemporary

Answer 1

Possible Workaround

1. Create a separate column for each association between derived types and make each of these columns nullable
2. Create a foreign key between each of these new columns and the primary key table.
3. Map each association in your Entity Model to a specific, unique column and foreign key so that each column and foreign key is only used once.

---

Problems

This is quite an undesirable solution because it explodes out the number of columns you need.

• More Columns - Adding a column for each association between derived types results in an explosion on the number of columns.
• Empty Columns In the case of TPH, it means you'll have a lot of empty columns in your table.
• SQL JOIN - Switching from TPH to TPT to avoid the number of empty columns results in the necessity for EF to use a JOIN which will have to occur extremely frequently (almost every time you deal with any of the derived types).
• Refactoring If you add a derived type in the future, you not only have to update your Entity model (*.edmx) and the it's mapping but you will also have to change the database schema by adding additional columns!

  ---

Example

For the Link/Node example above, the resulting database schema would look like this:

---

Code

SQL:

USE [GraphExample2]
GO
/****** Object:  Table [dbo].[Node]    Script Date: 02/26/2009 15:45:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Node](
    [NodeID] [int] IDENTITY(1,1) NOT NULL,
    [NodeTypeDiscriminator] [int] NOT NULL,
    [Name] [varchar](255) NOT NULL,
    [Description] [varchar](1023) NULL,
 CONSTRAINT [PK_Node] PRIMARY KEY CLUSTERED 
(
    [NodeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Link]    Script Date: 02/26/2009 15:45:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Link](
    [LinkID] [int] IDENTITY(1,1) NOT NULL,
    [LinkTypeDiscriminator] [int] NOT NULL,
    [LeaderID] [int] NULL,
    [FollowerID] [int] NULL,
    [PersonID] [int] NULL,
    [LocationID] [int] NULL,
    [Name] [varchar](255) NULL,
    [Description] [varchar](1023) NULL,
 CONSTRAINT [PK_Link] PRIMARY KEY CLUSTERED 
(
    [LinkID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  ForeignKey [FK_Link_Node_Follower]    Script Date: 02/26/2009 15:45:53 ******/
ALTER TABLE [dbo].[Link]  WITH CHECK ADD  CONSTRAINT [FK_Link_Node_Follower] FOREIGN KEY([FollowerID])
REFERENCES [dbo].[Node] ([NodeID])
GO
ALTER TABLE [dbo].[Link] CHECK CONSTRAINT [FK_Link_Node_Follower]
GO
/****** Object:  ForeignKey [FK_Link_Node_Leader]    Script Date: 02/26/2009 15:45:53 ******/
ALTER TABLE [dbo].[Link]  WITH CHECK ADD  CONSTRAINT [FK_Link_Node_Leader] FOREIGN KEY([LeaderID])
REFERENCES [dbo].[Node] ([NodeID])
GO
ALTER TABLE [dbo].[Link] CHECK CONSTRAINT [FK_Link_Node_Leader]
GO
/****** Object:  ForeignKey [FK_Link_Node_Location]    Script Date: 02/26/2009 15:45:53 ******/
ALTER TABLE [dbo].[Link]  WITH CHECK ADD  CONSTRAINT [FK_Link_Node_Location] FOREIGN KEY([LocationID])
REFERENCES [dbo].[Node] ([NodeID])
GO
ALTER TABLE [dbo].[Link] CHECK CONSTRAINT [FK_Link_Node_Location]
GO
/****** Object:  ForeignKey [FK_Link_Node_Person]    Script Date: 02/26/2009 15:45:53 ******/
ALTER TABLE [dbo].[Link]  WITH CHECK ADD  CONSTRAINT [FK_Link_Node_Person] FOREIGN KEY([PersonID])
REFERENCES [dbo].[Node] ([NodeID])
GO
ALTER TABLE [dbo].[Link] CHECK CONSTRAINT [FK_Link_Node_Person]
GO

EDMX:

<?xml version="1.0" encoding="utf-8"?>
  <edmx:Edmx Version="1.0" xmlns:edmx="http://schemas.microsoft.com/ado/2007/06/edmx">
    <!-- EF Runtime content -->
    <edmx:Runtime>
      <!-- SSDL content -->
      <edmx:StorageModels>
        <Schema Namespace="GraphModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2005" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2006/04/edm/ssdl">
          <EntityContainer Name="GraphModelStoreContainer">
            <EntitySet Name="Link" EntityType="GraphModel.Store.Link" store:Type="Tables" Schema="dbo" />
            <EntitySet Name="Node" EntityType="GraphModel.Store.Node" store:Type="Tables" Schema="dbo" />
            <AssociationSet Name="FK_Link_Node_Follower" Association="GraphModel.Store.FK_Link_Node_Follower">
              <End Role="Node" EntitySet="Node" />
              <End Role="Link" EntitySet="Link" />
            </AssociationSet>
            <AssociationSet Name="FK_Link_Node_Leader" Association="GraphModel.Store.FK_Link_Node_Leader">
              <End Role="Node" EntitySet="Node" />
              <End Role="Link" EntitySet="Link" />
            </AssociationSet>
            <AssociationSet Name="FK_Link_Node_Location" Association="GraphModel.Store.FK_Link_Node_Location">
              <End Role="Node" EntitySet="Node" />
              <End Role="Link" EntitySet="Link" />
            </AssociationSet>
            <AssociationSet Name="FK_Link_Node_Person" Association="GraphModel.Store.FK_Link_Node_Person">
              <End Role="Node" EntitySet="Node" />
              <End Role="Link" EntitySet="Link" />
            </AssociationSet>
          </EntityContainer>
          <EntityType Name="Link">
            <Key>
              <PropertyRef Name="LinkID" />
            </Key>
            <Property Name="LinkID" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
            <Property Name="LinkTypeDiscriminator" Type="int" Nullable="false" />
            <Property Name="LeaderID" Type="int" />
            <Property Name="FollowerID" Type="int" />
            <Property Name="PersonID" Type="int" />
            <Property Name="LocationID" Type="int" />
            <Property Name="Name" Type="varchar" MaxLength="255" />
            <Property Name="Description" Type="varchar" MaxLength="1023" />
          </EntityType>
          <EntityType Name="Node">
            <Key>
              <PropertyRef Name="NodeID" />
            </Key>
            <Property Name="NodeID" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
            <Property Name="NodeTypeDiscriminator" Type="int" Nullable="false" />
            <Property Name="Name" Type="varchar" Nullable="false" MaxLength="255" />
            <Property Name="Description" Type="varchar" MaxLength="1023" />
          </EntityType>
          <Association Name="FK_Link_Node_Follower">
            <End Role="Node" Type="GraphModel.Store.Node" Multiplicity="0..1" />
            <End Role="Link" Type="GraphModel.Store.Link" Multiplicity="*" />
            <ReferentialConstraint>
              <Principal Role="Node">
                <PropertyRef Name="NodeID" />
              </Principal>
              <Dependent Role="Link">
                <PropertyRef Name="FollowerID" />
              </Dependent>
            </ReferentialConstraint>
          </Association>
          <Association Name="FK_Link_Node_Leader">
            <End Role="Node" Type="GraphModel.Store.Node" Multiplicity="0..1" />
            <End Role="Link" Type="GraphModel.Store.Link" Multiplicity="*" />
            <ReferentialConstraint>
              <Principal Role="Node">
                <PropertyRef Name="NodeID" />
              </Principal>
              <Dependent Role="Link">
                <PropertyRef Name="LeaderID" />
              </Dependent>
            </ReferentialConstraint>
          </Association>
          <Association Name="FK_Link_Node_Location">
            <End Role="Node" Type="GraphModel.Store.Node" Multiplicity="0..1" />
            <End Role="Link" Type="GraphModel.Store.Link" Multiplicity="*" />
            <ReferentialConstraint>
              <Principal Role="Node">
                <PropertyRef Name="NodeID" />
              </Principal>
              <Dependent Role="Link">
                <PropertyRef Name="LocationID" />
              </Dependent>
            </ReferentialConstraint>
          </Association>
          <Association Name="FK_Link_Node_Person">
            <End Role="Node" Type="GraphModel.Store.Node" Multiplicity="0..1" />
            <End Role="Link" Type="GraphModel.Store.Link" Multiplicity="*" />
            <ReferentialConstraint>
              <Principal Role="Node">
                <PropertyRef Name="NodeID" />
              </Principal>
              <Dependent Role="Link">
                <PropertyRef Name="PersonID" />
              </Dependent>
            </ReferentialConstraint>
          </Association>
        </Schema>
      </edmx:StorageModels>
      <!-- CSDL content -->
      <edmx:ConceptualModels>
        <Schema xmlns="http://schemas.microsoft.com/ado/2006/04/edm" Namespace="GraphModel" Alias="Self">
          <EntityContainer Name="GraphModelContainer" >
            <EntitySet Name="NodeSet" EntityType="GraphModel.Node" />
            <EntitySet Name="LinkSet" EntityType="GraphModel.Link" />
            <AssociationSet Name="PersonPersonToPerson_Leader" Association="GraphModel.PersonPersonToPerson_Leader">
              <End Role="Person" EntitySet="NodeSet" />
              <End Role="PersonToPerson" EntitySet="LinkSet" />
            </AssociationSet>
            <AssociationSet Name="PersonPersonToPerson_Follower" Association="GraphModel.PersonPersonToPerson_Follower">
              <End Role="Person" EntitySet="NodeSet" />
              <End Role="PersonToPerson" EntitySet="LinkSet" />
            </AssociationSet>
            <AssociationSet Name="Person_PersonToLocation" Association="GraphModel.Person_PersonToLocation">
              <End Role="Person" EntitySet="NodeSet" />
              <End Role="PersonToLocation" EntitySet="LinkSet" />
            </AssociationSet>
            <AssociationSet Name="Location_PersonToLocation" Association="GraphModel.Location_PersonToLocation">
              <End Role="Location" EntitySet="NodeSet" />
              <End Role="PersonToLocation" EntitySet="LinkSet" />
            </AssociationSet>
          </EntityContainer>
          <EntityType Name="Node" Abstract="true">
            <Key>
              <PropertyRef Name="NodeId" />
            </Key>
            <Property Name="NodeId" Type="Int32" Nullable="false" />
            <Property Name="Name" Type="String" Nullable="false" />
            <Property Name="Description" Type="String" Nullable="true" />
          </EntityType>
          <EntityType Name="Person" BaseType="GraphModel.Node" >
            <NavigationProperty Name="Leaders" Relationship="GraphModel.PersonPersonToPerson_Leader" FromRole="Person" ToRole="PersonToPerson" />
            <NavigationProperty Name="Followers" Relationship="GraphModel.PersonPersonToPerson_Follower" FromRole="Person" ToRole="PersonToPerson" />
            <NavigationProperty Name="Locations" Relationship="GraphModel.Person_PersonToLocation" FromRole="Person" ToRole="PersonToLocation" />
          </EntityType>
          <EntityType Name="Location" BaseType="GraphModel.Node" >
            <NavigationProperty Name="Visitors" Relationship="GraphModel.Location_PersonToLocation" FromRole="Location" ToRole="PersonToLocation" />
          </EntityType>
          <EntityType Name="Link" Abstract="true">
            <Key>
              <PropertyRef Name="LinkId" />
            </Key>
            <Property Name="LinkId" Type="Int32" Nullable="false" />
            <Property Name="Name" Type="String" Nullable="true" />
            <Property Name="Description" Type="String" Nullable="true" />
          </EntityType>
          <EntityType Name="PersonToPerson" BaseType="GraphModel.Link" >
            <NavigationProperty Name="Leader" Relationship="GraphModel.PersonPersonToPerson_Leader" FromRole="PersonToPerson" ToRole="Person" />
            <NavigationProperty Name="Follower" Relationship="GraphModel.PersonPersonToPerson_Follower" FromRole="PersonToPerson" ToRole="Person" />
          </EntityType>
          <EntityType Name="PersonToLocation" BaseType="GraphModel.Link" >
            <NavigationProperty Name="Person" Relationship="GraphModel.Person_PersonToLocation" FromRole="PersonToLocation" ToRole="Person" />
            <NavigationProperty Name="Location" Relationship="GraphModel.Location_PersonToLocation" FromRole="PersonToLocation" ToRole="Location" />
          </EntityType>
          <Association Name="PersonPersonToPerson_Leader">
            <End Type="GraphModel.Person" Role="Person" Multiplicity="1" />
            <End Type="GraphModel.PersonToPerson" Role="PersonToPerson" Multiplicity="*" />
          </Association>
          <Association Name="PersonPersonToPerson_Follower">
            <End Type="GraphModel.Person" Role="Person" Multiplicity="1" />
            <End Type="GraphModel.PersonToPerson" Role="PersonToPerson" Multiplicity="*" />
          </Association>
          <Association Name="Person_PersonToLocation">
            <End Type="GraphModel.Person" Role="Person" Multiplicity="1" />
            <End Type="GraphModel.PersonToLocation" Role="PersonToLocation" Multiplicity="*" />
          </Association>
          <Association Name="Location_PersonToLocation">
            <End Type="GraphModel.Location" Role="Location" Multiplicity="1" />
            <End Type="GraphModel.PersonToLocation" Role="PersonToLocation" Multiplicity="*" />
          </Association>
        </Schema>
      </edmx:ConceptualModels>
      <!-- C-S mapping content -->
      <edmx:Mappings>
        <Mapping xmlns="urn:schemas-microsoft-com:windows:storage:mapping:CS" Space="C-S">
          <Alias Key="Model" Value="GraphModel" />
          <Alias Key="Target" Value="GraphModel.Store" />
          <EntityContainerMapping CdmEntityContainer="GraphModelContainer" StorageEntityContainer="GraphModelStoreContainer">
            <EntitySetMapping Name="LinkSet">
              <EntityTypeMapping TypeName="IsTypeOf(GraphModel.Link)">
                <MappingFragment StoreEntitySet="Link">
                  <ScalarProperty Name="Description" ColumnName="Description" />
                  <ScalarProperty Name="Name" ColumnName="Name" />
                  <ScalarProperty Name="LinkId" ColumnName="LinkID" />
                </MappingFragment>
              </EntityTypeMapping>
              <EntityTypeMapping TypeName="IsTypeOf(GraphModel.PersonToPerson)">
                <MappingFragment StoreEntitySet="Link" >
                  <ScalarProperty Name="LinkId" ColumnName="LinkID" />
                  <Condition ColumnName="LinkTypeDiscriminator" Value="1" />
                </MappingFragment>
              </EntityTypeMapping>
              <EntityTypeMapping TypeName="IsTypeOf(GraphModel.PersonToLocation)">
                <MappingFragment StoreEntitySet="Link" >
                  <ScalarProperty Name="LinkId" ColumnName="LinkID" />
                  <Condition ColumnName="LinkTypeDiscriminator" Value="2" />
                </MappingFragment>
              </EntityTypeMapping>
            </EntitySetMapping>
            <EntitySetMapping Name="NodeSet">
              <EntityTypeMapping TypeName="IsTypeOf(GraphModel.Node)">
                <MappingFragment StoreEntitySet="Node">
                  <ScalarProperty Name="Description" ColumnName="Description" />
                  <ScalarProperty Name="Name" ColumnName="Name" />
                  <ScalarProperty Name="NodeId" ColumnName="NodeID" />
                </MappingFragment>
              </EntityTypeMapping>
              <EntityTypeMapping TypeName="IsTypeOf(GraphModel.Person)">
                <MappingFragment StoreEntitySet="Node" >
                  <ScalarProperty Name="NodeId" ColumnName="NodeID" />
                  <Condition ColumnName="NodeTypeDiscriminator" Value="1" />
                </MappingFragment>
              </EntityTypeMapping>
              <EntityTypeMapping TypeName="IsTypeOf(GraphModel.Location)">
                <MappingFragment StoreEntitySet="Node" >
                  <ScalarProperty Name="NodeId" ColumnName="NodeID" />
                  <Condition ColumnName="NodeTypeDiscriminator" Value="2" />
                </MappingFragment>
              </EntityTypeMapping>
            </EntitySetMapping>
            <AssociationSetMapping Name="PersonPersonToPerson_Follower" TypeName="GraphModel.PersonPersonToPerson_Follower" StoreEntitySet="Link">
              <EndProperty Name="Person">
                <ScalarProperty Name="NodeId" ColumnName="FollowerID" />
              </EndProperty>
              <EndProperty Name="PersonToPerson">
                <ScalarProperty Name="LinkId" ColumnName="LinkID" />
              </EndProperty>
            </AssociationSetMapping>
            <AssociationSetMapping Name="PersonPersonToPerson_Leader" TypeName="GraphModel.PersonPersonToPerson_Leader" StoreEntitySet="Link">
              <EndProperty Name="Person">
                <ScalarProperty Name="NodeId" ColumnName="LeaderID" />
              </EndProperty>
              <EndProperty Name="PersonToPerson">
                <ScalarProperty Name="LinkId" ColumnName="LinkID" />
              </EndProperty>
            </AssociationSetMapping>
            <AssociationSetMapping Name="Person_PersonToLocation" TypeName="GraphModel.Person_PersonToLocation" StoreEntitySet="Link">
              <EndProperty Name="Person">
                <ScalarProperty Name="NodeId" ColumnName="PersonID" />
              </EndProperty>
              <EndProperty Name="PersonToLocation">
                <ScalarProperty Name="LinkId" ColumnName="LinkID" />
              </EndProperty>
            </AssociationSetMapping>
            <AssociationSetMapping Name="Location_PersonToLocation" TypeName="GraphModel.Location_PersonToLocation" StoreEntitySet="Link">
              <EndProperty Name="Location">
                <ScalarProperty Name="NodeId" ColumnName="LocationID" />
              </EndProperty>
              <EndProperty Name="PersonToLocation">
                <ScalarProperty Name="LinkId" ColumnName="LinkID" />
              </EndProperty>
            </AssociationSetMapping>
          </EntityContainerMapping>
        </Mapping>
      </edmx:Mappings>
    </edmx:Runtime>
    <!-- EF Designer content (DO NOT EDIT MANUALLY BELOW HERE) -->
    <edmx:Designer xmlns="http://schemas.microsoft.com/ado/2007/06/edmx">
      <edmx:Connection>
        <DesignerInfoPropertySet>
          <DesignerProperty Name="MetadataArtifactProcessing" Value="EmbedInOutputAssembly" />
        </DesignerInfoPropertySet>
      </edmx:Connection>
      <edmx:Options>
        <DesignerInfoPropertySet>
          <DesignerProperty Name="ValidateOnBuild" Value="true" />
        </DesignerInfoPropertySet>
      </edmx:Options>
      <!-- Diagram content (shape and connector positions) -->
      <edmx:Diagrams>
        <Diagram Name="GraphModel" ZoomLevel="114" >
          <EntityTypeShape EntityType="GraphModel.Node" Width="1.5" PointX="5.875" PointY="1.375" Height="1.427958984375" />
          <EntityTypeShape EntityType="GraphModel.Person" Width="1.5" PointX="5.875" PointY="3.25" Height="1.4279589843749996" />
          <EntityTypeShape EntityType="GraphModel.Location" Width="1.5" PointX="7.75" PointY="4.625" Height="1.0992643229166665" />
          <InheritanceConnector EntityType="GraphModel.Location">
            <ConnectorPoint PointX="7.375" PointY="2.4176741536458342" />
            <ConnectorPoint PointX="8.5" PointY="2.4176741536458342" />
            <ConnectorPoint PointX="8.5" PointY="4.625" />
          </InheritanceConnector>
          <EntityTypeShape EntityType="GraphModel.Link" Width="1.5" PointX="2.875" PointY="1.375" Height="1.427958984375" />
          <EntityTypeShape EntityType="GraphModel.PersonToPerson" Width="1.75" PointX="2.75" PointY="3.25" Height="1.2636116536458326" />
          <InheritanceConnector EntityType="GraphModel.PersonToPerson" ManuallyRouted="false">
            <ConnectorPoint PointX="3.625" PointY="2.802958984375" />
            <ConnectorPoint PointX="3.625" PointY="3.25" />
          </InheritanceConnector>
          <InheritanceConnector EntityType="GraphModel.Person">
            <ConnectorPoint PointX="6.625" PointY="3.4603483072916683" />
            <ConnectorPoint PointX="6.625" PointY="3.25" />
          </InheritanceConnector>
          <EntityTypeShape EntityType="GraphModel.PersonToLocation" Width="1.875" PointX="0.75" PointY="4.625" Height="1.2636116536458326" />
          <InheritanceConnector EntityType="GraphModel.PersonToLocation">
            <ConnectorPoint PointX="2.875" PointY="2.4176741536458342" />
            <ConnectorPoint PointX="1.65625" PointY="2.4176741536458342" />
            <ConnectorPoint PointX="1.65625" PointY="4.625" />
          </InheritanceConnector>
          <AssociationConnector Association="GraphModel.PersonPersonToPerson_Leader">
            <ConnectorPoint PointX="5.875" PointY="3.8818058268229163" />
            <ConnectorPoint PointX="4.5" PointY="3.8818058268229163" />
          </AssociationConnector>
          <AssociationConnector Association="GraphModel.PersonPersonToPerson_Follower">
            <ConnectorPoint PointX="5.875" PointY="3.5034029134114579" />
            <ConnectorPoint PointX="4.5" PointY="3.5034029134114579" />
          </AssociationConnector>
          <AssociationConnector Association="GraphModel.Person_PersonToLocation">
            <ConnectorPoint PointX="6.625" PointY="4.677958984375" />
            <ConnectorPoint PointX="6.625" PointY="5.0078214863281243" />
            <ConnectorPoint PointX="2.625" PointY="5.0078214863281243" />
          </AssociationConnector>
          <AssociationConnector Association="GraphModel.Location_PersonToLocation">
            <ConnectorPoint PointX="7.75" PointY="5.40018798828125" />
            <ConnectorPoint PointX="2.625" PointY="5.40018798828125" />
          </AssociationConnector>
        </Diagram>
      </edmx:Diagrams>
    </edmx:Designer>
  </edmx:Edmx>

Cotemporary

Burly, did you end up solving this by any other way than you mentioned? – Mcquillin

divitiae - No, I never found a "proper" solution to this issue. I ended up using TPT in order to avoid this issue but keep my inheritance structure =/ – Cotemporary

im just running into this EXACT same problem now. I actually came FROM doing TPT to using TPH. Did you find an issue? (1 year later). Don't tell me i have to go back to TPT again. :( – Chef

And also - i was using your workaround (before i even came here). I have unique, nullable, self-referencing FK's for all the navs between the derived entities. It still does not work. :( – Chef

It's been almost 2 years and I don't have access to that code anymore so the details are a bit fuzzy. However, I seem to recall that my workaround at least worked well enough to get me to the next step. Ultimately, we ended up using NHibernate for that project because we ruled EF was just not ready for code first design at that point. Obviously a lot has changed since then, but I was always surprised how little traffic this question got given the high occurrence of this type of data model. Sorry I don't have something more specific and helpful. – Cotemporary

Answer 2

AoA!

EntityModelCodeGenerator create 0..1 to many relation between two tables, Make it 1 to many.

In this kind of case it may be a possible Solution.

Best Regards! Salahuddin

Anzac

Answer 3

I had this error and it indicated that a column I removed in the database had not been removed in the model. I removed it from the model and the error went away. That was my situation, though. Maybe it will help someone.

Onassis