5

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.

Community
  • 1
  • 1
pbristow
  • 1,997
  • 4
  • 26
  • 46
  • 1
    you got a down vote so I assume I'm not the only one having a bit of trouble understand what you are after. can you just do `employee.managerId` or `employee.manager.id`? you have to know what the convention is, your DBA can't just simply change the DB without it affecting your project in other ways anyway. – Eonasdan Nov 14 '11 at 14:12
  • @Eonasdan - thanks. I was wondering why I was downvoted. I rewrote entirely to hopefully make it much more clear. Unfortunately now it's much longer, and I was trying to keep it short. – pbristow Nov 14 '11 at 14:56

3 Answers3

2

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.

AakashM
  • 62,551
  • 17
  • 151
  • 186
  • 1
    That's a good thought, but I need to get the name of the foreign key on the current object, not the key of the foreign object. It's the difference betwee myObject.ManagerID and myObject.Manager.ID. We don't use the convention of Manager.ManagerID - we use Manager.ID - so the columns are named differently on each end. – pbristow Nov 14 '11 at 16:26
1

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).

pbristow
  • 1,997
  • 4
  • 26
  • 46
0

The answer to the question is in another post here: Read foreign key metadata programatically with Entity Framework 4

Note the poster's answer didn't work for me. Use @Ashraf's answer, which worked great.

Community
  • 1
  • 1
pbristow
  • 1,997
  • 4
  • 26
  • 46