实体框架自我引用分层次多 [英] Entity Framework Self Referencing Hierarchical Many To Many

问题描述

从概念上讲，我有一个可以拥有许多子资源和许多父资源的资源实体。资源表有两个字段，ID和Name，ID为主键。

为了完成多对多关系，我创建了一个ResourceHierarchy表，它有两个字段（ Parent_ID，Child_ID）和两个外键，每个引用资源表（ID）和ResourceHierarchy表的主键的外键具有（Parent_ID，Child_ID）的复合主键

现在我们已经知道，每个资源都可以作为其他资源的父或子，但逻辑上并不是所有的资源都将有一个父，但除此之外。举个例子来说，我的资源表中有以下资源。

  ID名称
 10000小屋
 10001字体门
 10002屋顶
 10003屋顶瓷砖
 10004瓷砖怪物
  

在ResourceHierarchy表中，我们有以下关系。

  Parent_ID Child_ID 
 10000 10001 
 10000 10002 
 10002 10003 
 10004 10003 
  

然后Entity Framework生成实体，到目前为止这么好...

如果要检查edmx文件中生成的代码，您将看到ResourceHierarchy表被视为关系， ResourceHierarchy表不能通过代码访问，因为它不被视为一个实体。

如果这是我想要的，那么它将完美的解决。

但是，当我要向资源实体hier添加数量列时，问题开始君主国。例如，小房子只有一个前门和一个屋顶，但屋顶和瓷砖怪物资源可以有许多屋顶瓦。

所以如果我们添加一个数量列资源表然后我们得到以下。

  ID名称数量
 10000小屋1 
 10001字体门1 
 10002屋顶1 
 10003屋顶瓦5 
 10004瓷砖怪物1 
  

这造成了屋顶和瓦片怪物必须共享5个瓦片的问题。所以我自然会尝试将数量列添加到ResourceHierarchy表中，但是一旦我这样做，刷新生成的代码现在将ResourceHierarchy表视为一个实体，而不是像之前那样的关系。而现在为了回到资源表，我必须经历这个不是很简单的非概念性的实体/关系。这就像我在我的概念模型中有一个实体，只能用于遍历资源实体，我甚至不确定Resource.Children.Add（r）是否会在数据库的ResourceHierarchy表中创建新的行。这就像我会从物质中淘汰属性，即数量，我只用作一个关系。

理论上，ResourceHierarchy表将数量列看起来像这个。

  Parent_ID Child_ID数量
 10000 10001 1 
 10000 10002 1 
 10002 10003 8 
 10004 10003 13 
  

资源实体仍然会有Children，Parents导航属性，访问数量列作为资源实体的属性。

我已经尝试合并生成的代码，具有数量列，没有数量列但是异常被抛出，我解释为ResourceHierarchy表可以是关系或实体，但不能两者。

请帮助！

在db中ResourceHierarchy表中添加和排除数量列后，edmx会发生重大变化。

下面是一个示例比较，唯一的区别是Resource is ResourceType和ResourceHierarchy是ResourceTypeHierarchy。

SSDL存储模型除了一个额外的ResourceTypeHierarchy EntityType中的属性，所以我不会在下面包含它。

在资源库中没有数量COLUMN

RESOURCETYPEHIERARCHY是一个关系

 <！ -  CSDL内容 - > 
< edmx：ConceptualModels> 
< Schema Namespace =MyModelAlias =Selfxmlns：store =http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGeneratorxmlns =http：// schemas .microsoft.com / ADO / 2008/09 / EDM> 
< EntityContainer Name =MyEntities> 
< EntitySet Name =ResourceTypesEntityType =MyModel.ResourceType/> 
< AssociationSet Name =ResourceTypeHierarchyAssociation =MyModel.ResourceTypeHierarchy> 
< End Role =ResourceTypeEntitySet =ResourceTypes/> 
< End Role =ResourceType1EntitySet =ResourceTypes/>< / AssociationSet>< / EntityContainer> 
< EntityType Name =ResourceType> 
< Key> 
< PropertyRef Name =ID/>< / Key> 
<属性类型=Int32Name =IDNullable =false/> 
< Property Type =StringName =TypeNullable =falseMaxLength =25FixedLength =falseUnicode =false/> 
< NavigationProperty Name =ParentsRelationship =MyModel.ResourceTypeHierarchyFromRole =ResourceTypeToRole =ResourceType1/> 
< NavigationProperty Name =ChildrenRelationship =MyModel.ResourceTypeHierarchyFromRole =ResourceType1ToRole =ResourceType/>< / EntityType> 
< Association Name =ResourceTypeHierarchy> 
< End Type =MyModel.ResourceTypeRole =ResourceTypeMultiplicity =*/> 
< End Type =MyModel.ResourceTypeRole =ResourceType1Multiplicity =*/>< / Association>< / Schema> 
< / edmx：ConceptualModels> 
 
 
<！ -  C-S映射内容 - > 
< edmx：映射> 
< Mapping Space =C-Sxmlns =http://schemas.microsoft.com/ado/2008/09/mapping/cs> 
< EntityContainerMapping StorageEntityContainer =MyModelStoreContainerCdmEntityContainer =MyEntities> 
< EntitySetMapping Name =ResourceTypes> 
 
