如何查询SQL数据库生成三级树形结构的Razor页面菜单数据

1. 定义数据传输对象（DTO） #

先定义嵌套结构的DTO，用来承载最终的树形数据：

public class DirectoryDto
{
    public int DirectoryId { get; set; }
    public string DirectoryName { get; set; }
    public List<DepartmentDto> Departments { get; set; } = new List<DepartmentDto>();
}

public class DepartmentDto
{
    public int DepartmentId { get; set; }
    public string DepartmentName { get; set; }
    public List<SectorDto> Sectors { get; set; } = new List<SectorDto>();
}

public class SectorDto
{
    public int SectorId { get; set; }
    public string SectorName { get; set; }
}

2. 使用EF Core查询并组装树形结构 #

如果使用Entity Framework Core，先确保实体类配置好导航属性：

// 数据库实体类示例
public class Directory
{
    public int DirectoryId { get; set; }
    public string DirectoryName { get; set; }
    public ICollection<Department> Departments { get; set; } = new List<Department>();
}

public class Department
{
    public int DepartmentId { get; set; }
    public string DepartmentName { get; set; }
    public int DirectoryId { get; set; }
    public Directory Directory { get; set; }
    public ICollection<Sector> Sectors { get; set; } = new List<Sector>();
}

public class Sector
{
    public int SectorId { get; set; }
    public string SectorName { get; set; }
    public int DepartmentId { get; set; }
    public Department Department { get; set; }
}

在Razor页面后台代码中注入DbContext，通过Include+ThenInclude加载关联数据并映射到DTO：

private readonly YourDbContext _context;

public YourPageModel(YourDbContext context)
{
    _context = context;
}

public List<DirectoryDto> TreeData { get; set; }

public async Task OnGetAsync()
{
    TreeData = await _context.Directories
        .Include(d => d.Departments)
            .ThenInclude(dept => dept.Sectors)
        .Select(d => new DirectoryDto
        {
            DirectoryId = d.DirectoryId,
            DirectoryName = d.DirectoryName,
            Departments = d.Departments.Select(dept => new DepartmentDto
            {
                DepartmentId = dept.DepartmentId,
                DepartmentName = dept.DepartmentName,
                Sectors = dept.Sectors.Select(sec => new SectorDto
                {
                    SectorId = sec.SectorId,
                    SectorName = sec.SectorName
                }).ToList()
            }).ToList()
        })
        .ToListAsync();
}

3. 使用原生SQL查询并组装树形结构 #

如果偏好原生SQL，先编写联合查询语句，再在内存中分组组装：

3.1 编写SQL查询 #

SELECT 
    d.DirectoryId, d.DirectoryName,
    dept.DepartmentId, dept.DepartmentName,
    sec.SectorId, sec.SectorName
FROM Directory d
LEFT JOIN Department dept ON d.DirectoryId = dept.DirectoryId
LEFT JOIN Sector sec ON dept.DepartmentId = sec.DepartmentId
ORDER BY d.DirectoryId, dept.DepartmentId, sec.SectorId

3.2 读取并组装数据 #

先定义临时类存储原始查询结果：

public class RawMenuData
{
    public int DirectoryId { get; set; }
    public string DirectoryName { get; set; }
    public int? DepartmentId { get; set; }
    public string DepartmentName { get; set; }
    public int? SectorId { get; set; }
    public string SectorName { get; set; }
}

在后台代码中执行查询并组装树形结构：

