ASP.NET MVC 3.0 2外键来自同一个表例外 [英] ASP.NET MVC 3.0 2 Foreign Keys from the same table exception

问题描述

我有两个表和模型对应于每个表：员工和EmployeeEducation
在EmployeeEducation我从Employee表2的外键：ID为顾问，ID为谁拥有实际的教育员工。每个教育可以有不同的顾问。

  [必需（的ErrorMessage =联系管理员）] 
 [显示（名字=顾问）] 
公众诠释？ ConsultantId {搞定;组; } 
 *强调文本* 
 [必需（的ErrorMessage =联系管理员）] 
公众诠释？雇员{搞定;组; } 
  

有关每个ID我有这些对象到达目标

  [ForeignKey的（雇员）] 
公共虚拟员工员工{搞定;组; } 
 
 [ForeignKey的（ConsultantId）] 
公共虚拟员工顾问{搞定;组; } 
  

当我运行的代码，并尝试进入教育与咨询顾问的员工它给了我在与内部异常的异常。

 在执行命令定义EntityCommandExecutionException 
 {时发生错误。见内例外详细信息} 
 
内部异常：SqlCeException 
 {列名无效[节点名称（如果有的话）= Extent1，列名= EMPLOYEE_ID]} $ b。 $ b  

但是，当我删除顾问对象它不给例外。 ？
我该如何解决这个问题，使我有机会获得这两个顾问和员工本身

