Entity Framework - Reflecting on Foreign Keys and Relationships/Associations

Question

I have a challenging problem where I would like to use reflection on my Entity Framework mapping to find all the foreign keys that reference a table, and I want the names of the columns that are the foreign keys.

According to another post on SO, I can easily find the navigation properties on a table through reflection. But this doesn't give me the name of the property or column that contains the foreign key value.

The reason I'm trying to do this is that I have a large number of tables (nearly 40) that reference one table of items. Let's say a user enters a new value in the items table called "Andew" and later an admin notices it's actually just a typo for the already-existing item "Andrew". Now I want to find all references to "Andew" and change those references to "Andrew". I would prefer to do this effeciently, so using the reverse-navigation properties would be too slow since you have to load the values before modifying them. What I would like to do is be able to reflect a list of tables and columns, then issue update commands directly to the database. It would look something like this:

var command = String.Format("UPDATE [{0}] SET [{1}] = {{1}} WHERE [{1}] = {{0}}; ", fk.FromTableName, fk.FromColumnName);
dataContext.ExecuteStoreCommand(command, new Object[] { oldID, newID });

In LINQ to SQL this was actually pretty easy... 20 lines of reflection on the LINQ auto-generated code and I was done, but we switched to EF recently and I can't find the names of the foreign key columns through EF.

A simplified example of what I'm looking for: if I have an object called Employee with a navigation property called Manager and a foreign key of ManagerID, then I want to know that Manager is my navigation property and the underlying storage is the ManagerID property. I want to do this strictly through reflection or metadata so I can build a dynmaic query from it.

Answer 1

Once you've used the ideas in the linked question to get to the EntityType you're interested in, note that EntityType inherits from EntityTypeBase, which has a property KeyMembers which is a collection of all the EdmMembers that participate in the entity's key.

Each EdmMember has a Name which will be the string "ManagerID" that you seek.

Answer 2

Just to save time, I want to throw out this is not the correct answer, but you can do what I'm asking through system views in SQL. I have tried this and it works, but it botheres me I could get this data so easily through LINQ to SQL, yet I can't find it in EF at all. If there's no alternative I'll have to use the below solution. (But EF has to have this data internally somewhere... I just want access to it.)

select K.name as RelationshipName, T1.name as FromTable, C1.name as FromColumn, T2.name as ToTable, C2.name as ToColumn
from sys.foreign_keys as K
join sys.foreign_key_columns as C on K.object_id = C.constraint_object_id
join sys.columns as C1 on K.parent_object_id = C1.object_id 
join sys.tables as T1 on K.parent_object_id = T1.object_id 
join sys.columns as C2 on K.referenced_object_id = C2.object_id
join sys.tables as T2 on K.referenced_object_id = T2.object_id 
where C1.column_id = C.parent_column_id
and C2.column_id = C.referenced_column_id
and T2.Name = 'Employee'
order by T1.Name, C1.Name

Hopefully posting this wrong answer is at least useful to someone other than me... (also, just FYI, this solution needs a lot more code to work with multi-column PKs - mine are all single-column).