实体框架-反映外键和关系/关联

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.

根据SO上的另一篇文章，我可以通过反射很容易地找到表上的导航属性。但这并没有给出包含外键值的属性或列的名称。

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:

我这样做的原因是我有大量的表(将近40个)，它们引用一个项目表。假设一个用户在名为“Andew”的items表中输入了一个新值，然后一个管理员注意到，它实际上只是已经存在的项“Andrew”的一个输入错误。现在我想查找所有关于“Andew”的引用，并将这些引用改为“Andrew”。我更喜欢这样做，所以使用反向导航属性将会很慢，因为在修改它们之前必须加载这些值。我想做的是能够反映表和列的列表，然后直接向数据库发出更新命令。大概是这样的:

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.

在LINQ to SQL中，这其实很简单……LINQ自动生成的代码有20行，我完成了，但是我们最近切换到EF，我无法通过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.

我正在寻找的一个简化示例:如果我有一个名为Employee的对象，它具有一个名为Manager的导航属性和一个名为ManagerID的外键，那么我想知道Manager是我的导航属性，底层存储是ManagerID属性。我想通过反射或元数据来严格地实现这一点，这样我就可以从它构建一个dynmaic查询。

3 个解决方案

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

一旦您使用了链接问题中的思想来获得感兴趣的EntityType，请注意EntityType继承自EntityTypeBase，它有一个属性KeyMembers，它是参与实体键的所有EdmMembers的集合。

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

每个EdmMember都有一个名称，该名称将是您要查找的字符串“ManagerID”。

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

为了节省时间，我想指出这不是正确的答案，但是您可以通过SQL的系统视图来做我要求的事情。我尝试过这个方法，它很有效，但是让我烦恼的是，我可以通过LINQ到SQL很容易地获得这些数据，但是我在EF根本找不到。如果没有其他选择，我将不得不使用下面的解决方案。(但EF必须有内部的数据……)我只是想访问它)

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

希望发布这个错误的答案至少对我以外的人有用……(同样，只是FYI，这个解决方案需要更多的代码来处理多列PKs——我的都是单列的)。

#3

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

问题的答案在这里的另一篇文章中:用实体框架4程序化地读取外键元数据

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

注意海报上的回答对我不起作用。使用@Ashraf的答案，效果非常好。

#1