Many-to-many relationship with surrogate key in Entity Framework

Question

Entity Framework magically interprets the following table structure as a many-to-many relationship.

table foo (int id)
table foo_bar (int foo_id, int bar_id)
table bar (int id)

But if the join table has any additional fields it will instead be interpreted as two one-to-many relationships.

I am using a database in which the join table has a surrogate key as primary key. Because of this EF interprets it as two one-to-many relationships.

table foo (int id)
table foo_bar (int surrogate_pk, int foo_id, int bar_id)
table bar (int id)

Is it possible to modify EF:s interpretation to make it an actual many-to-many relationship in the model? Can it be done using the designer?

score 2 · Answer 1 · answered Jan 04 '11 at 20:15

it's possible, but it requires quite a bit of manual work in the EDMX file, and I haven't been able to make EF use the surrogate key as actual primary key on the link table. You have to make EF use a combination key of both foo_id and bar_id as primary key.

in your storage model you have to change the EntityType of the link table from

<EntityType Name="foo_bar">
    <Key>
        <PropertyRef Name="surrogate_pk" />
    </Key>
    <Property Name="surrogate_pk" Type="bigint" Nullable="false" StoreGeneratedPattern="Identity" />
    <Property Name="foo_id" Type="int" Nullable="false" StoreGeneratedPattern="None" />
    <Property Name="bar_id" Type="int" Nullable="false" StoreGeneratedPattern="None" />
</EntityType>

to:

<EntityType Name="foo_bar">
    <Key>
        <PropertyRef Name="foo_id" />
        <PropertyRef Name="bar_id" />
    </Key>
    <Property Name="foo_id" Type="int" Nullable="false" StoreGeneratedPattern="None" />
    <Property Name="bar_id" Type="int" Nullable="false" StoreGeneratedPattern="None" />
</EntityType>

So you make the surrogate key invisible to EF, and tell it to use the combination of the two foreign keys as primary key.

In your conceptual model, you need to have the many-many association defined:

<Association Name="foo_bar_association">
    <End Role="foo" Type="foo" Multiplicity="*" />
    <End Role="bar" Type="bar" Multiplicity="*" />
</Association>

and in your mappings, an AssociationSetMapping:

<AssociationSetMapping Name="foo_bar_association" TypeName="foo_bar_association" StoreEntitySet="foo_bar">
    <EndProperty Name="foo">
        <ScalarProperty Name="id" ColumnName="foo_id" />
    </EndProperty>
    <EndProperty Name="bar">
        <ScalarProperty Name="id" ColumnName="bar_id" />
    </EndProperty>
</AssociationSetMapping>

By far the easiest way to get this right, is to remove the surrogate key from the db, generate the EDMX, and then put this model on your original DB. The result will be the same. EF doesn't really need the surrogate key for anything, the table is invisible in a many-many association

I can't say I've tested it, but this answer looks accurate. – Luke Puplett Jun 12 '12 at 14:52 — Luke Puplett, Jun 12 '12 at 14:52

score 1 · Accepted Answer · answered Feb 16 '09 at 20:47

1

I am positive that this cannot be done using the designer. I don't know if there is a way to do it in EDMX manually, but I have never seen an example of it. One possible workaround might be to not map the surrogate key at all. If you can generate that on the database, you might not need it in your model.

answered Feb 16 '09 at 20:47

Craig Stuntz

125,891
12
252
273

The surrogate key is of no real interest, so that would work nicely. Just deleting the field from the model does not seem to make any difference. Could it be excluded in a more definite way or something? – Erik Öjebo Feb 16 '09 at 20:55
Deleting the surrogate key in the designer will not remove it from the storage model. You'll have to manually edit the EDMX for that. – Craig Stuntz Feb 16 '09 at 21:29

Many-to-many relationship with surrogate key in Entity Framework

2 Answers2