Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

EF7 SqlServer Migration is trying to update columns on History table before creating the History table if any new columns are added in the same migration #29799

Closed
willmcgowan85 opened this issue Dec 8, 2022 · 5 comments · Fixed by #32239
Assignees
Labels
closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. customer-reported type-bug
Milestone

Comments

@willmcgowan85
Copy link

willmcgowan85 commented Dec 8, 2022

I ran into an issue when setting a table to include it's history counterpart, where it will attempt to update the columns on the history table, before the history table is created, if there are any additional columns also being added, like delete_date below.

I couldn't find any know issues or roadmap items regarding Temporal/History tables and ordering of SQL queries during the Update-Database or Script-Migration process. So I don't know if this is a known issue or not, and I know Temporal tables are still being worked on overall. But here are my findings if it helps.

I am unable to upload sample project from my work machine, I might be able to reporduce it from personal machine later if needed.

Steps to reproduce:

  1. Create a new table
  2. Create Migration to insert new table
  3. Add new column to table
  4. Set table's .IsTemporal(true)
  5. Create Migration for new column and is temporal
  6. Update-Database will fail on second migration

Here is the generated migration from the C# changes for step 5, the second migration.

using System;
using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

namespace TemporalTest.Migrations
{
    /// <inheritdoc />
    public partial class SetTemporal : Migration
    {
        /// <inheritdoc />
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.AlterTable(
                name: "Country")
                .Annotation("SqlServer:IsTemporal", true)
                .Annotation("SqlServer:TemporalHistoryTableName", "CountryHistory")
                .Annotation("SqlServer:TemporalHistoryTableSchema", null)
                .Annotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
                .Annotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart");

            migrationBuilder.AlterColumn<string>(
                name: "iso_country_code",
                table: "Country",
                type: "nvarchar(2)",
                maxLength: 2,
                nullable: false,
                comment: "ISO standard code of the country",
                oldClrType: typeof(string),
                oldType: "nvarchar(2)",
                oldMaxLength: 2,
                oldComment: "ISO standard code of the country")
                .Annotation("SqlServer:IsTemporal", true)
                .Annotation("SqlServer:TemporalHistoryTableName", "CountryHistory")
                .Annotation("SqlServer:TemporalHistoryTableSchema", null)
                .Annotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
                .Annotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart");

            migrationBuilder.AlterColumn<string>(
                name: "country_name",
                table: "Country",
                type: "nvarchar(100)",
                maxLength: 100,
                nullable: false,
                comment: "Name of the country",
                oldClrType: typeof(string),
                oldType: "nvarchar(100)",
                oldMaxLength: 100,
                oldComment: "Name of the country")
                .Annotation("SqlServer:IsTemporal", true)
                .Annotation("SqlServer:TemporalHistoryTableName", "CountryHistory")
                .Annotation("SqlServer:TemporalHistoryTableSchema", null)
                .Annotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
                .Annotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart");

            migrationBuilder.AlterColumn<int>(
                name: "Id",
                table: "Country",
                type: "int",
                nullable: false,
                comment: "Primary Key/Id",
                oldClrType: typeof(int),
                oldType: "int",
                oldComment: "Primary Key/Id")
                .Annotation("SqlServer:Identity", "1, 1")
                .Annotation("SqlServer:IsTemporal", true)
                .Annotation("SqlServer:TemporalHistoryTableName", "CountryHistory")
                .Annotation("SqlServer:TemporalHistoryTableSchema", null)
                .Annotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
                .Annotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart")
                .OldAnnotation("SqlServer:Identity", "1, 1");

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

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

            migrationBuilder.AddColumn<DateTime>(
                name: "delete_date",
                table: "Country",
                type: "datetime2",
                nullable: true)
                .Annotation("SqlServer:IsTemporal", true)
                .Annotation("SqlServer:TemporalHistoryTableName", "CountryHistory")
                .Annotation("SqlServer:TemporalHistoryTableSchema", null)
                .Annotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
                .Annotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart");
        }

        /// <inheritdoc />
        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropColumn(
                name: "PeriodEnd",
                table: "Country")
                .Annotation("SqlServer:IsTemporal", true)
                .Annotation("SqlServer:TemporalHistoryTableName", "CountryHistory")
                .Annotation("SqlServer:TemporalHistoryTableSchema", null)
                .Annotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
                .Annotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart");

