entity has two relationships one-to-one and one-to-many at the same time

user_5136659 0
Viewed 102

I have two entities Employee, Branch

public class Branch
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    public string Name { get; set; }

    public int ManagerId { get; set; }
    public Employee Manager { get; set; }

    public List<Employee> Employees { get; set; }
    public List<Department> Departments { get; set; }
}


public class Employee
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    public int ManagerId { get; set; }
    public Employee Manager { get; set; }

    public int DepartmentId { get; set; }
    public Department Department { get; set; }

    public int BranchId { get; set; }
    public Branch Branch { get; set; }

}

each branch has many departments, each department has many employees, each employee belong to one department and one branch

the database scheme should look like this :

Branch     : Id , Name , ManagerId
Department : Id , Name , ManagerId
Employee   : Id , Name , DepartmentId , BranchId, ManagerId #(department manager)

I want to have OneToOne relationship for Branch with Employee (Manager), and OneToMany relationship for Branch with Employee (Employees Of Branch)

right now when I try to add migrations, it fails with

Unable to determine the relationship represented by navigation property 'Branch.Manager' of type 'Employee'

I understand the issue, but not able to solve it.

entity-framework c#
1 Answers

This design is too de-normalized which leads to a risk of invalid associations, and more explicit mapping needed for relationships.

For instance, if a Branch has a Manager, and a collection of employees, then an employee can get their manager via the Branch. I.e.

employee.Branch.Manager

The issue with having Manager on both Employee and Branch is there is no way to ensure that employee.Manager and employee.Branch.Manager are always pointing at the same person. (where they should always be the same person)

A Department may have a manager as well, but this would be expected to be a higher level manager where each branch has a manager that reports to a department manager.

EF will sometimes get confused trying to work out associations between entities by convention if you don't specify the FK relationship between the FK property and navigation property. From memory, EF convention uses the Property type, not the property name as the default expected key name.

So for instance given a property

public virtual Employee Manager { get; set; }

You might expect EF to work out to expect a FK to be "ManagerId" but instead it would attempt to use "EmployeeId" by default based on the type Employee not the property name. This is why you would need to explicitly point it at "ManagerId" as the FK using the ForeignKey attribute or better, using .Map(x => x.MapKey("ManagerId")) or EF Core Shadow Properties to avoid declaring FK properties in your entities alongside the navigation properties.

user_4234970
Related