EF4 Inheritance issues using Table per Type

Question

I'm having quite a bit of trouble trying to get inheritance to work in EF4. The original model is quite large, however I have managed to replicate it in a model of just two entities.

Essentially the two tables are "Asset" and "Questionnaire". Questionnaire inherits Asset (i.e. Questionnaire is an Asset). They do this by Questionnaire having an AssetID linking back to the Asset. Technically the design may not be the best however I'm trying to make this work for a 10 year old database without any major redesigns...

So given that, the tables are:

Asset

• AssetID uniqueidentifier NOT NULL - the primary key of this table
• Other irrelevant columns

Content_Questionnaire

• Id uniqueidentifier NOT NULL - the primary key of this table
• AssetID uniqueidentifier NOT NULL - foreign key to Asset.AssetID
• Other irrelevant columns

The model for this is extremely simplistic - essentially it is:

The EDMX file for this is:

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="2.0" xmlns:edmx="http://schemas.microsoft.com/ado/2008/10/edmx">
  <!-- EF Runtime content -->
  <edmx:Runtime>
    <!-- SSDL content -->
    <edmx:StorageModels>
    <Schema Namespace="SnapServerModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2008" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2009/02/edm/ssdl">
        <EntityContainer Name="SnapServerModelStoreContainer">
          <EntitySet Name="Asset" EntityType="SnapServerModel.Store.Asset" store:Type="Tables" Schema="dbo" />
          <EntitySet Name="Content_Questionnaire" EntityType="SnapServerModel.Store.Content_Questionnaire" store:Type="Tables" Schema="dbo" />
          <AssociationSet Name="FK_Content_Questionnaire_Asset" Association="SnapServerModel.Store.FK_Content_Questionnaire_Asset">
            <End Role="Asset" EntitySet="Asset" />
            <End Role="Content_Questionnaire" EntitySet="Content_Questionnaire" />
          </AssociationSet>
        </EntityContainer>
        <EntityType Name="Asset">
          <Key>
            <PropertyRef Name="AssetID" />
          </Key>
          <Property Name="AssetID" Type="uniqueidentifier" Nullable="false" />
        </EntityType>
        <EntityType Name="Content_Questionnaire">
          <Key>
            <PropertyRef Name="Id" />
          </Key>
          <Property Name="Id" Type="uniqueidentifier" Nullable="false" />
          <Property Name="AssetID" Type="uniqueidentifier" Nullable="false" />
        </EntityType>
        <Association Name="FK_Content_Questionnaire_Asset">
          <End Role="Asset" Type="SnapServerModel.Store.Asset" Multiplicity="1" />
          <End Role="Content_Questionnaire" Type="SnapServerModel.Store.Content_Questionnaire" Multiplicity="*" />
          <ReferentialConstraint>
            <Principal Role="Asset">
              <PropertyRef Name="AssetID" />
            </Principal>
            <Dependent Role="Content_Questionnaire">
              <PropertyRef Name="AssetID" />
            </Dependent>
          </ReferentialConstraint>
        </Association>
      </Schema></edmx:StorageModels>
    <!-- CSDL content -->
    <edmx:ConceptualModels>
      <Schema Namespace="SnapServerModel" Alias="Self" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" xmlns="http://schemas.microsoft.com/ado/2008/09/edm">
        <EntityContainer Name="SnapServerEntities" annotation:LazyLoadingEnabled="true">
          <EntitySet Name="Assets" EntityType="SnapServerModel.Asset" />
          <AssociationSet Name="FK_Content_Questionnaire_Asset" Association="SnapServerModel.FK_Content_Questionnaire_Asset">
            <End Role="Asset" EntitySet="Assets" />
            <End Role="Content_Questionnaire" EntitySet="Assets" />
          </AssociationSet>
        </EntityContainer>
        <EntityType Name="Asset">
          <Key>
            <PropertyRef Name="AssetID" />
          </Key>
          <Property Type="Guid" Name="AssetID" Nullable="false" />
          <NavigationProperty Name="Content_Questionnaire" Relationship="SnapServerModel.FK_Content_Questionnaire_Asset" FromRole="Asset" ToRole="Content_Questionnaire" />
        </EntityType>
        <EntityType Name="Content_Questionnaire" BaseType="SnapServerModel.Asset">
          <Property Type="Guid" Name="Id" Nullable="false" />
          <NavigationProperty Name="Asset" Relationship="SnapServerModel.FK_Content_Questionnaire_Asset" FromRole="Content_Questionnaire" ToRole="Asset" />
        </EntityType>
        <Association Name="FK_Content_Questionnaire_Asset">
          <End Type="SnapServerModel.Asset" Role="Asset" Multiplicity="1" />
          <End Type="SnapServerModel.Content_Questionnaire" Role="Content_Questionnaire" Multiplicity="1" />
          <ReferentialConstraint>
            <Principal Role="Asset">
              <PropertyRef Name="AssetID" />
            </Principal>
            <Dependent Role="Content_Questionnaire">
              <PropertyRef Name="AssetID" />
            </Dependent>
          </ReferentialConstraint>
        </Association>
      </Schema>
    </edmx:ConceptualModels>
    <!-- C-S mapping content -->
    <edmx:Mappings>
      <Mapping Space="C-S" xmlns="http://schemas.microsoft.com/ado/2008/09/mapping/cs">
        <EntityContainerMapping StorageEntityContainer="SnapServerModelStoreContainer" CdmEntityContainer="SnapServerEntities">
          <EntitySetMapping Name="Assets">
            <EntityTypeMapping TypeName="IsTypeOf(SnapServerModel.Asset)">
              <MappingFragment StoreEntitySet="Asset">
                <ScalarProperty Name="AssetID" ColumnName="AssetID" />
                </MappingFragment>
            </EntityTypeMapping>
            <EntityTypeMapping TypeName="IsTypeOf(SnapServerModel.Content_Questionnaire)">
              <MappingFragment StoreEntitySet="Content_Questionnaire">
                <ScalarProperty Name="Id" ColumnName="Id" />
                <ScalarProperty Name="AssetID" ColumnName="AssetID" />
              </MappingFragment>
            </EntityTypeMapping>
          </EntitySetMapping>
        </EntityContainerMapping>
      </Mapping>
    </edmx:Mappings>
  </edmx:Runtime>
  <!-- EF Designer content (DO NOT EDIT MANUALLY BELOW HERE) -->
  <Designer xmlns="http://schemas.microsoft.com/ado/2008/10/edmx">
    <Connection>
      <DesignerInfoPropertySet>
        <DesignerProperty Name="MetadataArtifactProcessing" Value="EmbedInOutputAssembly" />
      </DesignerInfoPropertySet>
    </Connection>
    <Options>
      <DesignerInfoPropertySet>
        <DesignerProperty Name="ValidateOnBuild" Value="true" />
        <DesignerProperty Name="EnablePluralization" Value="True" />
        <DesignerProperty Name="IncludeForeignKeysInModel" Value="True" />
      </DesignerInfoPropertySet>
    </Options>
    <!-- Diagram content (shape and connector positions) -->
    <Diagrams>
      <Diagram Name="Model1">
        <EntityTypeShape EntityType="SnapServerModel.Asset" Width="1.5" PointX="0.75" PointY="2.125" Height="1.4033821614583335" />
        <EntityTypeShape EntityType="SnapServerModel.Content_Questionnaire" Width="1.5" PointX="3" PointY="2.125" Height="1.4033821614583339" />
        <AssociationConnector Association="SnapServerModel.FK_Content_Questionnaire_Asset" >
          <ConnectorPoint PointX="2.25" PointY="2.4133455403645834" />
          <ConnectorPoint PointX="3" PointY="2.4133455403645834" />
        </AssociationConnector>
        <InheritanceConnector EntityType="SnapServerModel.Content_Questionnaire" ManuallyRouted="false" >
          <ConnectorPoint PointX="2.25" PointY="2.8266910807291667" />
          <ConnectorPoint PointX="3" PointY="2.8266910807291667" />
        </InheritanceConnector>
      </Diagram>
    </Diagrams>
  </Designer>
