Entity Framework Self Referencing Hierarchical Many To Many

Question

OK this problem has it all.

Conceptually I have a Resource entity which can have many Child Resources and many Parent Resources. The Resource table has two fields, ID and Name with ID being the primary key.

To complete the many to many relationship I created a ResourceHierarchy table which has two fields, (Parent_ID, Child_ID) and two foreign keys with each one referencing the primary key of the Resource table (ID) and the ResourceHierarchy table has a composite primary key of (Parent_ID, Child_ID)

Now we already know that each Resource can act as a Parent or a Child to other resources, however logically not all Resources will have a Parent but that's besides the point. As an example lets say I have the following Resources in my Resource table.

ID    Name
10000 Little House
10001 Font Door
10002 Roof
10003 Roof Tile
10004 Tile Monster

And in the ResourceHierarchy table we have the following relationships.

Parent_ID Child_ID
10000     10001
10000     10002
10002     10003
10004     10003

Then Entity Framework generates the Entity, so far so good...

If you were to check the generated code in the edmx file you would see that the ResourceHierarchy table is being treated as a relationship and the ResourceHierarchy table is not accessible via code because it's not being treated as an entity.

If this is all I wanted then it would work out perfectly.

However the problem starts when I want to add a quantity column to the Resource entity hierarchy. For example the little house has just one Front Door and one Roof, but the Roof and Tile Monster Resources can have many Roof Tiles.

So if we add a Quantity column to the Resource table then we get the following.

ID    Name            Quantity
10000 Little House 1
10001 Font Door  1
10002 Roof   1
10003 Roof Tile  5
10004 Tile Monster 1

This creates the problem that the Roof and Tile Monster must share the 5 Roof Tiles. So naturally I would try to add the quantity column to the ResourceHierarchy table, however as soon as I do this and refresh the generated code is now treating the ResourceHierarchy table as an entity and not a relationship as it was previously. And now in order to get back to the Resource table I have to go through this non conceptual "Entity/Relationship" which isn't very straight forward. It's like I have a Entity in my conceptual model which would only be used to traverse back to the Resource Entity, and I'm not even sure if Resource.Children.Add(r) would create new rows in the ResourceHierarchy table in the db. It's like I would be picking off properties i.e. Quantity, off of an entity that I am only using as a relationship.

Ideally the ResourceHierarchy table would have the Quantity column look like this.

Parent_ID Child_ID Quantity
10000     10001  1
10000     10002  1
10002     10003  8
10004  10003  13

AND the Resource Entity would still have Children, Parents navigation properties and somehow access the Quantity column as a property of the Resource Entity.

I've tried to merge the generated code from having a quantity column and not having a quantity column but an exception is thrown which I interpret as the ResourceHierarchy table can either be a relationship or an entity, but not both.

Please HELP!

The edmx changes drastically with the addition and exclusion of the quantity column on the ResourceHierarchy table in the db.

Here is a sample comparison, the only difference is Resource is ResourceType and ResourceHierarchy is ResourceTypeHierarchy.

The SSDL Storage Model has no changes except one extra Property in the ResourceTypeHierarchy EntityType so I won't include it below.

WITHOUT QUANTITY COLUMN ON RESOURCETYPEHIERARCHY

RESOURCETYPEHIERARCHY IS A RELATIONSHIP

<!-- CSDL content -->
        <edmx:ConceptualModels>
          <Schema Namespace="MyModel" Alias="Self" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2008/09/edm"&gt;
            <EntityContainer Name="MyEntities">
              <EntitySet Name="ResourceTypes" EntityType="MyModel.ResourceType" />
              <AssociationSet Name="ResourceTypeHierarchy" Association="MyModel.ResourceTypeHierarchy">
                <End Role="ResourceType" EntitySet="ResourceTypes" />
                <End Role="ResourceType1" EntitySet="ResourceTypes" /></AssociationSet></EntityContainer>
            <EntityType Name="ResourceType">
              <Key>
                <PropertyRef Name="ID" /></Key>
              <Property Type="Int32" Name="ID" Nullable="false" />
              <Property Type="String" Name="Type" Nullable="false" MaxLength="25" FixedLength="false" Unicode="false" />
              <NavigationProperty Name="Parents" Relationship="MyModel.ResourceTypeHierarchy" FromRole="ResourceType" ToRole="ResourceType1" />
              <NavigationProperty Name="Children" Relationship="MyModel.ResourceTypeHierarchy" FromRole="ResourceType1" ToRole="ResourceType" /></EntityType>
            <Association Name="ResourceTypeHierarchy">
              <End Type="MyModel.ResourceType" Role="ResourceType" Multiplicity="*" />
              <End Type="MyModel.ResourceType" Role="ResourceType1" Multiplicity="*" /></Association></Schema>
        </edmx:ConceptualModels>