            migrationBuilder.DropColumn(
                name: "PeriodStart",
                table: "Country")
                .Annotation("SqlServer:IsTemporal", true)
                .Annotation("SqlServer:TemporalHistoryTableName", "CountryHistory")
                .Annotation("SqlServer:TemporalHistoryTableSchema", null)
                .Annotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
                .Annotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart");

            migrationBuilder.DropColumn(
                name: "delete_date",
                table: "Country")
                .Annotation("SqlServer:IsTemporal", true)
                .Annotation("SqlServer:TemporalHistoryTableName", "CountryHistory")
                .Annotation("SqlServer:TemporalHistoryTableSchema", null)
                .Annotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
                .Annotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart");

            migrationBuilder.AlterTable(
                name: "Country")
                .OldAnnotation("SqlServer:IsTemporal", true)
                .OldAnnotation("SqlServer:TemporalHistoryTableName", "CountryHistory")
                .OldAnnotation("SqlServer:TemporalHistoryTableSchema", null)
                .OldAnnotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
                .OldAnnotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart");

            migrationBuilder.AlterColumn<string>(
                name: "iso_country_code",
                table: "Country",
                type: "nvarchar(2)",
                maxLength: 2,
                nullable: false,
                comment: "ISO standard code of the country",
                oldClrType: typeof(string),
                oldType: "nvarchar(2)",
                oldMaxLength: 2,
                oldComment: "ISO standard code of the country")
                .OldAnnotation("SqlServer:IsTemporal", true)
                .OldAnnotation("SqlServer:TemporalHistoryTableName", "CountryHistory")
                .OldAnnotation("SqlServer:TemporalHistoryTableSchema", null)
                .OldAnnotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
                .OldAnnotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart");

            migrationBuilder.AlterColumn<string>(
                name: "country_name",
                table: "Country",
                type: "nvarchar(100)",
                maxLength: 100,
                nullable: false,
                comment: "Name of the country",
                oldClrType: typeof(string),
                oldType: "nvarchar(100)",
                oldMaxLength: 100,
                oldComment: "Name of the country")
                .OldAnnotation("SqlServer:IsTemporal", true)
                .OldAnnotation("SqlServer:TemporalHistoryTableName", "CountryHistory")
                .OldAnnotation("SqlServer:TemporalHistoryTableSchema", null)
                .OldAnnotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
                .OldAnnotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart");

            migrationBuilder.AlterColumn<int>(
                name: "Id",
                table: "Country",
                type: "int",
                nullable: false,
                comment: "Primary Key/Id",
                oldClrType: typeof(int),
                oldType: "int",
                oldComment: "Primary Key/Id")
                .Annotation("SqlServer:Identity", "1, 1")
                .OldAnnotation("SqlServer:Identity", "1, 1")
                .OldAnnotation("SqlServer:IsTemporal", true)
                .OldAnnotation("SqlServer:TemporalHistoryTableName", "CountryHistory")
                .OldAnnotation("SqlServer:TemporalHistoryTableSchema", null)
                .OldAnnotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
                .OldAnnotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart");
        }
    }
}

This is the SQL generated when running Script-Migration targeting only the above migration change.

BEGIN TRANSACTION;
GO

ALTER TABLE [Country] ADD [PeriodEnd] datetime2 NOT NULL DEFAULT '9999-12-31T23:59:59.9999999';
GO

ALTER TABLE [Country] ADD [PeriodStart] datetime2 NOT NULL DEFAULT '0001-01-01T00:00:00.0000000';
GO

ALTER TABLE [Country] ADD [delete_date] datetime2 NULL;
GO

ALTER TABLE [CountryHistory] ADD [delete_date] datetime2 NULL;
GO

ALTER TABLE [Country] ADD PERIOD FOR SYSTEM_TIME ([PeriodStart], [PeriodEnd])
GO

ALTER TABLE [Country] ALTER COLUMN [PeriodStart] ADD HIDDEN
GO

ALTER TABLE [Country] ALTER COLUMN [PeriodEnd] ADD HIDDEN
GO

DECLARE @historyTableSchema sysname = SCHEMA_NAME()
EXEC(N'ALTER TABLE [Country] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [' + @historyTableSchema + '].[CountryHistory]))')

