In Entity Framework, how can I create a referrential constraint using a subset of the primary key?
entity-framework
Question
My data model contains two tables with composite primary keys and an associative table. Part of the composite primary key is common between the tables.
SitePrivilege
-------------
SiteId
PrivilegeId
UserSite
--------
SiteId
UserId
UserSitePrivilege
-----------------
UserId
SiteId
PrivilegeId
I have created a SitePrivilege entity and a UserSite entity. I have mapped a many-to-many association between them to UserSitePrivilege.
<Association Name="UserSiteSitePrivilege">
<End Type="PrivilegeModel.UserSite" Multiplicity="*" Role="UserSite" />
<End Type="PrivilegeModel.SitePrivilege" Multiplicity="*" Role="SitePrivilege" />
</Association>
...
<AssociationSetMapping Name="UserSiteSitePrivilege" TypeName="PrivilegeModel.UserSiteSitePrivilege" StoreEntitySet="UserSitePrivilege">
<EndProperty Name="SitePrivilege">
<ScalarProperty Name="PrivilegeId" ColumnName="PrivilegeId" />
<ScalarProperty Name="SiteId" ColumnName="SiteId" />
</EndProperty>
<EndProperty Name="UserSite">
<ScalarProperty Name="SiteId" ColumnName="SiteId" />
<ScalarProperty Name="UserId" ColumnName="UserId" />
</EndProperty>
</AssociationSetMapping>
The above code produces this error:
Each of the following columns in table UserSitePrivilege is mapped to multiple conceptual side properties: UserSitePrivilege.SiteId is mapped to UserSiteSitePrivilegeSitePrivilege.SiteId, UserSiteSitePrivilege.UserSite.SiteId
So I added a referential constraint.
<Association Name="UserSiteSitePrivilege">
<End Type="PrivilegeModel.UserSite" Multiplicity="*" Role="UserSite" />
<End Type="PrivilegeModel.SitePrivilege" Multiplicity="*" Role="SitePrivilege" />
<ReferentialConstraint>
<Principal Role="UserSite">
<PropertyRef Name="SiteId"/>
</Principal>
<Dependent Role="SitePrivilege">
<PropertyRef Name="SiteId"/>
</Dependent>
</ReferentialConstraint>
</Association>
...
<AssociationSetMapping Name="UserSiteSitePrivilege" TypeName="PrivilegeModel.UserSiteSitePrivilege" StoreEntitySet="UserSitePrivilege">
<EndProperty Name="SitePrivilege">
<ScalarProperty Name="PrivilegeId" ColumnName="PrivilegeId" />
<ScalarProperty Name="SiteId" ColumnName="SiteId" />
</EndProperty>
<EndProperty Name="UserSite">
<ScalarProperty Name="SiteId" ColumnName="SiteId" />
<ScalarProperty Name="UserId" ColumnName="UserId" />
</EndProperty>
</AssociationSetMapping>
Now it produces this error:
Properties referred by the Principal Role UserSite must be exactly identical to the key of the EntityType PrivilegeModel.UserSite referred to by the Principal Role in the relationship constraint for Relationship PrivilegeModel.UserSiteSitePrivilege. Make sure all the key properties are specified in the Principal Role.
How do I correctly model this relationship?
Accepted Answer
Overlapping FKs like this are not supported in 3.5 SP1.
i.e.
UserSitePrivilege
----------
UserId
SiteId
PrivilegeId
PK => UserId, SitedId, PrivilegeId
FK1 => UserId, SiteId
FK2 => SiteId, PrivilegeId
FK1 overlaps with FK2. This is going to be supported as of Beta2 of EF 4. This is because FK Associations (which is available in Beta2) are much more flexible than Independent Associations (what you have in 3.5 SP1 and 4.0 Beta 1).
See this post for more on FK Associations
In the meantime your only option is probably to hide all of this behind DefiningQueries and CUD procedures etc.
- Alex
Popular Answer
If your primary key is a compound key, all your foreign key relationships must also be using the entire compound key (all key columns) for reference - I don't see any way to get around this, this is a basic tenet of relational database design, really.
This is definitely one of the major reasons I would probably choose to use a substitute column on the main table for the primary key, instead of a compound key made up from actual data columns.
UPDATE: yes, based on your comment, you're absolutely right - the DB design is solid. Not quite sure why EF can't deal with this....
Marc
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
