Handling lookup tables in Entity Framework

Question

I am just getting started with Microsoft's Entity Framework, using it for an MVC project since MS seems to be really pushing it, and I'm running into some issues. In my database there are multiple lookup tables tied to a single table via foreign keys. Within the entity framework I am trying to combine these into one so that I have a simplified single view for this data in my model. However, this doesn't seem possible from the designer view. Is there something obvious I'm missing? Is there a way that I can edit the edmx file manually to produce this sort of model?

Answer 1

At the moment, Foreign keys and lookup tables in Entity Framework are a PAIN.

EF with LINQ makes getting your data super-easy, and on the surface it looks easy to update, but with lookup tables things get difficult (for now... read on...)

I'm not sure how you would "combine" your lookup tables into a single table. If each table contains a different type of "lookup entity" then IMHO they should be represented separately in your EDM. I'm guessing you're having headaches updating a record's foreign keys to the lookup tables. That's because it is a headache.

Changing foreign key values:

MyDBEntities _db = new MyDBEntities();
//get a Person
MyDBEntities.Person person = (from p in _db.Persons
                      where p.Id = 1
                      select p).First();
// This sets the foreign key value in the Person table on the PersonType field
person.PersonTypeReference = new EntityKey("MyDBEntities.PersonType", "PersonTypeId", 3)

The next release version of the Entity Framework will have a new concept called "FK Associations." This will bring back the sanity of setting the foreign key value directly rather than having to create and set an EntityKey.

HTH.