唯一的例外发生在DetailsEducation.cshtml：

  @ {如果（Model.EducationList == NULL ||！Model.EducationList.Any（））
 {
  

下面是EducationList的填充方式：

 公众的ActionResult DetailsEducation（INT ID）
 {
员工员工= _work.EmployeeRepository.GetSet（）
 .INCLUDE（A => a.EducationList）
 .INCLUDE（A => a.EducationList.Select（C => c.University））
 .INCLUDE（A => a.EducationList.Select（C => c.Department））
 .FirstOrDefault（一个= GT; a.Id == ID）; 
返回PartialView（_ DetailsEducation，员工）; 
} 
  

解决方案

列名= EMPLOYEE_ID

在实体框架创建了一个具有外键的SQL查询（意外）强调在其列名几乎总是一个指标，EF推断按约定的关系，这是另外一个比你用注释或流畅API定义的关系。

本外键无法从 EmployeeEducation.Employee 和 EmployeeEducation.Consultant 导航属性，因为对于那些您已经定义由来与数据的注解外键名称 [ForeignKey的（雇员）] 和 [ForeignKey的（ConsultantId）]

现在，如何检测EF的关系？它会检查你的模型类的导航性能。我们已经知道， EmployeeEducation.Employee 和 EmployeeEducation.Consultant 不能成为问题，所以必须有三分之一导航属性某处。属于该导航属性的关系，必须在 EmployeeEducation 的关联到底是因为EF显然推断出额外的外键的需要 EMPLOYEE_ID 在 EmployeeEducation 表

由于名称 - 员工 _id - 这个导航属性将在你的类员工。看你的包括：（a => a.EducationList）您似乎在员工集合属性：

 公共SomeCollectionType< EmployeeEducation> EducationList {搞定;组; } 
  

这集是最有可能导致第三次外键。如果你曾在 EmployeeEducation 只有一个导航属性，例如仅 EmployeeEducation.Employee ，就不会出现问题，因为EF就推断在这种情况下 Employee.EducationList 和 EmployeeEducation.Employee 是一对单的关系导航属性。

如果你的两个的导航属性都指的是员工 EF不能决定其中两人在收集员工所属。而不是选择一个以任何规则，它选择的没有人，并假定集合属于第三者的关系。

要解决这个问题，必须EF给出提示，其中在 EmployeeEducation 两个引用您希望通过使用一个 [InverseProperty] 属性相关的集合，例如属性（但不能同时）的：

  [ForeignKey的（雇员），InverseProperty（EducationList）] 
公共虚拟员工员工{搞定;组; } 
 
 [ForeignKey的（ConsultantId）] 
公共虚拟员工顾问{搞定;组; } 
  

注意： EducationList 将只包含 EmployeeEducation S中的给定雇员是员工，但并未顾问。对于您需要在员工与的 [InverseProperty] 标注第二集合属性顾问这个时候。一般来说，你可以不关联的有一个的导航集于一体的实体的两个的导航中的其他实体引用。你唯一的选择要么是两个集合或没有收藏的。 （在后一种情况下，您的问题都会消失，以及通过的方式，但你不会有一个导航属性了，你可以有。）

I have two tables and models corresponding to each table: Employee and EmployeeEducation In EmployeeEducation I have 2 foreign keys from the table Employee: Id for consultant and id for actual employee who owns the educations. Each education can have different consultant.

[Required(ErrorMessage = "Contact Admin")]
[Display(Name = "Consultant")]
public int? ConsultantId { get; set; }
*emphasized text*
[Required(ErrorMessage = "Contact Admin")]
public int? EmployeeId { get; set; }

For each id ı have these objects to reach the objects

[ForeignKey("EmployeeId")]
public virtual Employee Employee { get; set; }

[ForeignKey("ConsultantId")]
public virtual Employee Consultant { get; set; }

When I run the code and try to enter an education to the employee with the consultant it gives me following exception with an inner exception.

EntityCommandExecutionException 
{"An error occurred while executing the command definition. See the inner exception for details."}

Inner exception: SqlCeException
{"The column name is not valid. [ Node name (if any) = Extent1,Column name = Employee_Id ]"}

But when I remove Consultant object it does not give an exception. How can I solve this problem so that i have access to both Consultant and Employee itself?

The exception happens in DetailsEducation.cshtml:

@{ if (Model.EducationList == null || !Model.EducationList.Any())
    { 

Here is how EducationList is populated:

public ActionResult DetailsEducation(int id) 
{ 
  Employee employee = _work.EmployeeRepository.GetSet()
    .Include(a => a.EducationList)
    .Include(a => a.EducationList.Select(c => c.University))
    .Include(a => a.EducationList.Select(c => c.Department))
    .FirstOrDefault(a => a.Id == id); 
  return PartialView("_DetailsEducation", employee);
}

解决方案

Column name = Employee_Id

When Entity Framework creates a SQL query with a foreign key that has an (unexpected) underscore in its column name it is almost always an indicator that EF infers a relationship by convention and that is another one than the relationship you have defined with annotations or Fluent API.

This foreign key cannot origin from the EmployeeEducation.Employee and EmployeeEducation.Consultant navigation properties because for those you have defined the foreign key name with data annotations [ForeignKey("EmployeeId")] and [ForeignKey("ConsultantId")].

Now, how does EF detect relationships? It inspects the navigation properties in your model classes. We already know that EmployeeEducation.Employee and EmployeeEducation.Consultant cannot be the problem, so there must be a third navigation property somewhere. The relationship that belongs to this navigation property must have an association end in EmployeeEducation because EF apparently infers the need of an additional foreign key Employee_Id in the EmployeeEducation table.

Because of the name - Employee _Id - this navigation property will be in your class Employee. Looking at your Include(a => a.EducationList) you seem to have a collection property in Employee:

public SomeCollectionType<EmployeeEducation> EducationList { get; set; }

This collection is most likely causing the third foreign key. If you had only one navigation property in EmployeeEducation, for example only EmployeeEducation.Employee, the problem would not occur because EF would infer in this case that Employee.EducationList and EmployeeEducation.Employee are a pair of navigation properties of a single relationship.

If you have two navigation properties both refering to Employee EF cannot decide which of the two the collection in Employee belongs to. Instead of choosing one by whatever rule it chooses none of them and assumes that the collection belongs to a third relationship.

To solve the problem you must EF give a hint which of the two references in EmployeeEducation you want to relate the collection to, for example by using the [InverseProperty] attribute on one of the properties (but not both):

[ForeignKey("EmployeeId"), InverseProperty("EducationList")]
public virtual Employee Employee { get; set; }

[ForeignKey("ConsultantId")]
public virtual Employee Consultant { get; set; }

Attention: The EducationList will only contain EmployeeEducations the given employee is Employee for, but not Consultant. For that you would need a second collection property in Employee with an [InverseProperty] annotation on Consultant this time. Generally you cannot associate one navigation collection in one entity to two navigation references in the other entity. Your only choice are either two collections or no collection at all. (In the latter case your problem would disappear as well by the way, but you wouldn't have a navigation property anymore you could "include".)

这篇关于ASP.NET MVC 3.0 2外键来自同一个表例外的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！