none
Cannot find way to specify the following relationship in Entity Framework RRS feed

  • Question

  • I would like to create the following scenario in the Entity Framework and cannot find a way to achieve it.

    EntityA
       Id - int - Primary Key
       Instance - GUID - Unique Constraint

    EntityB
       Id - int - Primary Key
       Instance - GUID - Unique Constraint

    EntityProperties
       Id - int - Primary Key
       Instance - GUID
       Value - string

    The idea is that the EntityProperties has 0 or more extra values that are relevant to each instance in the EntityA and EntityB tables. So if I know the primary key of an EntityA row then I can use that to join on EntityProperties using the Instance value from EntityA to match the Instance values in EntityProperties. But because the Instance columns in EntityA/B are not primary keys the Entity Framework seems to choke and this. Any ideas how to fix this or an alternative that acheives the same thing?

    Thursday, June 7, 2012 5:31 AM

Answers

  • After simplified, I assume that the way you want to do is something like 'Group By GUID'  and try to get list of properties related to given GUID.

    If a GUID key is unique across those primary tables [both EntityA and B], you can simply create primary~foreign relationships between the primary tables and property table. If it is not, I rather suggest you to include one more grouping field in property table and just do query with 'Group By' statement.

    Hope this helps.

    Friday, June 8, 2012 8:01 AM

All replies

  • Hi Philip Wright,

    Welcome to MSDN Forum.

    Based on the issue, my understanding is you want to store all the properties of EntityA and EntityB into EntityProperties, and if you want to access the properties, pass the 'Instance' to EntityProperties table to query. If I understand correctly. Could you please why you want to achieve this relationship? I don't think joining table everytime when you want to get some properties is a good way.

    Even though Instance is not the primary key, we can also use it for joining tables. I'm not clear about the issue why you said "Entity Framework seems to choke this". Or I misunderstand your goal?

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, June 7, 2012 9:03 AM
    Moderator
  • The scenario is that I have multiple entities such as Person, Order, Contact and the like and they all have a fixed set of properties. However, the business application needs to be customizable so that the operator can add extra data to each Person/Order/Contact on a per instance basis. This could be implemented by simply having an extra table per entity that contains the extra values for that entity. So Person and PersonExtra, Order and OrderExtra and so forth. This is simple enough and I can certainly take this approach.

    But I thought it might be more efficient to just have a single Extras table that has the extra values for any of the Person/User/Contact etc. This is why I was using a GUID on each of the Person/Order/Contact tables, so that they would have unique values across tables so that the Extras table would only need to store a GUID to indicate which Person/Order/Contact it belongs to.


    Friday, June 8, 2012 1:32 AM
  • After simplified, I assume that the way you want to do is something like 'Group By GUID'  and try to get list of properties related to given GUID.

    If a GUID key is unique across those primary tables [both EntityA and B], you can simply create primary~foreign relationships between the primary tables and property table. If it is not, I rather suggest you to include one more grouping field in property table and just do query with 'Group By' statement.

    Hope this helps.

    Friday, June 8, 2012 8:01 AM