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

Question

My data model contains two tables with composite primary keys and an associative table. Part of the composite primary key is common to tables.

SitePrivilege ------------- SiteId PrivilegeId UserSite -------- SiteId UserId UserSitePrivilege ----------------- UserId SiteId PrivilegeId 

I created a SitePrivilege object and a UserSite object. I have mapped the many-to-many association between them in 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 causes this error:

Each of the following columns in the UserSitePrivilege table maps to several conceptual side properties: UserSitePrivilege.SiteId maps to UserSiteSitePrivilegeSitePrivilege.SiteId, UserSiteSitePrivilege.UserSite.SiteId

So I added a reference 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 causes this error:

Properties provided by the Principal The UserSite role must be exactly identical to the EntityType PrivilegeModel.UserSite key referenced by the Primary role in the relationship is a restriction on the PrivilegeModel.UserSiteSitePrivilege relationship. Ensure that all key properties are specified in the Primary Role.

How to simulate this relationship?

Answer 1

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 will be supported as Beta2 from EF 4. This is because FK associations (which are available in Beta2) are much more flexible than independent associations (which you have in versions 3.5 SP1 and 4.0 Beta 1).

For more details see this FK Association post.

In the meantime, your only option is probably to hide all this after DefiningQueries and CUD procedures, etc.

• Alex

Answer 2

If your primary key is a composite key, all of your relationships with the foreign key should also use the entire composite key (all key columns) for reference - I see no way around this, this is the basic principle of a relational database, really.

This is definitely one of the main reasons why I would probably prefer to use a replacement column in the main table for the primary key instead of a composite key made up of actual data columns.

UPDATE: yes, based on your comment, you are absolutely right - the database design is solid. Not quite sure why EF can't handle it ....

Mark