.NET 3.0升级到6.0后EF Core查询出现PersonsHistoryId不存在错误

项目从.NET 3.0升级至.NET Core 6.0，同步升级EF Core 6.0，数据库采用时态表。项目可正常编译，但执行包含.Include(x => x.PersonRoles)的Persons集合查询时，EF Core会尝试选择数据库和EF模型中均不存在的PersonsHistoryId列，报错**"Invalid column name 'PersonsHistoryId'"**；移除.Include则无此问题。

查询代码 #

public async Task<Person> GetPersonAndAllRolesAndTagsByEmailAsync(string email)
{
    return await dbSet
                 .Include(x => x.PersonRoles)
                 .Where(x => x.Email == email)
                 .FirstOrDefaultAsync();
}

生成的SQL查询 #

SELECT [t].[Id], [t].[AddressLine1], [t].[AddressLine2], [t].[AptifyId], [t].[City], [t].[CountryId], [t].[Credentials], 
[t].[Email], [t].[ExpiredReminder], [t].[Fax], [t].[FirstName], [t].[HasSignature], [t].[InsertedBy], [t].[InsertedByPersonId], 
[t].[InsertedDateUtc], [t].[IsAcsMember], [t].[IsActive], [t].[LastLoginUtc], [t].[LastModifiedBy], [t].[LastModifiedByPersonId], 
[t].[LastModifiedDateUtc], [t].[LastName], [t].[MiddleName], [t].[Military], [t].[MobileNumber], [t].[NameOnCard], 
[t].[NineMonthReminder], [t].[NonNaState], [t].[PhoneNumber], [t].[PostalCode], [t].[PreviousAtlsId], [t].[SecondaryEmailAddress], 
[t].[StateId], [t].[Surgeon], [t].[SysEndTime], [t].[SysStartTime], [p0].[Id], [p0].[BackupRoleId], [p0].[CovidFlagItem1], 
[p0].[CovidFlagItem2], [p0].[CovidFlagItem3], [p0].[CovidOriginalEndDateItem1], [p0].[CovidOriginalEndDateItem2], 
[p0].[CovidOriginalEndDateItem3], [p0].[EndDateUtc], [p0].[InsertedBy], [p0].[InsertedByPersonId], [p0].[InsertedDateUtc], 
[p0].[IsDeactivated], [p0].[LastModifiedBy], [p0].[LastModifiedByPersonId], [p0].[LastModifiedDateUtc], [p0].[PersonId], 
[p0].[PersonsHistoryId], [p0].[ProgramRoleId], [p0].[RoleExtension4], [p0].[RoleExtension4OriginalEndDateTime], [p0].[StartDateUtc], 
[p0].[StateChairApprovalSettingsId], [p0].[SysEndTime], [p0].[SysStartTime]
FROM (
    SELECT TOP(1) [p].[Id], [p].[AddressLine1], [p].[AddressLine2], [p].[AptifyId], [p].[City], [p].[CountryId], [p].[Credentials], 
    [p].[Email], [p].[ExpiredReminder], [p].[Fax],[p].[FirstName], [p].[HasSignature], [p].[InsertedBy], [p].[InsertedByPersonId], 
    [p].[InsertedDateUtc], [p].[IsAcsMember], [p].[IsActive], [p].[LastLoginUtc], [p].[LastModifiedBy], [p].[LastModifiedByPersonId], 
    [p].[LastModifiedDateUtc], [p].[LastName], [p].[MiddleName], [p].[Military], [p].[MobileNumber], [p].[NameOnCard], 
    [p].[NineMonthReminder], [p].[NonNaState], [p].[PhoneNumber], [p].[PostalCode], [p].[PreviousAtlsId], [p].[SecondaryEmailAddress], 
    [p].[StateId], [p].[Surgeon], [p].[SysEndTime], [p].[SysStartTime]
    FROM [Persons] AS [p]
    WHERE [p].[Email] = @__email_0
) AS [t]
LEFT JOIN [PersonRoles] AS [p0] ON [t].[Id] = [p0].[PersonId]   
ORDER BY [t].[Id]',N'@__email_0 varchar(100)',@__email_0='atlsadmin'

实体类代码 #

public class Person
{
    public int Id { get; set; }

    public int? PreviousAtlsId { get; set; }

    public int AptifyId { get; set; }

    [Column(TypeName = "varchar(20)")]
    public string FirstName { get; set; }

    [Column(TypeName = "varchar(20)")]
    public string MiddleName { get; set; }

    [Column(TypeName = "varchar(40)")]
    public string LastName { get; set; }

    [Column(TypeName = "varchar(60)")]
    public string NameOnCard { get; set; }

    [Column(TypeName = "varchar(63)")]
    public string Credentials { get; set; }

    [Column(TypeName = "varchar(134)")]
    public string PhoneNumber { get; set; }

    [Column(TypeName = "varchar(90)")]
    public string MobileNumber { get; set; }

    [Column(TypeName = "varchar(63)")]
    public string Fax { get; set; }

    [Column(TypeName = "varchar(100)")]
    public string Email { get; set; }

    [Column(TypeName = "varchar(100)")]
    public string SecondaryEmailAddress { get; set; }

    public bool Surgeon { get; set; }

    public bool Military { get; set; }

    public bool IsActive { get; set; }

    public bool IsAcsMember { get; set; }

    //Address
    [Column(TypeName = "varchar(100)")]
    public string AddressLine1 { get; set; }

