EF Core Code-First Migration Changing Primary Key from Int to Long Failed

1

I'm using Dotnet Core 3.1 with Entity Framework Core 3.1.3 using Code-First with Azure SQL Database.

During the development, I want to change the Primary Key of a table from int to long from the Model class in dotnet core project and use add-migration. However, when using Update-Database, there are errors:

Failed executing DbCommand (153ms) [Parameters=[], CommandType='Text', CommandTimeout='600']
DECLARE @var0 sysname;
SELECT @var0 = [d].[name]
FROM [sys].[default_constraints] [d]
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Warnings]') AND [c].[name] = N'Id');
IF @var0 IS NOT NULL EXEC(N'ALTER TABLE [Warnings] DROP CONSTRAINT [' + @var0 + '];');
ALTER TABLE [Warnings] ALTER COLUMN [Id] bigint NOT NULL;
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (153ms) [Parameters=[], CommandType='Text', CommandTimeout='600']
      DECLARE @var0 sysname;
      SELECT @var0 = [d].[name]
      FROM [sys].[default_constraints] [d]
      INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
      WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Warnings]') AND [c].[name] = N'Id');
      IF @var0 IS NOT NULL EXEC(N'ALTER TABLE [Warnings] DROP CONSTRAINT [' + @var0 + '];');
      ALTER TABLE [Warnings] ALTER COLUMN [Id] bigint NOT NULL;
Microsoft.Data.SqlClient.SqlException (0x80131904): ALTER TABLE failed because the following SET options have incorrect settings: 'ANSI_WARNINGS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
   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 contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ClientConnectionId:774960a2-b0a7-4134-8050-0b8b18be8d37
Error Number:1934,State:1,Class:16
ALTER TABLE failed because the following SET options have incorrect settings: 'ANSI_WARNINGS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

How to fix this problem?

.net-core
entity-framework-core
azure-sql-database
asked on Stack Overflow Apr 10, 2020 by Wilton Kwok

2 Answers

0

Probably u have foreign keys that use this id. U can't do if there is ,u should firstly remove foreign keys and then try it, then add again foreign keys .

answered on Stack Overflow Apr 10, 2020 by pc_coder
0

I was attempting to do the same thing: change data type of a primary key. I had been deleting and recreating all foreign keys, and the primary key, but I was still getting the error

ALTER TABLE failed because the following SET options have incorrect settings: 'ANSI_WARNINGS'".

I ended up solving it with a ALTER INDEX ALL ON Warnings DISABLE; paired with a ALTER INDEX ALL ON Warnings REBUILD;. So my final script looked something like this:

// drop all fk's
migrationBuilder.DropForeignKey("FK_Warnings_Messages_WarningId", "Messages");

// drop pk
migrationBuilder.DropPrimaryKey("PK_Warnings", "Warnings");

// disable all indexes
migrationBuilder.Sql("ALTER INDEX ALL ON Warnings DISABLE;");

// update data types
migrationBuilder.AlterColumn<long>(name: "Id",
        table: "Warnings",
        type: "long",
        nullable: false,
        oldClrType: typeof(int),
        oldType: "int")
    .Annotation("SqlServer:Identity", "1, 1")
    .OldAnnotation("SqlServer:Identity", "1, 1")

migrationBuilder.AlterColumn<long>(
        name: "WarningId",
        table: "Messages",
        type: "long",
        nullable: false,
        oldClrType: typeof(int),
        oldType: "int");

// reenable indexes
migrationBuilder.Sql("ALTER INDEX ALL ON Warnings REBUILD;");

// restore PK's
migrationBuilder.AddPrimaryKey("PK_Warnings", "Warnings", "Id");

// restore FK's
migrationBuilder.AddForeignKey("FK_Messages_Warnings_WarningId", "Messages", "WarningId", "Warnings", principalColumn: "Id", onDelete: ReferentialAction.Restrict);
answered on Stack Overflow Mar 3, 2021 by Lee Richardson

User contributions licensed under CC BY-SA 3.0