Composite primary keys which are also foreign keys in Entity Framework

Question

If I have 2 tables 1 with a composite primary key where one of the keys is also a foreign key in another table:

Table 1:

• A (PK, FK - maps to X in Table 2)
• B (PK)
• C

Table 2:

• X (PK)
• Y

Because A is both the PK in table 1 and FK in table 2, when I use EF to generate the entity model, I have both a Scalar AND a Navigation property for A in table 1. I cannot seem to remove A as a scalar (I think because it is a primary key).

The problem I am having is that if I create a table1Entity and set A's scalar property to a new value, A's navigation property will not be changed automatically (and vice versa).

Ideally I just want A to expose the navigation property - which is the way it behaves if A was not also part of the composite primary key anyway. Is there any way to achieve this?

Answer 1

Am I correct in assuming that Table1 derives from Table2? If so, I would do it like so:

(I'd also change the PK for both tables to the same name, since they probably have the same meaning - for the instance of this, I'll use the example ID)

• First, create the model with the default relationships (I usually just import the two tables from the database)
• In the designer, right click the base type, add inheritance, select the derived type.
• Delete the one to zero or one association
• Then, since the base type already has column ID, delete it from the derived type.
• Go to table mapping for the derived type, and map the ID property to the ID of the table.

Answer 2

Instead of mapping to table 1 directly, add a view to your database that's got all of table 1's fields, plus an extra copy of A (A2). Then, map the scalar key to A2 and the nav key to A.

(You'll run into a problem where if you use a view, Visual Studio can't find a primary key; fix this by manually editing the XML of the edmx file and adding a <Key><PropertyRef ... /></Key> to the <EntityType> for table A)

I know - it's hacky and horrible... but hey - it works!