0

Suppose the following database schema:

Table A: AId (PK)

Table B: BId (PK)

Table C: CId (PK)

Table AB: AId, BId (composite PK, FKs to A and B), Data

Table BC: BId, CId (composite PK, FKs to B and C), Data

Table ABC: AId, BId, CId, Data

In the database, ABC has two FKs: one to AB on AId and BId, and one to BC on BId and CId.

Use the EF Designer and attempt to create a Model from this database.

If you have Include foreign key columns in the model checked, it works; but having FK Columns in the model isn't very nice.

If you have Include foreign key columns in the model unchecked, only one of the FKs from ABC will be successfully mapped. To see what went wrong, you have to view the .edmx xml (thanks Craig!) and you see this error:

warning 6037: Foreign key constraint 'FK_ABC_BC' has been omitted from the storage model. Column 'BId' of table 'Model.Store.ABC' is a foreign key participating in multiple relationships. A one-to-one Entity Model will not validate since data inconsistency is possible.

I've read the only other mention of this problem I can find on SO, and I don't think this is the same problem. I can't see anything wrong at a database design level. I'm going to work round this for the time being by imposing surrogate keys on AB and BC, but what I'd really like to know is:

What possible data inconsistency is EF worried about happening here, if it created a model to match the database?

And is there anything I can do to persuade it that everything's going to be OK?

Community
  • 1
  • 1
AakashM
  • 62,551
  • 17
  • 151
  • 186

1 Answers1

1

My opinion is that EF is too clever in this scenario and it prevents you from using entity where you can assign only one relation and make the entity non-savable because relation to second entity will not exists.

There is also possibility that EF has some internal problem with tracking state of independent associations if more than one association is based on the same foreign key column but that is just another guess. Generally database features used to map EF features cannot be shared among multiple constructions. The only exceptions I can think about now are primary keys and in their own way discriminator columns.

I would like to mention that I don't like this type of relations in database at all.

Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670