Entity Framework 4 - Associations

EF 4 does not support associations that are not based on foreign/primary keys. There are lots of occasions where you need to navigate between tables using non-key fields.

For example, a medications table has:

medID (PK)
medGrouperID
medName

an ingredients table has:

ingredientID (PK)
ingredientName

and a link table has:

medGrouperIDID (PK)
ingredientID (PK)

In this case EF doesn't let me create an association between medications and the link table as neither has a foreign key for the other.

I haven't done any db design for a long time so i'm struggling to understand th开发者_如何学Gois restriction; Is this following good database design or a failure of EF?

EDIT

The MedGrouperID allows all medications with the same ingredients, uses, instructions etc to be grouped together. This removes the duplication of data that would exist if every medicine had its own set of records for ingredients, uses etc.

Adding a new MedGrouper table will work but this would neccessitate an additional join which can't be good for performance.

Medication

* MedID (PK)
* MedGrouperID (FK)
* MedName

MedGrouper * MedID (FK) * MedGrouperID (PK)

Ingredient

* IngredientID (PK)
* IngrediantName

MedicationIngredients (junction/join table)

* MedGrouperID (PK, FK)
* IngredientID (PK, FK)

/EDIT

---

Is this following good database design or a failure of EF?

It's not a failure of EF, it's an incorrect database design (sorry for the bluntless). That design would not work from a pure database query perspective, therefore you cannot expect EF to work it out either.

It appears you have a *..* between Medications and Ingredients.

Therefore you should have the following tables:

Medication

• MedID (PK)
• MedGrouperID
• MedName

Ingredient

• IngredientID (PK)
• IngrediantName

MedicationIngredients (junction/join table)

• MedID (PK, FK)
• IngredientID (PK, FK)

If you generate a model from that using Entity Framework, EF will create the two entities with the many to many association present, and the join table will not have to be mapped at all.

The thing you have to remember is relationships are based on referential integrity, which you did not have correctly setup in your current database schema.

HTH.