.NET 6.0升级后执行存储过程触发列缺失异常求助

升级至.NET 6.0后，EF Core 对FromSqlRaw的映射规则变得更严格：你的MentorRep实体包含RepInfoData导航属性，EF Core会自动推断该关联的外键列为RepInfoDataAgencyCode（对应RepInfo的主键AgencyCode），但存储过程未返回该列，导致实体映射失败，抛出System.InvalidOperationException。

方案1：使用无导航属性的DTO（推荐） #

创建仅包含存储过程返回字段的DTO类，避免EF处理导航属性的外键要求：

public class MentorRepDto
{
    public string UserId { get; set; }
    public string MenteeUserId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Code { get; set; }
    public string Status { get; set; }
    public string ACode { get; set; }
    public string PRole { get; set; }
    // 不包含RepInfoData、AffRepTeamDetails等导航属性
}

在DbContext中注册该DTO为无键实体：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // 原有配置...
    modelBuilder.Entity<MentorRepDto>().HasNoKey();
}

修改查询方法，直接映射到DTO：

private async Task<IEnumerable<MentorRepDto>> MentorRepDetails(string userId)
{
    var uId = new SqlParameter("@userId", userId);
    using (var ctx = _generateDbContext.GenerateNetCoreContext(_configuration))
    {
        try
        {
            var data = await ctx.Set<MentorRepDto>()
                .FromSqlRaw($"EXEC SupportingRepData @userId", uId)
                .ToListAsync();

            return data;
        }
        catch (Exception ex)
        {
            _logger.LogError($"Exception {ex} occured in the application ");
        }
        return null;
    }
}

方案2：修改查询或模型配置，跳过导航属性加载 #

方法A：查询时禁用自动包含导航属性 #

在查询中添加IgnoreAutoIncludes()阻止EF尝试加载导航属性，同时用AsNoTracking()避免实体追踪：

private async Task<IEnumerable<MentorRep>> MentorRepDetails(string userId)
{
    var uId = new SqlParameter("@userId", userId);
    using (var ctx = _generateDbContext.GenerateNetCoreContext(_configuration))
    {
        DbSet<MentorRep> s = ctx.Set<MentorRep>();
        try
        {
            var data = await s.FromSqlRaw($"EXEC SupportingRepData @userId", uId)
                .AsNoTracking()
                .IgnoreAutoIncludes()
                .ToListAsync();

            return data;
        }
        catch (Exception ex)
        {
            _logger.LogError($"Exception {ex} occured in the application ");
        }
        return null;
    }
}

方法B：配置外键为可选并补全返回列 #

在模型配置中明确外键并设置为可空：

modelBuilder.Entity<MentorRep>(v =>
{
    v.HasKey(p => new { p.UserId, p.MenteeUserId });
    v.HasOne(e => e.RepInfoData)
        .WithMany(e => e.AffiliatedRepDetails)
        .HasForeignKey("RepInfoDataAgencyCode")
        .IsRequired(false); // 设置外键为可选
});

修改存储过程，返回名为RepInfoDataAgencyCode的空列：

SELECT 
    -- 原有返回字段...,
    NULL AS RepInfoDataAgencyCode
FROM ...

方案3：手动读取SQL结果并映射 #

绕过EF的实体映射，直接读取SQL数据并手动实例化实体：

private async Task<IEnumerable<MentorRep>> MentorRepDetails(string userId)
{
    var uId = new SqlParameter("@userId", userId);
    using (var ctx = _generateDbContext.GenerateNetCoreContext(_configuration))
    {
        try
        {
            using var command = ctx.Database.GetDbConnection().CreateCommand();
            command.CommandText = "EXEC SupportingRepData @userId";
            command.Parameters.Add(uId);
            
            await ctx.Database.OpenConnectionAsync();
            using var reader = await command.ExecuteReaderAsync();
            
            var result = new List<MentorRep>();
            while (reader.Read())
            {
                result.Add(new MentorRep
                {
                    UserId = reader["UserId"].ToString(),
                    MenteeUserId = reader["MenteeUserId"].ToString(),
                    FirstName = reader["FirstName"].ToString(),
                    LastName = reader["LastName"].ToString(),
                    Code = reader["code"].ToString(),
                    Status = reader["Status"].ToString(),
                    ACode = reader["ACode"].ToString(),
                    PRole = reader["PRole"].ToString(),
                    AffRepTeamDetails = new HashSet<TeamInfo>()
                });
            }
            return result;
        }
        catch (Exception ex)
        {
            _logger.LogError($"Exception {ex} occured in the application ");
        }
        finally
        {
            await ctx.Database.CloseConnectionAsync();
        }
        return null;
    }
}

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