Mapping composite foreign keys in a many-many relationship in Entity Framework

Question

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!

Answer 1

I'm not aware of a solution in Entity Framework for this problem, but a workaround could be to add primary key columns to your tables and add a unique constraints on the fields you wanted to act like a composite key. This way you ensure uniqueness of your data, but still have one primary key column. Pro-con arguments can be found under this topic: stackoverflow question

Cheers

Answer 2

After much reading and messing about, this is just a limitation of the EF designer and validator when working with many-many relationships.

Answer 3

I was going to write that you should use a surrogate key, but I don't think this will actually help you. The join table is enforcing a business rule basedon the logical attributes of the entities - these same attributes would be stored in the join table even if Page and View were augmented with surrogate keys.

If you are executing on a server that supports constraints, you could separate the Version into PageVersion and ViewVersion and add a constraint that the two are equal, or use an INSERT/UPDATE trigger to enforce this.

I may have simply misunderstood the intent, but I feel there is something that doesn't seem right with this design. I can't imagine how the versioning will work as pages and views are changed and new versions created. If changing a page means it gets a new version, then it will also have cause new versions of all it's views to be made, even for views that haven't changed in that version. Equivalently, if one view in a page changes, the view's version changes, which means the page's version must also change, and so all other views in that page, since page and view versions must match. Does this seem right?

Answer 4

Consider using nHibernate? :) - or at least for anything more than simple joins in your DB. Im working with EF4 and it doesnt seem mature enough for complex data graphs IMO, at the moment. Hopefully it will get there though!