public async Task OnGetAsync()
{
    var sql = @"SELECT 
                    d.DirectoryId, d.DirectoryName,
                    dept.DepartmentId, dept.DepartmentName,
                    sec.SectorId, sec.SectorName
                FROM Directory d
                LEFT JOIN Department dept ON d.DirectoryId = dept.DirectoryId
                LEFT JOIN Sector sec ON dept.DepartmentId = sec.DepartmentId
                ORDER BY d.DirectoryId, dept.DepartmentId, sec.SectorId";

    var rawData = await _context.Database.SqlQueryRaw<RawMenuData>(sql).ToListAsync();

    TreeData = rawData
        .GroupBy(x => new { x.DirectoryId, x.DirectoryName })
        .Select(g => new DirectoryDto
        {
            DirectoryId = g.Key.DirectoryId,
            DirectoryName = g.Key.DirectoryName,
            Departments = g
                .Where(x => x.DepartmentId != null)
                .GroupBy(x => new { x.DepartmentId, x.DepartmentName })
                .Select(deptGroup => new DepartmentDto
                {
                    DepartmentId = deptGroup.Key.DepartmentId.Value,
                    DepartmentName = deptGroup.Key.DepartmentName,
                    Sectors = deptGroup
                        .Where(x => x.SectorId != null)
                        .Select(sec => new SectorDto
                        {
                            SectorId = sec.SectorId.Value,
                            SectorName = sec.SectorName
                        }).ToList()
                }).ToList()
        }).ToList();
}

4. 在Razor页面中使用树形数据 #

4.1 静态三级下拉菜单（带分组） #

<select id="menuSelect" class="form-select">
    <option value="">请选择</option>
    @foreach (var dir in Model.TreeData)
    {
        <optgroup label="@dir.DirectoryName">
            @foreach (var dept in dir.Departments)
            {
                <optgroup label="&nbsp;&nbsp;@dept.DepartmentName">
                    @foreach (var sec in dept.Sectors)
                    {
                        <option value="@sec.SectorId">@sec.SectorName</option>
                    }
                </optgroup>
            }
        </optgroup>
    }
</select>

4.2 级联下拉菜单（动态加载） #

<div class="mb-3">
    <label class="form-label">目录</label>
    <select id="directorySelect" class="form-select" onchange="loadDepartments()">
        <option value="">选择目录</option>
        @foreach (var dir in Model.TreeData)
        {
            <option value="@dir.DirectoryId">@dir.DirectoryName</option>
        }
    </select>
</div>

<div class="mb-3">
    <label class="form-label">部门</label>
    <select id="departmentSelect" class="form-select" onchange="loadSectors()">
        <option value="">选择部门</option>
    </select>
</div>

<div class="mb-3">
    <label class="form-label">板块</label>
    <select id="sectorSelect" class="form-select">
        <option value="">选择板块</option>
    </select>
</div>

<script>
    const menuTree = @Json.Serialize(Model.TreeData);

    function loadDepartments() {
        const dirId = parseInt(document.getElementById('directorySelect').value);
        const deptSelect = document.getElementById('departmentSelect');
        deptSelect.innerHTML = '<option value="">选择部门</option>';
        
        if (!dirId) return;
        
        const selectedDir = menuTree.find(d => d.DirectoryId === dirId);
        selectedDir.Departments.forEach(dept => {
            const option = document.createElement('option');
            option.value = dept.DepartmentId;
            option.textContent = dept.DepartmentName;
            deptSelect.appendChild(option);
        });
        
        document.getElementById('sectorSelect').innerHTML = '<option value="">选择板块</option>';
    }

    function loadSectors() {
        const dirId = parseInt(document.getElementById('directorySelect').value);
        const deptId = parseInt(document.getElementById('departmentSelect').value);
        const secSelect = document.getElementById('sectorSelect');
        secSelect.innerHTML = '<option value="">选择板块</option>';
        
        if (!dirId || !deptId) return;
        
        const selectedDir = menuTree.find(d => d.DirectoryId === dirId);
        const selectedDept = selectedDir.Departments.find(dept => dept.DepartmentId === deptId);
        selectedDept.Sectors.forEach(sec => {
            const option = document.createElement('option');
            option.value = sec.SectorId;
            option.textContent = sec.SectorName;
            secSelect.appendChild(option);
        });
    }
</script>

内容的提问来源于stack exchange，提问作者dj_killer13