In Entity Framework, how can I create a referrential constraint using a subset of the primary key?

entity-framework

Solution

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

Problem

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?

Original source