如何用Dapper实现Project多级一对多嵌套对象高效查询？

核心思路：一次性联表查询+内存映射层级 #

放弃多层foreach的N+1查询模式，用单条SQL联表取出所有层级数据，再通过内存字典缓存实体，快速构建嵌套结构，彻底避免重复查询数据库导致的性能损耗。

1. 编写联表SQL #

针对你的层级结构，编写包含所有关联表的LEFT JOIN查询（如果子级可能为空用LEFT JOIN；若确保子级必存在，可改用INNER JOIN），示例如下：

SELECT 
    p.Id AS ProjectId, p.Name AS ProjectName,
    pf.Id AS ProjectFaseId, pf.Name AS ProjectFaseName, pf.ProjectId AS PfProjectId,
    pi.Id AS ProjectItemId, pi.Name AS ProjectItemName, pi.ProjectFaseId AS PiProjectFaseId,
    pid.Id AS ProjectItemDateId, pid.Date AS ProjectItemDateValue, pid.ProjectItemId AS PidProjectItemId,
    a.Id AS AppointmentId, a.Time AS AppointmentTime, a.ProjectItemDateId AS AProjectItemDateId
FROM Project p
LEFT JOIN ProjectFase pf ON p.Id = pf.ProjectId
LEFT JOIN ProjectItem pi ON pf.Id = pi.ProjectFaseId
LEFT JOIN ProjectItemDate pid ON pi.Id = pid.ProjectItemId
LEFT JOIN Appointment a ON pid.Id = a.ProjectItemDateId
WHERE p.Id = @ProjectId

注意事项：

• 只查询页面实际需要的字段，避免SELECT *减少数据传输量；
• 确保Project.Id、ProjectFase.ProjectId、ProjectItem.ProjectFaseId等关联字段都创建了数据库非聚集索引，加速联表查询。

2. 用Dapper多映射构建嵌套结构 #

利用Dapper的Query方法支持多类型映射的特性，配合字典缓存已创建的实体，避免重复实例化和重复关联：

public async Task<Project> GetProjectWithFullHierarchy(int projectId)
{
    var sql = // 上述SQL语句

    var projectCache = new Dictionary<int, Project>();
    var faseCache = new Dictionary<int, ProjectFase>();
    var itemCache = new Dictionary<int, ProjectItem>();
    var dateCache = new Dictionary<int, ProjectItemDate>();

    await using var connection = new SqlConnection(_connectionString);
    await connection.QueryAsync<Project, ProjectFase, ProjectItem, ProjectItemDate, Appointment, Project>(
        sql,
        (project, fase, item, date, appointment) =>
        {
            // 处理Project层级
            if (!projectCache.TryGetValue(project.Id, out var currentProject))
            {
                currentProject = project;
                currentProject.ProjectFases = new List<ProjectFase>();
                projectCache.Add(project.Id, currentProject);
            }

            // 处理ProjectFase层级
            if (fase != null)
            {
                if (!faseCache.TryGetValue(fase.Id, out var currentFase))
                {
                    currentFase = fase;
                    currentFase.ProjectItems = new List<ProjectItem>();
                    currentProject.ProjectFases.Add(currentFase);
                    faseCache.Add(fase.Id, currentFase);
                }

                // 处理ProjectItem层级
                if (item != null)
                {
                    if (!itemCache.TryGetValue(item.Id, out var currentItem))
                    {
                        currentItem = item;
                        currentItem.ProjectItemDates = new List<ProjectItemDate>();
                        currentFase.ProjectItems.Add(currentItem);
                        itemCache.Add(item.Id, currentItem);
                    }

                    // 处理ProjectItemDate层级
                    if (date != null)
                    {
                        if (!dateCache.TryGetValue(date.Id, out var currentDate))
                        {
                            currentDate = date;
                            currentDate.Appointments = new List<Appointment>();
                            currentItem.ProjectItemDates.Add(currentDate);
                            dateCache.Add(date.Id, currentDate);
                        }

                        // 处理Appointment层级
                        if (appointment != null)
                        {
                            currentDate.Appointments.Add(appointment);
                        }
                    }
                }
            }

            return currentProject;
        },
        param: new { ProjectId = projectId },
        splitOn: "ProjectFaseId,ProjectItemId,ProjectItemDateId,AppointmentId"
    );

    return projectCache.TryGetValue(projectId, out var result) ? result : null;
}

关键说明：

• splitOn参数指定每个实体类型的起始字段，对应SQL中别名的顺序，Dapper会据此拆分结果集到对应实体；
• 各层级字典缓存确保同一个实体只被创建一次，避免重复添加到父级集合，同时提升映射效率。

3. Blazor SSR页面中调用 #

在你的Blazor SSR页面中注入数据服务，通过[Parameter]传入的ProjectID调用查询方法：

@page "/project/{ProjectId:int}"
@inject IProjectDataService ProjectDataService

@if (Project != null)
{
    <h1>@Project.Name</h1>
    @foreach (var fase in Project.ProjectFases)
    {
        <h2>@fase.Name</h2>
        @foreach (var item in fase.ProjectItems)
        {
            <h3>@item.Name</h3>
            @foreach (var date in item.ProjectItemDates)
            {
                <p>日期：@date.Date</p>
                @foreach (var appointment in date.Appointments)
                {
                    <p>预约时间：@appointment.Time</p>
                }
            }
        }
    }
}
else
{
    <p>加载中...</p>
}

@code {
    [Parameter] public int ProjectId { get; set; }
    private Project Project { get; set; }

    protected override async Task OnInitializedAsync()
    {
        Project = await ProjectDataService.GetProjectWithFullHierarchy(ProjectId);
    }
}

额外性能优化建议 #

• 连接复用：在依赖注入中注册SqlConnection为范围服务，避免频繁创建销毁数据库连接；
• 结果缓存：如果项目数据不频繁更新，可添加内存缓存（如IMemoryCache），相同ProjectID的请求直接返回缓存结果；
• 分页处理：若单项目下子级数据量极大，可考虑针对最深层级（如Appointment）做分页加载，避免一次性加载过多数据。

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