.NET Core控制器中NotMapped属性的原生SQL查询问题求助

问题根源 #

你的ApplicationUser.Roles标记了[NotMapped]，意味着EF Core不会从数据库加载这个属性的值，所以从DbContext.Users获取的allClientUsers列表里，所有用户的Roles都是null，自然筛选不出目标角色的用户。

解决方案 #

方案1：使用EF Core导航属性（推荐） #

这是符合EF Core设计的标准做法，无需编写原生SQL：

1. 在ApplicationUser类中添加用户角色的导航属性（继承IdentityUser的项目通常已默认包含，若缺失可手动添加）：

public virtual ICollection<IdentityUserRole<string>> UserRoles { get; set; } = new List<IdentityUserRole<string>>();

2. 查询用户时通过Include和ThenInclude加载关联角色数据，直接在数据库层面完成筛选，性能更优：

if (entity.NotifyManagers || entity.NotifyUsers)
{
    var targetRoles = new List<string>();
    if (entity.NotifyManagers) targetRoles.Add("Call Coaching Manager");
    if (entity.NotifyUsers) targetRoles.Add("Call Coaching User");

    var recipients = await DbContext.Users
        .Where(u => u.ClientId == entity.Dashboard.ClientId)
        .Include(u => u.UserRoles)
            .ThenInclude(ur => ur.Role)
        .Where(u => u.UserRoles.Any(ur => targetRoles.Contains(ur.Role.Name)))
        .ToListAsync();

    // 按需拆分角色组
    var callCoachingManagers = recipients.Where(u => u.UserRoles.Any(ur => ur.Role.Name == "Call Coaching Manager")).ToList();
    var callCoachingUsers = recipients.Where(u => u.UserRoles.Any(ur => ur.Role.Name == "Call Coaching User")).ToList();
}

方案2：基于原生SQL关联角色数据 #

若因项目限制无法修改模型或使用导航属性，可参考你同事的逻辑调整到业务场景中：

1. 定义用于映射SQL查询结果的类：

public class UserWithRoles
{
    public string UserId { get; set; }
    public string Roles { get; set; } // 逗号分隔的角色名称字符串
}

2. 查询指定ClientId下的用户及角色，再关联到用户列表并赋值Roles属性：

if (entity.NotifyManagers || entity.NotifyUsers)
{
    var clientId = entity.Dashboard.ClientId;
    var connectionString = Configuration.GetConnectionString("DefaultConnection");
    List<UserWithRoles> userRoleMappings;

    string sqlQuery = @"
        SELECT u.Id AS UserId, STRING_AGG(r.Name, ', ') AS Roles
        FROM AspNetUsers u
        LEFT JOIN AspNetUserRoles ur ON u.Id = ur.UserId
        LEFT JOIN AspNetRoles r ON ur.RoleId = r.Id
        WHERE u.ClientId = @clientId
        GROUP BY u.Id";

    using (var sqlConnection = new SqlConnection(connectionString))
    {
        userRoleMappings = sqlConnection.Query<UserWithRoles>(sqlQuery, new { clientId }).ToList();
    }

    var allClientUsers = await DbContext.Users
        .Where(x => x.ClientId == clientId)
        .ToListAsync();

    // 为每个用户填充Roles属性
    foreach (var user in allClientUsers)
    {
        var mapping = userRoleMappings.FirstOrDefault(m => m.UserId == user.Id);
        user.Roles = mapping?.Roles?.Split(',').Select(r => r.Trim()).ToList() ?? new List<string>();
    }

    // 现在可以正常筛选角色
    var callCoachingManagers = allClientUsers
        .Where(x => x.Roles.Contains("Call Coaching Manager"))
        .ToList();
                            
    var callCoachingUsers = allClientUsers
        .Where(x => x.Roles.Contains("Call Coaching User"))
        .ToList();

    var recipients = new List<ApplicationUser>();
    if (entity.NotifyManagers) recipients.AddRange(callCoachingManagers);
    if (entity.NotifyUsers) recipients.AddRange(callCoachingUsers);
}

关于.NET Core初级开发者的SQL技能要求 #

作为初级.NET Core开发者，不需要精通复杂SQL语法（如存储过程、高级优化），但必须掌握基础SQL查询、表关联逻辑：

• 要理解EF Core的局限性，当EF Core无法生成高效查询、或处理复杂关联场景时，原生SQL是必要的补充手段；
• 能看懂基本的SELECT、JOIN、WHERE、GROUP BY语句，这对排查数据库问题、理解EF Core生成的SQL至关重要；
• 实际项目中常混合使用EF Core和原生SQL，掌握基础SQL能帮你快速定位解决问题，也是进阶的必备技能。

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