Linq to Entities, Table per Type and Nullable Foreign Key Relationships

Question

I'm using Linq to entities applying a Table per Type approach. This has been going very well, up to now. I have the following setup:

• Parent Table
• Child Table (Inherits from parent)
• Grand Child Table (Inherits from Child Table)
• Linking Table (Has Foreign Key Nullable, to Child Table)

Here is the database diagram

Following the above video i applied the Table Per Type approach to the default schema that Linq to entities creates when you add the above tables to a model.

Before applying Table per Type:

After Table per Type:

I then compiled the project and got the error you can see in the image above. To fix this i went to the mapping for the foreign key link, i added the childid field, which the error message was moaning about.

I then recompiled and get another error:

Problem in Mapping Fragments starting at lines 147, 176: Two entities with different keys are mapped to the same row. Ensure these two mapping fragments do not map two groups of entities with overlapping keys to the same group of rows.

This is the point i'm at now. The problem seems to the that the "ChildID" on the "LinkingTable" is Nullable. If i set it to be Not nullable i don't get the above error.

I have saved the Database and project used in the above steps to a sky drive.

Does anyone know how to fix this error?

Dave

Here's the fixed Code (Thanks to The Gecko)

Before

<AssociationSetMapping Name="FK_LinkingTable_Child"
    TypeName="TablePerTypeModel.FK_LinkingTable_Child" 
    StoreEntitySet="LinkingTable">
    <EndProperty Name="Child">
        <ScalarProperty Name="Id" ColumnName="ChildID" />
    </EndProperty>
    <EndProperty Name="LinkingTable">
        <ScalarProperty Name="LinkTableID" ColumnName="LinkTableID" />
    </EndProperty>
</AssociationSetMapping>

After

<AssociationSetMapping Name="FK_LinkingTable_Child"
    TypeName="TablePerTypeModel.FK_LinkingTable_Child" 
    StoreEntitySet="LinkingTable">
    <EndProperty Name="Child">
        <ScalarProperty Name="Id" ColumnName="ChildID" />
    </EndProperty>
    <EndProperty Name="LinkingTable">
        <ScalarProperty Name="LinkTableID" ColumnName="LinkTableID" />
    </EndProperty>
    <Condition ColumnName="ChildID" IsNull="false"/>
</AssociationSetMapping>

Proboscidean

Answer 1

Try updating the AssociationMapping node in the Mapping section of your EDMX file to include a condition to allow for nulls.

e.g.

<AssociationSetMapping>
  ...
  <Condition ColumnName="" IsNull="false"/>
</AssociationSetMapping>

Fichtean

brilliant... that totally works.. It now looks like this: <Condition ColumnName="ChildID" IsNull="false"/> – Proboscidean