CODEWITHSUNDEEP
c#entity-framework
pisuvar
pisuvar

Reputation: 267

ASP.NET MVC 3.0 2 Foreign Keys from the same table exception

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);
}

Upvotes: 2

Views: 2222

Answers (1)

Slauma
Slauma

Reputation: 177163

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

Upvotes: 3

Related Questions