< EntityTypeMapping TypeName =IsTypeOf（MyModel.ResourceType）> 
< MappingFragment StoreEntitySet =ResourceType> 
< ScalarProperty Name =IDColumnName =ID/> 
< ScalarProperty Name =TypeColumnName =Type/>< / MappingFragment>< / EntityTypeMapping>< / EntitySetMapping> 
< AssociationSetMapping Name =ResourceTypeHierarchyTypeName =MyModel.ResourceTypeHierarchyStoreEntitySet =ResourceTypeHierarchy> 
< EndProperty Name =ResourceType1> 
< ScalarProperty Name =IDColumnName =Parent_ID/>< / EndProperty> 
< EndProperty Name =ResourceType> 
< ScalarProperty Name =IDColumnName =Child_ID/>< / EndProperty>< / AssociationSetMapping>< / EntityContainerMapping> 
< / Mapping> 
< / edmx：映射> 
  

WITH QUANTITY COLUMN ON RESOURCETYPEHIERARCHY

RESOURCETYPEHIERARCHY现在是一个实体

 <！ -  CS映射内容 - > 
< edmx：映射> 
< Mapping Space =C-Sxmlns =http://schemas.microsoft.com/ado/2008/09/mapping/cs> 
< EntityContainerMapping StorageEntityContainer =MyModelStoreContainerCdmEntityContainer =MyEntities> 
< EntitySetMapping Name =ResourceTypes> 
 
< EntityTypeMapping TypeName =IsTypeOf（MyModel.ResourceType）> 
< MappingFragment StoreEntitySet =ResourceType> 
< ScalarProperty Name =IDColumnName =ID/> 
< ScalarProperty Name =TypeColumnName =Type/>< / MappingFragment>< / EntityTypeMapping>< / EntitySetMapping> 
< EntitySetMapping Name =ResourceTypeHierarchies> 
 
< EntityTypeMapping TypeName =IsTypeOf（MyModel.ResourceTypeHierarchy）> 
< MappingFragment StoreEntitySet =ResourceTypeHierarchy> 
< ScalarProperty Name =Child_IDColumnName =Child_ID/> 
< ScalarProperty Name =Parent_IDColumnName =Parent_ID/> 
< ScalarProperty Name =QuantityColumnName =Quantity/>< / MappingFragment>< / EntityTypeMapping>< / EntitySetMapping> 
< AssociationSetMapping Name =FK_ChildTypeName =MyModel.FK_ChildStoreEntitySet =ResourceTypeHierarchy> 
< EndProperty Name =ResourceTypeHierarchy> 
< ScalarProperty Name =Child_IDColumnName =Child_ID/> 
< ScalarProperty Name =Parent_IDColumnName =Parent_ID/>< / EndProperty> 
< EndProperty Name =ResourceType> 
< ScalarProperty Name =IDColumnName =Child_ID/>< / EndProperty>< / AssociationSetMapping> 
< AssociationSetMapping Name =FK_ParentTypeName =MyModel.FK_ParentStoreEntitySet =ResourceTypeHierarchy> 
< EndProperty Name =ResourceTypeHierarchy> 
< ScalarProperty Name =Child_IDColumnName =Child_ID/> 
< ScalarProperty Name =Parent_IDColumnName =Parent_ID/>< / EndProperty> 
< EndProperty Name =ResourceType> 
< ScalarProperty Name =IDColumnName =Parent_ID/>< / EndProperty>< / AssociationSetMapping>< / EntityContainerMapping> 
< / Mapping> 
< / edmx：映射> 
  

解决方案

我相信这是正常工作。我的意思是，如果你在一个建立两个实体之间的关系的表上放置一个属性，则表必须（至少从数据表示理论）被表示为适当的实体。如果你仔细想一想，你会明白为什么。您提到的数量是不是任何相关实体的关系的属性。因此，表必须被视为一个实体，而不是一个关系。

现在，如何克服这一点，我想到的一件事（虽然我是不确定如果这将完全解决您的问题）是按照您原来想的（没有数量）对待关系，并且有另一个表（将映射到您的模型中的实体），存储某个关系的数量。我认为这个表甚至可以有一个外键约束你的db到原始关系表，虽然这个外键不能映射到你的模型的关系（因为你没有端点的实体），但这仍然允许您掌握存储空间的数据完整性。

希望这有帮助，
Vítor

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">
            <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">
            <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">
    <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>

解决方案

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

这篇关于实体框架自我引用分层次多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！