Mapping composite foreign keys in a many-many relationship, with overlapping components.
- by Kirk Broadhurst
I have a Page table and a View table. There is a many-many relationship between these two via a PageView table. Unfortunately all of these tables need to have composite keys (for business reasons).
Page has a primary key of (PageCode, Version),
View has a primary key of (ViewCode, Version).
PageView obviously enough has PageCode, ViewCode, and Version.
The FK to Page is (PageCode, Version) and the FK to View is (ViewCode, Version)
Makes sense and works, but when I try to map this in Entity framework I get
Error 3021: Problem in mapping
fragments...: Each of the following
columns in table PageView is mapped to
multiple conceptual side properties:
PageView.Version is mapped to
(PageView_Association.View.Version,
PageView_Association.Page.Version)
So clearly enough, EF is having a complain about the Version column being a common component of the two foreign keys.
Obviously I could create a PageVersion and ViewVersion column in the join table, but that kind of defeats the point of the constraint, i.e. the Page and View must have the same Version value.
Has anyone encountered this, and is there anything I can do get around it? Thanks!
