Entity Framework Association with Non Key fields

Question

Is it possible to create associates b/t 2 non-key fields in the Entity Framework?

Example: Take the 2 tables in a legacy application (i.e. keys/structure cannot change)

Order (
    OrderId : int : PK
    OrderNo : varchar
)

OrderDetails (
    DetailRecordId : int : PK
    OrderNo : varchar
)

In the Entity Framework, I want to create an association b/t Order and OrderDetails by the OrderNo field, which is not a primary key on either table or a FK relationship in the database.

This seems to me as not only should it be easy to do, but one reasons to use something like EF. However, it seems to only want to allow me to create associations using entity keys.

score 6 · Accepted Answer · edited Feb 15 '13 at 14:03

6

The Entity Framework allows you to claim that columns are keys and that FK constraints exist where none actually exist in the database.

That is because the SSDL (StorageModel part of the EDMX) can if necessary be manipulated by you and lie about the database.

The EF will then interact with the database as if the keys and foreign keys really do exist.

This should work, but all the normal caveats about referential integrity apply.

See my Entity Framework Tips

Hope this helps.

edited Feb 15 '13 at 14:03

rae1

6,066
4
27
48

answered Nov 12 '09 at 02:39

Alex James

20,874
3
50
49

+1 So does this mean that I have to manually edit thge .edmx file? – Cody C Nov 12 '09 at 14:20
Thanks again. One last question. If I do edit the EDMX, how will that work when I want to use the wizard to update the model? Will my custom changes be persisted or once I start down the "Edit EDMX" route, am I forced to make all changes manually? – Cody C Nov 12 '09 at 15:27
Unfortunately changes in the SSDL portion of the EDMX are overwritten when you refresh the model from the database. So yes you will need to make the changes again. However if you are using the tools to modify the conceptual model using the designer, your custom SSDL bits shouldn't be touched. – Alex James Nov 12 '09 at 16:59

score 1 · Answer 2 · answered Nov 11 '09 at 20:41

1

The problem with using non-key fields to define relationships is that the keys are not guaranteed to be properly navigatable. That could lead to a situation where you have a one to one relationship between two entities where there are more than one possible rows that fufill the relationship.

...when relating data from a database, the relationships should always be based on keys. The keys enforce the referential integrity.

answered Nov 11 '09 at 20:41

Justin Niessner

242,243
40
408
536

3

The question is though will it allow it. let's say I'm 100% certain this wouldn't happen...will EF allow me to make the relationship even though it obviously isn't good practice? – Cody C Nov 11 '09 at 21:00

score 0 · Answer 3 · answered Oct 30 '13 at 20:49

0

One more workaround:

create view vOrder which will not include PK and create Entity from it. Set PK in this entity to OrderNo

Now you will be able create association

answered Oct 30 '13 at 20:49

parfilko

1,308
11
12

Entity Framework Association with Non Key fields

3 Answers3

Linked