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.

Answer 1

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.

Answer 2

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.