Entity framework specify string for dependant column without database key

Question

In EF is there a way to specify a relationship between two tables when there is no relationship defined in the database and one of the related columns is a specific string/hard-coded value?

Lets say I have a Document object and it can have various stages of approval and a category. My table might look like

DocumentID, DocumentName, DocumentState, DocumentCategory

With the following Document data:

1, Some Test Document, 0, 0

2, Another Doc, 2, 1

I have a Key/lookup table in the database and the table might look like:

LookupKey, LookupValue, LookupText

With the following data where LookupKey and LookupValue are the primary key (not defined in the db):

DocumentStatus, 0, Draft

DocumentStatus, 1, InReview

DocumentStatus, 2, Final

DocumentCategory, 0, Resume

DocumentCategory, 1, Cover Letter

The tables have two relationships based on:

DocumentStatus = LookupValue AND LookupKey = "DocumentStatus"

And a second relationship

DocumentCategory = LookupValue AND LookupKey = "DocumentCategory"

Can I define this type of relationship within the EDMX?

Answer 1

Well, if I were in your shoes, I would change the primary key of the lookup table to be a compound key on the lookupKey and LookupValue. But let's presume you are unable to change the structure of the database.

Yes, you can do this. Essentially, you're going to edit the SSDL section of the EDMX in such a way that it will be the same as the GUI designer would've produced if you had an actual foreign key. One way to do this is to look at an association based on a real, compound foreign key, and simply type that structure into your EDMX. However, there is a downside of doing this manually. When you choose "Update model from database" in the GUI designer, it will replace the complete SSDL section every time. This would wipe out your changes. Therefore, an easier method of doing this is to have a separate database, with a real foreign key to a compound primary key in your lookup value table, which you will use for model generation. These separate database is the same structure as your runtime database, except that it defines this bit of schema "more correctly." You just change the connect string before you update model from database, generate the model, and then change the connect string back to the "real" database for runtime.