none
Many to many relations in the same table with discriminator, is it possible? RRS feed

  • Question

  • I'm looking for a way to add documents to many entities without getting the db model too complex, for this reason I want to map all the documents n to n relations in only one db table. Here my proof of concept and the entities model:

    I have mapped the n to n document-user and document-role to the same table and added in the xml a condition to the association, like it would be a table per hierarchy mapping.

    <AssociationSetMapping Name="DocumentRole" TypeName="aspnetdbModel.DocumentRole" StoreEntitySet="DocumentObjects" >
      <EndProperty Name="Role">
        <ScalarProperty Name="RoleId" ColumnName="ObjectId" />
      </EndProperty>
      <EndProperty Name="Document">
        <ScalarProperty Name="DocumentId" ColumnName="DocumentId" />
      </EndProperty>
      <Condition ColumnName="ObjectType" Value="Role"/>
    </AssociationSetMapping>
     <AssociationSetMapping Name="DocumentUser" TypeName="aspnetdbModel.DocumentUser" StoreEntitySet="DocumentObjects" >
      <EndProperty Name="User">
        <ScalarProperty Name="UserId" ColumnName="ObjectId" />
      </EndProperty>
      <EndProperty Name="Document">
        <ScalarProperty Name="DocumentId" ColumnName="DocumentId" />
      </EndProperty>
      <Condition ColumnName="ObjectType" Value="User"/>
     </AssociationSetMapping>
    


    But the model throws the following exception:

    Two entities with possibly identical keys are mapped to different rows within the same table. Ensure these two mapping fragments do not map two unrelated EntitySets to two distinct groups of rows.

    Can it be solved or not? If yes how?

    Wednesday, December 21, 2011 10:37 AM

Answers

  • Thanks Allen for the reply

    I'm sorry but I don't see how that could solve the problem, perhaps I have explained it wrong.

    The User and Role entities already exist and they are already mapped, I was trying to extend the model with an attached document entity to any already mapped entity. I have already solved it with a table per hierarchy on the document entity, but in this way is a 1 to n relation and the documents are unique for each entity.

    <Association Name="RolesDocumentRole">
      <End Type="aspnetdbModel.Roles" Role="Roles" Multiplicity="1" />
      <End Type="aspnetdbModel.DocumentRole" Role="DocumentRole" Multiplicity="*" />
      <ReferentialConstraint>
    	<Principal Role="Roles">
    	  <PropertyRef Name="RoleId" />
    	</Principal>
    	<Dependent Role="DocumentRole">
    	  <PropertyRef Name="ObjectId" />
    	</Dependent>
      </ReferentialConstraint>
    </Association>
    <Association Name="UsersDocumentUser">
      <End Type="aspnetdbModel.Users" Role="Users" Multiplicity="1" />
      <End Type="aspnetdbModel.DocumentUser" Role="DocumentUser" Multiplicity="*" />
      <ReferentialConstraint>
    	<Principal Role="Users">
    	  <PropertyRef Name="UserId" />
    	</Principal>
    	<Dependent Role="DocumentUser">
    	  <PropertyRef Name="ObjectId" />
    	</Dependent>
      </ReferentialConstraint>
    </Association>
    

    I was looking for a way to share documents between entities, for this reason I was looking for a n to n relation.

    So If I have understood it right, relations can't be mapped like in a table per hierarchy, although the xsd schema allows to add a condition in a association set mapping, it's a pity, it would have been a nice feature

    Tuesday, December 27, 2011 8:43 AM

All replies

  • Hi Casual Trash,

    Welcome to MSDN Forum!

    I'm doing research on this issue, it may need some time, I'll get back as soon as possible.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Saturday, December 24, 2011 8:22 AM
    Moderator
  • Hi Casual Trash,

    Build your DocumentObjects table in the database like this

     

    At your concept model, create the two models 'user' and 'role', then right click on each of the entities and select add - > 

    inheritance. At last, the conceptual model will like this,

     

    If you want to map two models to one table in the database, inheritance is a good choic. Here's a sample to query out the user's document

    var query = db.DocumentObjects.OfType<User>().ToList();
    

    But, the user and role have no n-n association, if you want to the two model have a n-n relationship, we may need to redesign about the database.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Monday, December 26, 2011 8:55 AM
    Moderator
  • Thanks Allen for the reply

    I'm sorry but I don't see how that could solve the problem, perhaps I have explained it wrong.

    The User and Role entities already exist and they are already mapped, I was trying to extend the model with an attached document entity to any already mapped entity. I have already solved it with a table per hierarchy on the document entity, but in this way is a 1 to n relation and the documents are unique for each entity.

    <Association Name="RolesDocumentRole">
      <End Type="aspnetdbModel.Roles" Role="Roles" Multiplicity="1" />
      <End Type="aspnetdbModel.DocumentRole" Role="DocumentRole" Multiplicity="*" />
      <ReferentialConstraint>
    	<Principal Role="Roles">
    	  <PropertyRef Name="RoleId" />
    	</Principal>
    	<Dependent Role="DocumentRole">
    	  <PropertyRef Name="ObjectId" />
    	</Dependent>
      </ReferentialConstraint>
    </Association>
    <Association Name="UsersDocumentUser">
      <End Type="aspnetdbModel.Users" Role="Users" Multiplicity="1" />
      <End Type="aspnetdbModel.DocumentUser" Role="DocumentUser" Multiplicity="*" />
      <ReferentialConstraint>
    	<Principal Role="Users">
    	  <PropertyRef Name="UserId" />
    	</Principal>
    	<Dependent Role="DocumentUser">
    	  <PropertyRef Name="ObjectId" />
    	</Dependent>
      </ReferentialConstraint>
    </Association>
    

    I was looking for a way to share documents between entities, for this reason I was looking for a n to n relation.

    So If I have understood it right, relations can't be mapped like in a table per hierarchy, although the xsd schema allows to add a condition in a association set mapping, it's a pity, it would have been a nice feature

    Tuesday, December 27, 2011 8:43 AM
  • Hi Casual Trash,

    Entity Framework is a set of new technologies in ADO.NET, it is on the way to improved. If you have good feature suggestions about it, please submit your suggestions here. With your valuable suggestion, I think the Entity Framework will be better and better.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Wednesday, December 28, 2011 5:39 AM
    Moderator
  • Thanks for the suggestion but it seems to me to be a drop in a ocean of suggestions, most probably it will be never read and voted, I save me the work to have to register, go through 10 forms, fill 200 useless fields, click 20 useless link, check for 10 useless confirmation emails to just write a suggestion which will never be read? Thanks for the suggestion but I stay away from such suggestions systems.
    Wednesday, December 28, 2011 12:53 PM
  • Hi Casual Trash,

    Don't worry about your suggestion will not be read, every valuable suggestion will be read and be taken seriously.

    Best Regards 


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Thursday, December 29, 2011 2:13 AM
    Moderator