如何在Entity Framework Core中映射存储过程结果的复杂属性？

尝试将存储过程结果映射到EF Core无键实体的复杂属性时，抛出异常：System.InvalidOperationException: The required column 'Info_CreatedByUserId' was not present in the results of a 'FromSql' operation。存储过程返回正确结果，但EF无法识别复杂属性的列映射。

解决方案：正确配置复杂属性列映射并适配EF约定 #

EF Core默认会为复杂属性生成带前缀的列名（如Info_CreatedByUserId），但你的存储过程返回的是无前缀的原始列名，因此需要显式覆盖约定，同时调整实体配置和调用逻辑。

1. 修正实体类的构造可访问性 #

将私有构造改为protected，确保EF能反射创建实例（避免映射时的反射权限问题）：

public class UserAuditInfo
{
    protected UserAuditInfo() { } // 改为protected，允许EF实例化

    public UserAuditInfo(string username, AuditInfo info)
    {
        Username = username;
        Info = info;
    }

    public int Id { get; private set; }
    public string Username { get; private set; }
    public AuditInfo Info { get; private set; }
}

public class AuditInfo
{
    protected AuditInfo() { } // 同样改为protected

    public AuditInfo(int createdByUserId, DateTime dateCreated, int? updatedByUserId, DateTime? dateUpdated)
    {
        CreatedByUserId = createdByUserId;
        DateCreated = dateCreated;
        UpdatedByUserId = updatedByUserId;
        DateUpdated = dateUpdated;
    }

    public int CreatedByUserId { get; private set; }
    public DateTime DateCreated { get; private set; }
    public int? UpdatedByUserId { get; private set; }
    public DateTime? DateUpdated { get; private set; }
}

2. 修正ModelBuilder中的复杂属性配置 #

显式指定每个复杂属性的列名，关闭EF的前缀约定，同时移除不必要的默认schema配置（存储过程不在my_app schema下）：

public class SecurityModuleEntities(DbContextOptions options) : DbContext(options)
{
    public DbSet<UserAuditInfo> UserAudits { get; set; }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        builder.Entity<UserAuditInfo>(model =>
        {
            model.HasNoKey();
            model.Property(m => m.Id).HasColumnName("user_id");
            model.Property(m => m.Username).HasColumnName("user_name");

            // 配置复杂属性，完全匹配存储过程返回的列名
            model.ComplexProperty(m => m.Info, info =>
            {
                info.Property(i => i.DateCreated).HasColumnName("create_dt");
                info.Property(i => i.CreatedByUserId).HasColumnName("create_user_id");
                info.Property(i => i.DateUpdated).HasColumnName("update_dt");
                info.Property(i => i.UpdatedByUserId).HasColumnName("update_user_id");
            });
        });
    }
}

3. 调整存储过程调用逻辑 #

使用FromSqlInterpolated确保参数安全，同时添加AsNoTracking优化无键实体的查询：

public async Task<IEnumerable<UserAuditInfo>> GetAuditInfo(string username)
{
    var sql = FormattableStringFactory.Create("exec my_app_funcs.get_audit_info @Username = {0}", username);
    var query = entities.UserAudits.FromSqlInterpolated(sql).AsNoTracking();
    return await query.ToListAsync();
}

备选方案：手动映射结果（绕过EF自动映射） #

如果上述配置仍报错，可以直接读取存储过程结果并手动映射到实体类：

public async Task<IEnumerable<UserAuditInfo>> GetAuditInfo(string username)
{
    var parameters = new[]
    {
        new SqlParameter("@Username", SqlDbType.VarChar, 50) { Value = username }
    };

    // 直接查询并返回匿名类型结果
    var rawResults = await entities.Database.SqlQueryRaw(@"
        select
            user_id,
            user_name,
            create_dt,
            create_user_id,
            update_dt,
            update_user_id
        from my_app.user_profile
        where lower(user_name) = lower(@Username)", parameters)
        .ToListAsync();

    // 手动映射到目标实体
    return rawResults.Select(row => new UserAuditInfo(
        row.user_name,
        new AuditInfo(
            row.create_user_id,
            row.create_dt,
            row.update_user_id,
            row.update_dt
        ))
    {
        Id = row.user_id
    });
}

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