</edmx:Edmx>

Now my problem is that no matter what I've tried - I'm getting two error messages:

1. Error 1 Error 3002: Problem in mapping fragments starting at line 87:Potential runtime violation of table Content_Questionnaire's keys (Content_Questionnaire.Id): Columns (Content_Questionnaire.Id) are mapped to EntitySet Assets's properties (Assets.Id) on the conceptual side but they do not form the EntitySet's key properties (Assets.AssetID).
2. Error 2 Error 3003: Problem in mapping fragments starting at line 87:All the key properties (Assets.AssetID) of the EntitySet Assets must be mapped to all the key properties (Content_Questionnaire.Id) of table Content_Questionnaire.

How on earth do I get rid of these errors?

It is probably something completely obvious that I am missing - however ANY help or even a nudge in the right direction would be greatly appreciated. I've been getting a few grey hairs trying to work through this one today!

Thanks in advance for any help you may be able to shed.

Answer 1

Ok, there are a few things that you need to fix in your model:

1.First, you need to change the primary key on your Content_Questionnaire table to be AssetID, as far as I know, EF does support inheritance between 2 entity only if the association is on Primary Keys on both end. So it should be look like this:

<EntityType Name="Content_Questionnaire">
    <Key>
        <!--First Change:-->
        <PropertyRef Name="AssetID" />
    </Key>
    <Property Name="Id" Type="uniqueidentifier" Nullable="false" />
    <Property Name="AssetID" Type="uniqueidentifier" Nullable="false" />
</EntityType>

2.The Multiplicity on the Derived entity must be 0..1 (right now it's *)

<Association Name="FK_Content_Questionnaire_Asset">
  <End Role="Asset" Type="SnapServerModel.Store.Asset" Multiplicity="1" />
  <!--Secound Change:-->
  <End Role="Content_Questionnaire" 
Type="SnapServerModel.Store.Content_Questionnaire" Multiplicity="0..1" />
  <ReferentialConstraint>
    <Principal Role="Asset">
      <PropertyRef Name="AssetID" />
    </Principal>
    <Dependent Role="Content_Questionnaire">
      <PropertyRef Name="AssetID" />
    </Dependent>
  </ReferentialConstraint>
</Association>

3.Also, on your Model surface, just select the association line between the two entities and remove it (your navigation properties will be gone), by having inheritance, you don't need that.

Now, try to build your project again, you'll see that the compilation errors are gone.