EF Core: One-to-one and One-to-many Relationships Between a Single Pair of Entities

Tim Coulter 0
Viewed 47

Below is a simplified representation of some entities that I wish to access using an EF Core model. A Company can have an optional one-to-one relationship with its Manager, as well as a one-to-many relationship with its Employees.

public class Company
{
    public int Id { get; set; }
    public virtual ICollection<Employee> Employees { get; set; }
    public virtual Manager Manager {  get; set; }
    public int? ManagerId {  get; set; }
}

public class Employee
{
    public int Id { get; set; }
    public virtual Company Company {  get; set; }
    public int CompanyId {  get; set; }
}

public class Manager : Employee
{
}

The entities are exposed to EF Core in the usual way:

public DbSet<Company> Companies { get; set; }

public DbSet<Employee> Employees { get; set; }

I have attempted to model their relationships, as follows:

modelBuilder.Entity<Company>()
    .HasMany(item => item.Employees)
    .WithOne(item => item.Company)
    .HasForeignKey(item => item.CompanyId)
    .IsRequired(true);

modelBuilder.Entity<Manager>()
    .HasOne(item => item.Company)
    .WithOne(item => item.Manager)
    .HasForeignKey<Company>(item => item.ManagerId)
    .IsRequired(false);

However, at runtime, EF Core rejects this configuration with the message "Cannot create a relationship between 'Company.Manager' and 'Employee.Company' because a relationship already exists between 'Company.Employees' and 'Employee.Company'. Navigations can only participate in a single relationship. If you want to override an existing relationship call 'Ignore' on the navigation 'Employee.Company' first in 'OnModelCreating'."

Can anyone suggest how to configure this? Many thanks.

entity-framework-core c#
1 Answers

When extending an existing concrete entity with an other one, the extending's class fields are added on the same table, so that in a single row you can determine which employee is also a manager. This is called Table Per Hierarchy (TPH).

What you would like is a pattern of Table Per Type (TPT) - not yet provided by entity out of the box. More about those here!

To set it up yourself until then, define the models "totaly by fluent api", such that you create your own relations without needed to use entity conventions.

Something like so ( not even build though, its a notepad doodle )

public class Employee {
    public string EmployeeId {get;set;}
    public string DisplayName {get;set;}

    public string WorksAtCompanyId {get;set;}
    public Company WorksAtCompany {get;set; }

    public string ManagesCompanyId {get;set;}
    public Company ManagesCompany {get;set; }

}

modelBuilder.Entity<Employee>()
b.Property<string>("EmployeeId").HasMaxLength(36).HasColumnType("nvarchar(36)");
b.Property<string>("DisplayName").HasColumnType("nvarchar(max)");
b.Property<string>("WorksAtCompanyId").HasColumnType("nvarchar(max)");
b.Property<string>("ManagesCompanyId").HasColumnType("nvarchar(max)");

b.HasOne(d => d.WorksAtCompany).WithMany(d => d.Employees).HasPrincipalKey<Employee>(d => d.WorksAtCompanyId).HasForeignKey<Company>(d => d.CompanyId).IsRequired().OnDelete(DeleteBehavior.Restrict);
b.HasOne<Company>(d => d.ManagesCompany).WithOne<Employee>(d => d.Manager).HasPrincipalKey<Employee>(d => d.ManagesCompanyId).HasForeignKey<Company>(d => d.CompanyId).OnDelete(DeleteBehavior.Restrict);


public class Company {
    public string CompanyId {get;set;}
    public List<Employee> Employees {get;set;}

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

modelBuilder.Entity<Company>()
b.Property<string>("CompanyId").HasMaxLength(36).HasColumnType("nvarchar(36)");
b.Property<string>("DisplayName").HasColumnType("nvarchar(max)");

b.HasMany(d => d.Employees).WithOne<Employee>(d => d.WorksAtCompany).HasPrincipalKey<Employee>(d => d.WorksAtCompanyId).HasForeignKey<Company>(d => d.CompanyId).OnDelete(DeleteBehavior.Restrict);
b.HasOne(d => d.Manager).WithOne(d => d.ManagesCompany).HasPrincipalKey<Employee>(d => d.ManagesCompanyId).HasForeignKey<Company>(d => d.CompanyId).OnDelete(DeleteBehavior.Restrict);
MKougiouris0
Related