CODEWITHSUNDEEP
entity-frameworkentity-framework-4
Adam Robinson
Adam Robinson

Reputation: 185643

Many-to-Many Relationships in EF4 with "Shared" columns

Disclaimer: Strictly speaking, what I have here is not a many-to-many relationship, but given that it's using an associative table, I figured it would be better to be slightly less accurate in order to give a better idea of what I'm doing.

I have the equivalent of the following three tables in my database:

Customer
---------
CustomerID PK
...

CustomerAddress
---------
CustomerID PK, FK -> Customer
AddressNo  PK
... address columns ...

CustomerPrimaryAddress
--------------
CustomerID PK, FK -> Customer
AddressNo      FK -> CustomerAddress (with CustomerID, so CustomerID 
                                      participates in both relationships)

If it's not obvious, the intent here is to allow for multiple addresses per customer, while designating at most one address per customer as "primary". I'm using an associative table to avoid placing a nullable PrimaryAddressNumber column on Customer, then creating a foreign key from Customer to CustomerAddress.

This is all well and good, but EF then places the CustomerPrimaryAddress entity in my model. Since its one and only purpose is to serve as an associative table, I have no need to represent this table in code. I removed the CustomerPrimaryAddress table from the conceptual model, then created an association between Customer and CustomerAddress like so:

Table          Customer   CustomerAddress
Multiplicity   1          0..1

I then mapped the association to use the CustomerPrimaryAddress table from the storage model, and all of the columns mapped just fine, or so I thought.

My issue is that now EF is complaining that CustomerID in CustomerPrimaryAddress is being mapped to two locations in my association, as it's mapped to both Customer and CustomerAddress.

Is there any way around this? I would prefer not to add a nullable column to Customer to represent the primary address number, as not only is this not a pleasant option from a DBA perspective, EF will also complain about having a cyclical relationship and I'll have to break inserts up in the code.

Upvotes: 3

Views: 180

Answers (1)

Craig Stuntz
Craig Stuntz

Reputation: 126547

Thinking out loud here:

Customer
---------
CustomerID PK
...

CustomerAddress
---------
AddressNo  PK
CustomerID FK -> Customer, non-nullable
... address columns ...

CustomerPrimaryAddress
--------------
CustomerID PK, FK -> Customer
AddressNo      FK -> CustomerAddress

This seems like it should get the cardinalities right, but I may have missed something.

Upvotes: 2

Related Questions