.NET Core API配合AutoMapper实现多对多关联下项目用户总项目数映射

你遇到的问题是两个原因共同导致的：

1. 查询AppUser实体时没有加载UserProjects导航属性，EF默认不会自动加载关联数据，所以统计的集合长度为0
2. AutoMapper默认映射规则找不到ProjectsCount对应的字段，不会自动计算关联项目总数

1. 修改AutoMapper配置 #

删除多余的AppUserProject映射，新增ProjectsCount字段的映射规则：

// 只保留这一条映射即可
CreateMap<AppUser, UserForProjectDTO>()
    .ForMember(dest => dest.ProjectsCount, opt => opt.MapFrom(src => src.UserProjects.Count));

2. 修改控制器查询逻辑 #

需要在查询时加载每个用户的全部项目关联数据，才能统计全局总项目数，这里提供两种实现方案：

方案一：保留AutoMapper映射，加载关联数据 #

[HttpGet("{projectId}/users-assigned")]
[Description("Get list of users assigned to a specified project.")]
public async Task<ActionResult> GetProjectUsers(int projectId)
{
    if (!ProjectExistById(projectId)) return NotFound("Project doesn't exist");
    
    var users = await _context.AppUserProjects
        .Where(up => up.ProjectId == projectId)
        .Include(up => up.User)
            .ThenInclude(u => u.UserProjects) // 加载用户所有关联的项目，用于统计总数
        .Select(up => up.User)
        .ToListAsync();

    var usersDto = _mapper.Map<ICollection<UserForProjectDTO>>(users);
    return Ok(usersDto);
}

如果你的上下文没有暴露AppUserProject的DbSet，可以替换为如下查询逻辑：

var users = await _context.Projects
    .Where(p => p.ProjectId == projectId)
    .SelectMany(p => p.UserProjects)
    .Include(up => up.User)
        .ThenInclude(u => u.UserProjects)
    .Select(up => up.User)
    .ToListAsync();

方案二：SQL层面直接投影（性能更优） #

不需要依赖AutoMapper，直接在查询时完成DTO的构造，Count计算会在数据库层面执行，不需要加载全量关联数据到内存：

[HttpGet("{projectId}/users-assigned")]
[Description("Get list of users assigned to a specified project.")]
public async Task<ActionResult> GetProjectUsers(int projectId)
{
    if (!ProjectExistById(projectId)) return NotFound("Project doesn't exist");
    
    var usersDto = await _context.AppUserProjects
        .Where(up => up.ProjectId == projectId)
        .Select(up => new UserForProjectDTO
        {
            Id = up.User.Id,
            FirstName = up.User.FirstName,
            LastName = up.User.LastName,
            ProjectsCount = up.User.UserProjects.Count()
        })
        .ToListAsync();

    return Ok(usersDto);
}

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