Multiple relationships with lookup tables in Entity Framework

Question

The context of the question is ASP.NET MVC2 / EF4. I have a number of lookup tables in the database which have previously been programmatically referenced, each from a number of other tables... So there might be a country table with country-id shortcode, desc, and half a dozen tables that have a countryid in them. Repeat for 5 or 6 other lookup tables. At the moment there are no FK's - the lookup has been programmatic in the UI of the old database frontend.

The other thing to be wary of is that a person may have an countryid. The person may be related to a job which may also have a countryid. The job has an employer with a countryid (and on and on) - i.e. we end up with a pretty tangled graph.

Onto the question: I would like to be able to reach the country information in EF / LINQ queries without joins. Should I add the multiple FK's to the database first ? Do I just wire these up the relationships in the designer ? What (if anything) do I need to look out for when having multiple tables related to the same table in EF? Will this work at all ?

Just basically looking for best practice in doing this.

Answer 1

Create the FK first, update the model from the database in the EF Designer. It will create the associations between your entities from the FK relationships automatically.

It will work just fine.

The one caveat is that EF will connect up your graph both ways and this can cause issues. For example, you create some temporary Person object and set the Country on it to an existing Country entity. If that Country entity is also part of some other entity that has been changed in the current context then, when you save changes your temporary Person object may be added to the database too! EF's graph building can get a little over-eager sometimes.