GO

INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20221208140231_SetTemporal', N'7.0.0');
GO

COMMIT;
GO

Here is the output when attempting to run Update-Database as shown on the first line.

PM> Update-Database -Verbose
Using project 'TemporalTest'.
Using startup project 'TemporalTest'.
Build started...
Build succeeded.
C:\Program Files\dotnet\dotnet.exe exec --depsfile C:\GIT\Repos\TemporalTest\TemporalTest\bin\Debug\net6.0\TemporalTest.deps.json --additionalprobingpath C:\Users\wmcgowan\.nuget\packages --runtimeconfig C:\GIT\Repos\TemporalTest\TemporalTest\bin\Debug\net6.0\TemporalTest.runtimeconfig.json C:\Users\wmcgowan\.nuget\packages\microsoft.entityframeworkcore.tools\7.0.0\tools\netcoreapp2.0\any\ef.dll database update --verbose --no-color --prefix-output --assembly C:\GIT\Repos\TemporalTest\TemporalTest\bin\Debug\net6.0\TemporalTest.dll --project C:\GIT\Repos\TemporalTest\TemporalTest\TemporalTest.csproj --startup-assembly C:\GIT\Repos\TemporalTest\TemporalTest\bin\Debug\net6.0\TemporalTest.dll --startup-project C:\GIT\Repos\TemporalTest\TemporalTest\TemporalTest.csproj --project-dir C:\GIT\Repos\TemporalTest\TemporalTest\ --language C# --configuration Debug --working-dir C:\GIT\Repos\TemporalTest\TemporalTest --root-namespace TemporalTest --nullable
Using assembly 'TemporalTest'.
Using startup assembly 'TemporalTest'.
Using application base 'C:\GIT\Repos\TemporalTest\TemporalTest\bin\Debug\net6.0'.
Using working directory 'C:\GIT\Repos\TemporalTest\TemporalTest'.
Using root namespace 'TemporalTest'.
Using project directory 'C:\GIT\Repos\TemporalTest\TemporalTest\'.
Remaining arguments: .
Finding DbContext classes...
Finding IDesignTimeDbContextFactory implementations...
Finding application service provider in assembly 'TemporalTest'...
Finding Microsoft.Extensions.Hosting service provider...
Using environment 'Development'.
Using application service provider from Microsoft.Extensions.Hosting.
Found DbContext 'TestContext'.
Finding DbContext classes in the project...
Using context 'TestContext'.
Finding design-time services referenced by assembly 'TemporalTest'...
Finding design-time services referenced by assembly 'TemporalTest'...
No referenced design-time services were found.
Finding design-time services for provider 'Microsoft.EntityFrameworkCore.SqlServer'...
Using design-time services from provider 'Microsoft.EntityFrameworkCore.SqlServer'.
Finding IDesignTimeServices implementations in assembly 'TemporalTest'...
No design-time services were found.
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (29ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT 1
Executed DbCommand (29ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT 1
Executed DbCommand (23ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT OBJECT_ID(N'[__EFMigrationsHistory]');
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (23ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT OBJECT_ID(N'[__EFMigrationsHistory]');
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT 1
Executed DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT 1
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT OBJECT_ID(N'[__EFMigrationsHistory]');
Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT OBJECT_ID(N'[__EFMigrationsHistory]');
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [MigrationId], [ProductVersion]
      FROM [__EFMigrationsHistory]
      ORDER BY [MigrationId];
Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [MigrationId], [ProductVersion]
FROM [__EFMigrationsHistory]
ORDER BY [MigrationId];
Microsoft.EntityFrameworkCore.Migrations[20402]
      Applying migration '20221208140231_SetTemporal'.
Applying migration '20221208140231_SetTemporal'.
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (11ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      ALTER TABLE [Country] ADD [PeriodEnd] datetime2 NOT NULL DEFAULT '9999-12-31T23:59:59.9999999';
Executed DbCommand (11ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER TABLE [Country] ADD [PeriodEnd] datetime2 NOT NULL DEFAULT '9999-12-31T23:59:59.9999999';
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      ALTER TABLE [Country] ADD [PeriodStart] datetime2 NOT NULL DEFAULT '0001-01-01T00:00:00.0000000';
Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER TABLE [Country] ADD [PeriodStart] datetime2 NOT NULL DEFAULT '0001-01-01T00:00:00.0000000';
Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER TABLE [Country] ADD [delete_date] datetime2 NULL;
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      ALTER TABLE [Country] ADD [delete_date] datetime2 NULL;
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      ALTER TABLE [CountryHistory] ADD [delete_date] datetime2 NULL;
Failed executing DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER TABLE [CountryHistory] ADD [delete_date] datetime2 NULL;
Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot find the object "CountryHistory" because it does not exist or you do not have permissions.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean isAsync, Int32 timeout, Boolean asyncWrite)
   at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ClientConnectionId:29fb56dd-f6aa-4706-8048-069a0e4aa128
Error Number:4902,State:1,Class:16
Cannot find the object "CountryHistory" because it does not exist or you do not have permissions.
PM> 

EF Core version: 7.0.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6.0
Operating system: Win 10
IDE: Visual Studio 2022 17.4.1

@maumar
Copy link
Contributor

maumar commented Dec 9, 2022

i'm able to repro this on latest bits. When processing AddColumnOperation we should check for the scenario where the current column is on temporal table but the old column is on regular table.

@andersme
Copy link

andersme commented Jun 30, 2023

Is there any update on this? We are still seeing this issue.

@ajcvickers
Copy link
Member

@andersme We hope to be able to address this in EF8, but this may not happen due to other priorities.

@ajcvickers
Copy link
Member

@maumar to investigate.
/cc @bricelam

maumar added a commit that referenced this issue Nov 6, 2023
Before we used to put temporal annotations on temporal tables and all their columns, so that it's easier to process. Problem was that this would generate very noisy migrations when converting from regular table to temporal and vice versa. Every column would have an AlterColumn operation (which we would ignore during processing, but they were nonetheless generated in migration files).
Also, we were using relatively simple logic to track state of our temporal tables. Some operations require temporary disabling of the versioning/period, and we need to keep track of that so that we don't try to disable period twice, or forget to enable it later.
The way we did it could lead to invalid SQL in some non-trivial scenarios (e.g. converting table to temporal and adding a new column at the same time)

The new approach is to only put temporal annotations on the table itself, and the period columns. Regular columns of the temporal table don't have any temporal annotations on them anymore and we reason about temporal information based on other table-based migration operation in the batch and, if need be, on the relational model.
We also keep track of the actual temporal information for every operation (rather than keeping global dictionaries of period/version), so that complex migrations, involving multiple operations are more robust.
To achieve that we compute the initial (temporal) state of all the tables involved in the migration. We scan all the table operations, and if some info is missing we get it from relational model. Then we do the proper processing of the migration operations - when we encounter table operation, we update the temporal information for that table (since table operations contain relevant temporal annotations). For all other operations we extract the current temporal state for the table involved, and reason based on that info.

Fixes #27459 - SQL Server Migrations: Review temporal table annotations
Fixes #29536 - EF Core IsTemporal() creates huge migration
Fixes #29799 - EF7 SqlServer Migration is trying to update columns on History table before creating the History table if any new columns are added in the same migration
maumar added a commit that referenced this issue Nov 13, 2023
Before we used to put temporal annotations on temporal tables and all their columns, so that it's easier to process. Problem was that this would generate very noisy migrations when converting from regular table to temporal and vice versa. Every column would have an AlterColumn operation (which we would ignore during processing, but they were nonetheless generated in migration files).
Also, we were using relatively simple logic to track state of our temporal tables. Some operations require temporary disabling of the versioning/period, and we need to keep track of that so that we don't try to disable period twice, or forget to enable it later.
The way we did it could lead to invalid SQL in some non-trivial scenarios (e.g. converting table to temporal and adding a new column at the same time)

The new approach is to only put temporal annotations on the table itself, and the period columns. Regular columns of the temporal table don't have any temporal annotations on them anymore and we reason about temporal information based on other table-based migration operation in the batch and, if need be, on the relational model.
We also keep track of the actual temporal information for every operation (rather than keeping global dictionaries of period/version), so that complex migrations, involving multiple operations are more robust.
To achieve that we compute the initial (temporal) state of all the tables involved in the migration. We scan all the table operations, and if some info is missing we get it from relational model. Then we do the proper processing of the migration operations - when we encounter table operation, we update the temporal information for that table (since table operations contain relevant temporal annotations). For all other operations we extract the current temporal state for the table involved, and reason based on that info.

Fixes #27459 - SQL Server Migrations: Review temporal table annotations
Fixes #29536 - EF Core IsTemporal() creates huge migration
Fixes #29799 - EF7 SqlServer Migration is trying to update columns on History table before creating the History table if any new columns are added in the same migration
maumar added a commit that referenced this issue Nov 13, 2023
Before we used to put temporal annotations on temporal tables and all their columns, so that it's easier to process. Problem was that this would generate very noisy migrations when converting from regular table to temporal and vice versa. Every column would have an AlterColumn operation (which we would ignore during processing, but they were nonetheless generated in migration files).
Also, we were using relatively simple logic to track state of our temporal tables. Some operations require temporary disabling of the versioning/period, and we need to keep track of that so that we don't try to disable period twice, or forget to enable it later.
The way we did it could lead to invalid SQL in some non-trivial scenarios (e.g. converting table to temporal and adding a new column at the same time)

The new approach is to only put temporal annotations on the table itself, and the period columns. Regular columns of the temporal table don't have any temporal annotations on them anymore and we reason about temporal information based on other table-based migration operation in the batch and, if need be, on the relational model.
We also keep track of the actual temporal information for every operation (rather than keeping global dictionaries of period/version), so that complex migrations, involving multiple operations are more robust.
To achieve that we compute the initial (temporal) state of all the tables involved in the migration. We scan all the table operations, and if some info is missing we get it from relational model. Then we do the proper processing of the migration operations - when we encounter table operation, we update the temporal information for that table (since table operations contain relevant temporal annotations). For all other operations we extract the current temporal state for the table involved, and reason based on that info.

Fixes #27459 - SQL Server Migrations: Review temporal table annotations
Fixes #29536 - EF Core IsTemporal() creates huge migration
Fixes #29799 - EF7 SqlServer Migration is trying to update columns on History table before creating the History table if any new columns are added in the same migration
maumar added a commit that referenced this issue Nov 13, 2023
Before we used to put temporal annotations on temporal tables and all their columns, so that it's easier to process. Problem was that this would generate very noisy migrations when converting from regular table to temporal and vice versa. Every column would have an AlterColumn operation (which we would ignore during processing, but they were nonetheless generated in migration files).
Also, we were using relatively simple logic to track state of our temporal tables. Some operations require temporary disabling of the versioning/period, and we need to keep track of that so that we don't try to disable period twice, or forget to enable it later.
The way we did it could lead to invalid SQL in some non-trivial scenarios (e.g. converting table to temporal and adding a new column at the same time)

The new approach is to only put temporal annotations on the table itself, and the period columns. Regular columns of the temporal table don't have any temporal annotations on them anymore and we reason about temporal information based on other table-based migration operation in the batch and, if need be, on the relational model.
We also keep track of the actual temporal information for every operation (rather than keeping global dictionaries of period/version), so that complex migrations, involving multiple operations are more robust.
To achieve that we compute the initial (temporal) state of all the tables involved in the migration. We scan all the table operations, and if some info is missing we get it from relational model. Then we do the proper processing of the migration operations - when we encounter table operation, we update the temporal information for that table (since table operations contain relevant temporal annotations). For all other operations we extract the current temporal state for the table involved, and reason based on that info.

Fixes #27459 - SQL Server Migrations: Review temporal table annotations
Fixes #29536 - EF Core IsTemporal() creates huge migration
Fixes #29799 - EF7 SqlServer Migration is trying to update columns on History table before creating the History table if any new columns are added in the same migration
@maumar maumar modified the milestones: Backlog, 9.0.0 Nov 13, 2023
@maumar maumar added the closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. label Nov 13, 2023
@ajcvickers ajcvickers modified the milestones: 9.0.0, 9.0.0-preview1 Jan 31, 2024
@danielebanovaz
Copy link

Experiencing the same issue with EF Core 8.0.4

@roji roji modified the milestones: 9.0.0-preview1, 9.0.0 Oct 12, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. customer-reported type-bug
Projects
None yet
Development

Successfully merging a pull request may close this issue.

6 participants