Matching problematic relationships in Entity Framework using code

Question

I try to learn the code inside the Entity Framework first, and I am having problems modeling relationships. This is the basic personnel database, which for this has two organizations: "Employees and Departments".

The employee belongs to the department, and the department has an administrator and group manager, both of whom are active employees. I tried to simulate this using the following:

EMPLOYEE public int? DepartmentID { get; set; } public virtual Department Department { get; set; } Context: modelBuilder.Entity<Employee>().HasOptional(x => x.Department); DEPARTMENT public class Department { [Required] public int DepartmentID { get; set; } [Required(ErrorMessage = "The description is required.")] public string Description { get; set; } public int? ManagerID { get; set; } public virtual Employee Manager { get; set; } public int? TeamAdministratorID { get; set; } public virtual Employee TeamAdministrator { get; set; } } Context: modelBuilder.Entity<Department>().HasOptional(x => x.Manager); modelBuilder.Entity<Department>().HasOptional(x => x.TeamAdministrator); 

Obviously, I would like the department table to have only four columns - DepartmentID, Description, ManagerID and TeamAdministratorID, but two more are generated for these relationships, namely Manager_EmployeeID and Team_Administrator_EmployeeID. In addition, a Department_DepartmentID column is created in the Employee table to hold the DepartmentID instead, using the DepartmentID column specified in the entity.

What am I doing wrong? How do I define fields and relationships to prevent the code from first ignoring what I pointed out and generating its own navigation fields in the database?

Answer 1

This is because your model configuration is incomplete - you started your own mapping to the Fluent API, so you must tell EF that these properties are indeed FK for relationships. For use by employees:

 modelBuilder.Entity<Employee>() .HasOptional(x => x.Department) .WithMany() .HasForeignKey(x => x.DepartmentID); 

And for use by the department:

 modelBuilder.Entity<Department>() .HasOptional(x => x.Manager) .WithMany() .HasForeignKey(x => x.ManagerID); modelBuilder.Entity<Department>() .HasOptional(x => x.TeamAdministrator); .WithMany() .HasForeignKey(x => x.TeamAdministratorID); 

Btw. without the navigation navigation properties on the opposite side of the relationship it will be difficult to use the model (all WithMany are empty). At least the Department should have:

 public virtual ICollection<Employee> Employees { get; set;} 

And the display should be changed to:

 modelBuilder.Entity<Employee>() .HasOptional(x => x.Department) .WithMany(y => y.Employees) .HasForeignKey(x => x.DepartmentID); 

Answer 2

See employee class

 EMPLOYEE public int? DepartmentID { get; set; } public virtual Department Department { get; set; } 

To show the relationship between the employee and the department, you used the identifier and . In fact, you only need to do this once - through the Department . EF by default searches for the ID property and binds the two classes for you. Thus, your classes should include only one ID-identifier of the class itself. Try removing identifiers for other classes.