Self-Referencing Relationships

The Chinook database is an excellent example of a real-world schema that uses a self-referencing relationship in its Employee table to model the organizational hierarchy.

The Employee table is used to store details of all employees, and it includes a foreign key column that points back to its own primary key to establish the manager-subordinate relationship.

Chinook's Self-Referencing Table: Employee

The Chinook database's Employee table uses a self-referencing foreign key to model the "reports to" relationship.

The ReportsTo column contains the EmployeeId of the employee who is the manager. If an employee reports to no one (like the CEO or President), the ReportsTo column is NULL.

Entity Framework (EF) Model Configuration

In EF, this relationship is typically configured in the Employee class using two navigation properties:

1. Manager: A single reference property to the manager (the "parent" in the hierarchy).

2. Subordinates: A collection property of all employees who report to this employee (the "children" in the hierarchy).

C# Entity Class Example (EF Core)

public class Employee
{
    // Primary Key
    public int EmployeeId { get; set; }
 
    // Foreign Key to the Manager
    public int? ReportsTo { get; set; } // int? because the top manager's ID is NULL
 
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Title { get; set; }
 
    // Navigation Property for the Manager (One-to-Zero/One)
    // Points up the hierarchy
    public Employee Manager { get; set; }
 
    // Navigation Property for Subordinates (One-to-Many)
    // Points down the hierarchy
    public ICollection<Employee> Subordinates { get; set; }
}

EF Query Examples using the Hierarchy

With this self-referencing structure and the navigation properties configured in EF, common hierarchical queries become simple and intuitive.

1. Finding an Employee's Manager

To find the manager for a specific employee (e.g., employee with EmployeeId = 3), you just follow the single reference navigation property.

// Find the Manager of the Employee with ID 3
var employeeId = 3;
var employeeWithManager = context.Employees
    .Include(e => e.Manager) // Eagerly load the Manager's data
    .Where(e => e.EmployeeId == employeeId)
    .Select(e => new
    {
        EmployeeName = $"{e.FirstName} {e.LastName}",
        ManagerName = $"{e.Manager.FirstName} {e.Manager.LastName}"
    })
    .FirstOrDefault();
 
// Output will be: EmployeeName: Jane Peacock, ManagerName: Nancy Edwards

2. Finding an Employee's Direct Subordinates

To find all employees who directly report to a manager (e.g., manager with EmployeeId = 2), you use the collection navigation property.

// Find all employees reporting to the Manager with ID 2
var managerId = 2;
var managerWithSubordinates = context.Employees
    .Include(e => e.Subordinates) // Eagerly load the collection of subordinates
    .Where(e => e.EmployeeId == managerId)
    .Select(e => new
    {
        ManagerName = $"{e.FirstName} {e.LastName}",
        Subordinates = e.Subordinates.Select(s => $"{s.FirstName} {s.LastName}")
    })
    .FirstOrDefault();
 
// Output will be: ManagerName: Nancy Edwards, Subordinates: [Jane Peacock, Margaret Park, Steve Johnson]