Linq2db:查询对象层次结构的有效方法

CREATE TABLE Employees (
    EmployeeId INT PRIMARY KEY,
    Name NVARCHAR(100),
    ManagerId INT
);

using System;
using System.Collections.Generic;
using System.Linq;
using Linq2Db;

public class Employee
{
    public int EmployeeId { get; set; }
    public string Name { get; set; }
    public int? ManagerId { get; set; }
    public Employee Manager { get; set; }
    public List<Employee> Subordinates { get; set; }
}

public class Program
{
    public static void Main()
    {
        using (var db = new DataConnection("YourConnectionStringHere"))
        {
            var employees = db.GetTable<Employee>();

            var employeeHierarchy = employees
                .Include(e => e.Manager)
                .ThenInclude(e => e.Subordinates)
                .ToList();

            // 构建层次结构
            foreach (var employee in employeeHierarchy)
            {
                if (employee.ManagerId != null)
                {
                    var manager = employeeHierarchy.FirstOrDefault(e => e.EmployeeId == employee.ManagerId);
                    if (manager != null)
                    {
                        manager.Subordinates ??= new List<Employee>();
                        manager.Subordinates.Add(employee);
                    }
                }
            }

            // 打印层次结构
            PrintHierarchy(employeeHierarchy, null);
        }
    }

    private static void PrintHierarchy(List<Employee> employees, Employee manager)
    {
        foreach (var employee in employees.Where(e => e.ManagerId == manager?.EmployeeId))
        {
            Console.WriteLine($"  {manager?.Name} -> {employee.Name}");
            PrintHierarchy(employees, employee);
        }
    }
}