Renaming `PeriodStart` and `PeriodEnd` columns of a temporal table causes them to be swapped

[orty]

Hi,

I just discovered that, in my database, on 3 temporal tables, the meaning of the PeriodStart and PeriodEnd column has been kind of swapped after renaming them (to be compliant with our naming convention).
As a result, dates in the new PeriodStart-like column are greater than the dates in the PeriodEnd one.

Repro steps:

Having this entity model:

public class Contact : IEntity
{
    /// <inheritdoc />
    public Guid Id { get; set; }
}

Having this in DbContext:

modelBuilder.Entity<Contact>(
    entity =>
    {
        entity.ToTable(
            builder =>
            {
                builder.IsTemporal();
            });

        entity.HasKey(c => c.Id).HasName("ConId");
    });

Generates the following migration Up():

migrationBuilder.AlterTable(
    name: "Contacts")
    .Annotation("SqlServer:IsTemporal", true)
    .Annotation("SqlServer:TemporalHistoryTableName", "ContactsHistory")
    .Annotation("SqlServer:TemporalHistoryTableSchema", null)
    .Annotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
    .Annotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart");
    
migrationBuilder.AddColumn<DateTime>(
    name: "PeriodEnd",
    table: "Contacts",
    type: "datetime2",
    nullable: false,
    defaultValue: new DateTime(1, 1, 1, 0, 0, 0, 0, DateTimeKind.Unspecified))
    .Annotation("SqlServer:IsTemporal", true)
    .Annotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
    .Annotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart");
    
migrationBuilder.AddColumn<DateTime>(
    name: "PeriodStart",
    table: "Contacts",
    type: "datetime2",
    nullable: false,
    defaultValue: new DateTime(1, 1, 1, 0, 0, 0, 0, DateTimeKind.Unspecified))
    .Annotation("SqlServer:IsTemporal", true)
    .Annotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
    .Annotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart");

Then, if we are to rename the columns to be compliant with the Con prefix trigram, we add this to the DbContext:

modelBuilder.Entity<Contact>(
    entity =>
    {
        entity.ToTable(
            builder =>
            {
                builder.IsTemporal(
                    tableBuilder =>
                    {
                        tableBuilder.HasPeriodStart("ConValidFrom");
                        tableBuilder.HasPeriodEnd("ConValidTo");
                    });
            });
  
        entity.HasKey(c => c.Id).HasName("ConId");
    });

And this is the resulting migration Up() generated method:

migrationBuilder.RenameColumn(
    name: "PeriodStart",
    table: "Contacts",
    newName: "ConValidTo");

migrationBuilder.RenameColumn(
    name: "PeriodEnd",
    table: "Contacts",
    newName: "ConValidFrom");

migrationBuilder.AlterTable(
    name: "Contacts")
    .Annotation("SqlServer:IsTemporal", true)
    .Annotation("SqlServer:TemporalHistoryTableName", "ContactsHistory")
    .Annotation("SqlServer:TemporalHistoryTableSchema", null)
    .Annotation("SqlServer:TemporalPeriodEndColumnName", "ConValidTo")
    .Annotation("SqlServer:TemporalPeriodStartColumnName", "ConValidFrom")
    .OldAnnotation("SqlServer:IsTemporal", true)
    .OldAnnotation("SqlServer:TemporalHistoryTableName", "ContactsHistory")
    .OldAnnotation("SqlServer:TemporalHistoryTableSchema", null)
    .OldAnnotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
    .OldAnnotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart");

Here, it seems to me that the renaming of the columns kind of "swaps" them, isn't it ?

Thanks.

Include provider and version information

EF Core version: 6.0.5
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6.0
Operating system: Win 11
IDE: Visual Studio 2022 17.3.6

[orty]

I just double checked using the following SQL query

select  OBJECT_NAME(object_id) as table_name,* from sys.periods

that the columns for start and end are swapped in the database.

[maumar]

migrations model differ is not aware of the period start/end annotations, and when figuring out renames it just looks at the names (and property mappings, but those are the same). It so happens that old names were in different order ("End", "Start") than the new ones ("From", "To").

Workaround is to rename one column at a time.

[orty]

Thanks for having looked into it. This is definitely not a satisfying workaround for such a task, but at the very least, it does the trick.
Naming the columns the right way in the first place does not create any issue, I just hope that a fix for this strange behavior can find its way to v.7.0

[ajcvickers]

Note for triage: root cause is tracked by #15178. We should consider raising the priority of this.

[ajcvickers]

Note from triage: at least try to fix this issue in 8.0, even if we don't get to all of #15178.