    [Column(TypeName = "varchar(100)")]
    public string AddressLine2 { get; set; }

    [Column(TypeName = "varchar(50)")]
    public string City { get; set; }

    public int? StateId { get; set; }

    [Column(TypeName = "varchar(63)")]
    public string NonNaState { get; set; }

    [Column(TypeName = "varchar(25)")]
    public string PostalCode { get; set; }

    public int CountryId { get; set; }

    public bool? NineMonthReminder { get; set; }

    public bool? ExpiredReminder { get; set; }

    public DateTime? LastLoginUtc { get; set; }

    public ICollection<Comment> Comments { get; set; }

    public ICollection<EthosBank> EthosBanks { get; set; }

    public ICollection<DisclosureForm> DisclosureForms { get; set; }

    public ICollection<CourseRequest> CourseRequests { get; set; }

    public ICollection<PersonArea> PersonAreas { get; set; }

    public ICollection<PersonCourse> PersonCourses { get; set; }
    public ICollection<Course> ContactCourses { get; set; }

    public ICollection<CourseSurvey> CourseSurveys { get; set; }

    public ICollection<PersonCourseEdition> PersonCourseEditions { get; set; }

    public ICollection<PersonRegion> PersonRegions { get; set; }

    public ICollection<PersonSite> PersonSites { get; set; }

    public ICollection<PersonTag> PersonTags { get; set; }

    public List<PersonRole> PersonRoles { get; set; }

    public ICollection<Course> InsertedCourses { get; set; }

    public ICollection<CourseSchedule> CourseSchedules { get; set; }

    public ICollection<PlanningCommitteeMember> PlanningCommitteeMembers { get; set; }

    public Country Country { get; set; }

    public State State { get; set; }

    public ICollection<PersonAttestation> PersonAttestations { get; set; }
    public ICollection<PersonContactLog> PersonContactLogs { get; set; }
    public ICollection<EthosBankOrder> EthosBankOrders { get; set; }
    public ICollection<Alert> Alerts { get; set; }
    public bool HasSignature { get; set; }

    [Column(TypeName = "varchar(100)")]
    public string InsertedBy { get; set; }
    public int? InsertedByPersonId { get; set; }
    public DateTime InsertedDateUtc { get; set; }
    [Column(TypeName = "varchar(100)")]
    public string LastModifiedBy { get; set; }
    public int? LastModifiedByPersonId { get; set; }
    public DateTime LastModifiedDateUtc { get; set; }
}

public class PersonRole
{
    public int Id { get; set; }
    public int PersonId { get; set; }
    public Person Person { get; set; }
    public DateTime StartDateUtc { get; set; }
    public DateTime? EndDateUtc { get; set; }
    public int? StateChairApprovalSettingsId { get; set; }
    public StateChairApprovalSettings StateChairApprovalSettings { get; set; }
    public bool IsDeactivated { get; set; }
    public bool CovidFlagItem1 { get; set; }
    public DateTime? CovidOriginalEndDateItem1 { get; set; }
    public bool CovidFlagItem2 { get; set; }
    public DateTime? CovidOriginalEndDateItem2 { get; set; }
    public bool CovidFlagItem3 { get; set; }
    public DateTime? CovidOriginalEndDateItem3 { get; set; }
    public int ProgramRoleId { get; set; }
    public ProgramRole ProgramRole { get; set;  }
    public int? BackupRoleId { get; set; }
    public bool RoleExtension4 { get; set; }
    public DateTime? RoleExtension4OriginalEndDateTime { get; set; }
}

1. EF Core 6.0时态表关联默认行为：当主实体（Person）被配置为时态表时，EF Core 6.0会自动推断关联实体（PersonRole）需要与主实体的历史表建立关联，因此生成查询时会尝试添加指向历史表的外键列PersonsHistoryId，但你的数据库和实体模型中并未定义该列，导致SQL执行报错。
2. 升级后的配置缺失：从旧版本EF升级到EF Core 6.0时，时态表配置机制发生变化，若未显式指定关联关系的外键，EF Core会自动生成包含历史表关联的映射规则，进而生成无效的SQL列引用。
3. 关联关系自动映射误判：EF Core检测到Person是时态表后，默认认为关联的PersonRole需要跟踪主实体的历史版本，因此在JOIN查询中额外引入了历史表关联字段，而你的场景中并不需要这种关联。

1. 显式配置关联关系：在DbContext的OnModelCreating方法中，明确指定Person与PersonRole的关联外键为PersonId，并确保不会自动关联历史表：

   protected override void OnModelCreating(ModelBuilder modelBuilder)
   {
       // 配置Person与PersonRole的一对多关联
       modelBuilder.Entity<Person>()
           .HasMany(p => p.PersonRoles)
           .WithOne(pr => pr.Person)
           .HasForeignKey(pr => pr.PersonId) // 明确指定外键
           .IsRequired();
   
       // 显式标记Person为时态表（若未配置）
       modelBuilder.Entity<Person>()
           .ToTable(tb => tb.IsTemporal());
   }
   

2. 移除自动生成的历史表关联：若EF Core自动为PersonRole添加了历史表关联配置，可通过模型构建器手动移除该关联，确保仅关联主表。
3. 验证数据库时态表配置：确认Persons表的时态表（通常命名为PersonsHistory）结构正确，且PersonRoles表中确实不存在PersonsHistoryId列，避免数据库与模型不一致。

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