<!-- C-S mapping content -->
        <edmx:Mappings>
          <Mapping Space="C-S" xmlns="http://schemas.microsoft.com/ado/2008/09/mapping/cs"&gt;
            <EntityContainerMapping StorageEntityContainer="MyModelStoreContainer" CdmEntityContainer="MyEntities">
              <EntitySetMapping Name="ResourceTypes">

                <EntityTypeMapping TypeName="IsTypeOf(MyModel.ResourceType)">
                  <MappingFragment StoreEntitySet="ResourceType">
                    <ScalarProperty Name="ID" ColumnName="ID" />
                    <ScalarProperty Name="Type" ColumnName="Type" /></MappingFragment></EntityTypeMapping></EntitySetMapping>
              <AssociationSetMapping Name="ResourceTypeHierarchy" TypeName="MyModel.ResourceTypeHierarchy" StoreEntitySet="ResourceTypeHierarchy">
                <EndProperty Name="ResourceType1">
                  <ScalarProperty Name="ID" ColumnName="Parent_ID" /></EndProperty>
                <EndProperty Name="ResourceType">
                  <ScalarProperty Name="ID" ColumnName="Child_ID" /></EndProperty></AssociationSetMapping></EntityContainerMapping>
          </Mapping>
        </edmx:Mappings>

WITH QUANTITY COLUMN ON RESOURCETYPEHIERARCHY

RESOURCETYPEHIERARCHY IS NOW AN ENTITY

<!-- C-S mapping content -->
<edmx:Mappings>
  <Mapping Space="C-S" xmlns="http://schemas.microsoft.com/ado/2008/09/mapping/cs"&gt;
    <EntityContainerMapping StorageEntityContainer="MyModelStoreContainer" CdmEntityContainer="MyEntities">
      <EntitySetMapping Name="ResourceTypes">

        <EntityTypeMapping TypeName="IsTypeOf(MyModel.ResourceType)">
          <MappingFragment StoreEntitySet="ResourceType">
            <ScalarProperty Name="ID" ColumnName="ID" />
            <ScalarProperty Name="Type" ColumnName="Type" /></MappingFragment></EntityTypeMapping></EntitySetMapping>
      <EntitySetMapping Name="ResourceTypeHierarchies">

        <EntityTypeMapping TypeName="IsTypeOf(MyModel.ResourceTypeHierarchy)">
          <MappingFragment StoreEntitySet="ResourceTypeHierarchy">
            <ScalarProperty Name="Child_ID" ColumnName="Child_ID" />
            <ScalarProperty Name="Parent_ID" ColumnName="Parent_ID" />
            <ScalarProperty Name="Quantity" ColumnName="Quantity" /></MappingFragment></EntityTypeMapping></EntitySetMapping>
      <AssociationSetMapping Name="FK_Child" TypeName="MyModel.FK_Child" StoreEntitySet="ResourceTypeHierarchy">
        <EndProperty Name="ResourceTypeHierarchy">
          <ScalarProperty Name="Child_ID" ColumnName="Child_ID" />
          <ScalarProperty Name="Parent_ID" ColumnName="Parent_ID" /></EndProperty>
        <EndProperty Name="ResourceType">
          <ScalarProperty Name="ID" ColumnName="Child_ID" /></EndProperty></AssociationSetMapping>
      <AssociationSetMapping Name="FK_Parent" TypeName="MyModel.FK_Parent" StoreEntitySet="ResourceTypeHierarchy">
        <EndProperty Name="ResourceTypeHierarchy">
          <ScalarProperty Name="Child_ID" ColumnName="Child_ID" />
          <ScalarProperty Name="Parent_ID" ColumnName="Parent_ID" /></EndProperty>
        <EndProperty Name="ResourceType">
          <ScalarProperty Name="ID" ColumnName="Parent_ID" /></EndProperty></AssociationSetMapping></EntityContainerMapping>
  </Mapping>
</edmx:Mappings>

Answer 1

Whew, this makes my head spin just reading the question. What did you end up doing to make this work?

-Chris

Answer 2

I believe this is working as espected. What I mean is, if you put an attribute on a table that establishes a relation between two entities, that table MUST (from data representation theory at least) be represented as a proper entity. If you think about this carefully you'll understand why. The quantity you mentioned is an attribute of the relation not of any of the related entities. As such that table must be treated as an entity and not as a relationship.

Now on how to overcome this, one thing that comes to my mind (although I'm not sure if this will completly solve your problem) is to treat the relationship as you originally thought (without the quantity) and have another table (that will be mapped to an Entity in you model) that stores the quantity of a certain relation. I think that this table can even have a foreign key constraint on you db to the original relationship table, although this foreign key can't be mapped to a relation on your model (because you have no entity for the endpoint), but this still allows you to mantain data integrity on you storage.

Hope this helps, Vítor