search

dotnet / efcore

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations.
https://docs.microsoft.com/ef/
MIT License
13.68k stars 3.17k forks source link

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 closed 10 months ago

willmcgowan85 commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

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

ajcvickers commented 1 year ago

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

ajcvickers commented 12 months ago

@maumar to investigate. /cc @bricelam

danielebanovaz commented 5 months ago

Experiencing the same issue with EF Core 8.0.4