Upgrading 3.0 .net project to 6.0 and getting EF query error

Question

I upgraded a project to .NET core 6.0 which required upgrading to EF core 6.0. Temporal tables are being used in the database.

I was finally able to get the project to compile but when I run the application, I am getting an error when it performs a query against the Persons dbSet that has a ".Include". When the ".Include" is used in the query, it is trying to select a column "PersonsHistoryId" which does not exist. When the ".Include" is not part of the query, the "PersonsHistoryId" column is not part of the query. What triggers EF to select the "PersonsHistoryId" column when that column does not exist in the codebase or database? Why would it add a non-existent column?

The error I get is "Invalid column name 'PersonsHistoryId'." PersonsHistoryId is not a column in any table in the database or in any EF model or entity.

The query being used:

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

Produces this query:

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'

Entity Classes:

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; }
}

Answer 1

You will need to provide code for your entities (Guessing something like a PersonHistory entity and at least one other entity that has a navigation property of type PersonHistory)

This error is commonly caused by EF falling back on convention and a missed assumption about what EF will recognize as a FK.

Say I have a PersonHistory table and matching entity, then I have a related table and entity called "Foo"

public class Foo
{
    public int Id { get; set; }
    // ... some other fields...


    public int HistoryId { get; set; }
    public PersonHistory History { get; set; }
}

Now one would assume that EF would find the navigation property called "History" and match that up with the "HistoryId" property we had defined and have in the DB as the FK for History.

However, this is not the case. EF convention for finding FKs in the entity or table is determined on the type name, not the property name. EF will be looking for a "PersonHistoryId" if left to work on convention since the type is "PersonHistory" not "History".

The fix for this is to explicitly tell EF what the FK name is. There are a few options to do this, and it sounds like if this was working earlier but broke with changes to upgrade EF, one of these methods may have been accidentally edited out or a property was renamed.

FK Attribute:

Either:

[ForeignKey(nameof(History)]
public int HistoryId { get; set; }
public PersonHistory History { get; set; }

or:

public int HistoryId { get; set; }
[ForeignKey(nameof(HistoryId)]
public PersonHistory History { get; set; }

If you don't have a "HistoryId" or other FK property declared then this should work for a Shadow Property:

[ForeignKey("HistoryId"]
public PersonHistory History { get; set; }

(Shadow Property meaning telling EF what to look for in the table and managing the FK entirely behind the scenes without adding a visible FK property to the entity)

modelBuilder:

In the DbContext OnModelCreating we can configure our relationships. If we are relying on non-conventional naming and aren't using attributes then we can configure the FK relationship here:

modelBuilder.Entity<Foo>()
    .HasOne(x => x.History)
    .WithMany()
    .HasForeignKey(x => x.HistoryId);

or in the case where we use a shadow property:

modelBuilder.Entity<Foo>()
    .HasOne(x => x.History)
    .WithMany()
    .HasForeignKey("HistoryId");

The third option is similar to using the OnModelCreating / modelBuilder and that is using IEntityTypeConfiguration implementations. I don't find these commonly used (though they are my favorite for things that are too clumsy or can't be done with attributes) but you can check if any of your entity definitions have IEntityTypeConfiguration declarations to review.

This should give you some ideas to check through the code change history to see what might have been broken. Whether some configuration might have been lost, or there were naming changes which moved the code away from what EF can work out by convention and you just need to introduce some hints to get it linking things up again.