基于联合SQL视图的C# Web API：如何更新底层表？

问题场景 #

基于EF Core开发C# Web API，连接SQL数据库，采用遗留架构：用联合视图vwUsers对应多个Org专属用户表（如UserAA、UserBB），vwUsers的Org字段用于区分所属表。通过AutoMapper映射与vwUsers字段一致的DTO，现在需要实现UPDATE路由，但无法直接更新视图，需找到更新底层表的正确方式。

原代码的核心问题 #

原UpdateAsync方法存在几个关键问题：

• 直接拼接表名到FromSqlRaw存在SQL注入风险
• FromSqlRaw默认返回无跟踪实体，修改后SaveChanges不会生效，因为EF未跟踪实体变更
• 用反射遍历字段赋值不够优雅，易出现类型或字段名错误

修正后的解决方案 #

1. 核心改进点 #

• 先验证Org合法性，避免SQL注入和非法表名
• 开启EF实体跟踪，确保修改能被SaveChanges识别
• 用AutoMapper替代反射赋值，保证类型安全

2. 完整的SQLUserRepository UpdateAsync实现 #

public async Task<vwUsers?> UpdateAsync(int id, string org, vwUsers user)
{
    // 维护合法Org列表，根据实际业务场景更新
    var validOrgs = new List<string> { "AA", "BB", "CC" };
    if (!validOrgs.Contains(org))
    {
        return null;
    }

    // 参数化查询UserID，避免SQL注入，同时指定目标表
    string sqlQuery = $"SELECT * FROM [User{org}] WHERE UserID = @Id";
    // 开启实体跟踪，EF才能识别后续的修改
    var existingUser = await dbContext.Set<vwUsers>()
        .FromSqlRaw(sqlQuery, new SqlParameter("@Id", id))
        .AsTracking()
        .FirstOrDefaultAsync();

    if (existingUser == null)
    {
        return null;
    }

    // 用AutoMapper更新字段，忽略UserID和Org这两个不需要修改的字段
    mapper.Map(user, existingUser, opt =>
    {
        opt.ForMember(dest => dest.UserID, opt => opt.Ignore());
        opt.ForMember(dest => dest.Org, opt => opt.Ignore());
    });

    // 更新最后修改时间
    existingUser.LastUpd = DateTime.Now;

    await dbContext.SaveChangesAsync();

    // 可选：重新查询vwUsers视图，返回包含最新数据的实体
    return await dbContext.Set<vwUsers>()
        .FromSqlRaw($"SELECT * FROM vwUsers WHERE UserID = @Id AND Org = @Org",
            new SqlParameter("@Id", id),
            new SqlParameter("@Org", org))
        .FirstOrDefaultAsync();
}

3. 补充说明 #

• SQL注入防护：通过白名单验证Org值，杜绝恶意用户构造非法表名；UserID用参数化查询，避免注入。
• 实体跟踪：AsTracking()必须加上，否则EF不会跟踪从FromSqlRaw获取的实体，修改后SaveChanges不会执行更新语句。
• AutoMapper优势：相比反射，AutoMapper更简洁，能在编译期发现字段名错误，减少运行时异常。
• 返回视图数据：如果需要确保返回的是视图中的完整最新数据，更新后重新查询vwUsers是更稳妥的选择，避免底层表和视图的字段差异带来的不一致。

控制器层代码无需修改 #

原UserController的Update方法可以保持原样，所有底层逻辑都在Repository层处理好了。

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