How can I use a subset of the main key to establish a referrential constraint in Entity Framework?

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?

1
4
6/17/2009 8:09:00 PM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

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
4
6/17/2009 10:45:00 PM

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

marc_s

Related Questions

Violation of PRIMARY KEY constraint in Entity Framework code first link table

I have a User table and a Roles table. There is a automatically generated UsersRoles link table which contains the Id from the User and Roles tables. This is generated using the following code:...modelBuilder.Entity<User>() .HasMany(u => u.Roles) .WithMany(r => r.Users) .Map(c => { ...
.net c# ef-code-first entity-framework
asked by 97ldave

Code Generation : Add primary key / foreign key to Entity Framework

I'm using EF with database first approach....I would like to add attribute to the generated code to mark property as primary key / foreign key when it's the case....For example, if I have a table ...User... in my database...CREATE TABLE User ( Id int PRIMARY KEY ); ...I would like my generated code to include an attribute that indicate that ...I...
c# code-generation entity-framework-6
asked by Gudradain

Entity Framework creating duplicate objects from list when object has composite Primary Key

I have an object with a child collection as such:...public class ClaimGroup : BaseModel { public int Id { get; set; } [Required,StringLength(100)] public string Name { get; set; } public int Order { get; set; } public bool Include { get; set; } public virtual ICollection<ClaimGroupItem> Items { get; set; } } ...T...
asp.net-mvc-5 c# entity-framework-6 lazy-loading
asked by Rhys Stephens

primary key constraint updating many-to-many self referential table in entity framework code-first

TL;DR... What is the proper way of rehydrating an entity framework object with a self referential many to many relationship from a DTO and updating it with the new values so that the database updates correctly?...I have the following entity (irrelevant stuff trimmed)... public class Role { [Key] [Required] public String ...
asp.net-mvc asp.net-web-api entity-framework entity-framework-6 sql-server
asked by Jason Coyne

create non clustered index on primary key entity framework 6.0

I am aware of ...this..., which states that it is not possible to create a primary key with non clustered index via code first. Is this still the case?...Ideally, I would like to specify via EntityTypeConfiguration, that my primary key (Guid) has a non-clustered index and there is another column (int) with a clustered index.
entity-framework entity-framework-6
asked by cs0815

Violation of PRIMARY KEY constraint with Entity Framework 6 , Model one-to-many

I have 2 models with one to many relation, if I save in different instance of ...DbContext..., this throw an exception (Violation of Primary Key) - how to avoid it?...public class Customer { public Guid Id { get; set; } public string Name { get; set; } } public class User { public Guid Id { get; set; } public string Name { get; set...
c# code-first entity-framework-6
asked by mchouteau

Entity Framework code-first set foreign key with primary key in same table

I have this ...WdUser... class inherited from ...SysModelBase...:...[Serializable] public class WdUser : SysDomainModelBase, IWdUser { [Display(Name = "UserName")] [MaxLength(25)] [Required] public string UserName { get; set; } [Required] [Index(IsUnique = true)] [MaxLength(25)] [Display(Name = "LoginName ")] pu...
asp.net c# entity-framework-6
asked by Autyan

Entity Framework, Load multiple entities by primary key in a single query while still using caching

I have a list of primary key ids for a table whose entities I want to pull into EF memory, modify, and then save....With a single id you would do something like...var entity = dbContext.Entity.Find(id); entity.SomeColumn = "something"; dbContext.SaveChanges() ...Currently I'm doing something like this to pull all the entities to be modified in on...
.net c# entity-framework entity-framework-6
asked by Slight

Entity Framework 6 creates Id column even though other primary key is defined

I defined a DataObject as:...public class SensorType : EntityData { //PKs public string CompanyId { get; set; } public string ServiceId { get; set; } public string Type { get; set; } } ...And used fluent API to make CompanyId and ServiceId a composite key:...modelBuilder.Entity<SensorType>() .HasKey(t => new { t.Company...
c# data-annotations ef-fluent-api entity-framework entity-framework-6
asked by Emil Nyborg

Entity Framework 6.x Code First PRIMARY KEY constraint error saving Many to Many field

I am using Code First and I'm trying to add 2 records that share the same (facet) object. EF is trying to add the same shared object twice so I get the ...Violation of PRIMARY KEY constraint... error. Consider this.....XML...<RecipeSet xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <Recipes...
automapper c# entity-framework-6 many-to-many
asked by duyn9uyen

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow