SqlServer Migrations: Rebuild foreign keys

[Anderman]

I changed the length of een unique index column with give me the message.

The index 'IX_Invoices_ExternalInvoiceId' is dependent on column 'ExternalInvoiceId'. The index 'IX_Invoices_ExternalInvoiceId' is dependent on column 'ExternalInvoiceId'. ALTER TABLE ALTER COLUMN ExternalInvoiceId failed because one or more objects access this column.

use version

modelBuilder
                .HasAnnotation("ProductVersion", "2.1.1-rtm-30846")`

config

public void Configure(EntityTypeBuilder<Invoice> modelBuilder)
        {
            modelBuilder.Property(e => e.Amount)
                .HasColumnType("decimal(18,2)");
            modelBuilder.Property(e => e.ConceptAmount)
                .HasColumnType("decimal(18,2)");
            modelBuilder.Property(e => e.DebitorId)
                .HasColumnType("varchar(50)");
            modelBuilder.Property(e => e.TheirReference)
                .HasColumnType("varchar(24)");
            modelBuilder.Property(e => e.ExternalInvoiceId)
                .HasColumnType("varchar(12)");
            modelBuilder.Property(e => e.Username)
                .HasMaxLength(250);
            modelBuilder.Property(e => e.InvoiceType)
                .HasColumnType("varchar(50)");
            modelBuilder
                .HasIndex(x => new {x.ExternalInvoiceId})
                .IsUnique();
        }

old config

public void Configure(EntityTypeBuilder<Invoice> modelBuilder)
        {
            modelBuilder.Property(e => e.Amount)
                .HasColumnType("decimal(18,2)");
            modelBuilder.Property(e => e.ConceptAmount)
                .HasColumnType("decimal(18,2)");
            modelBuilder
                .HasIndex(x => new {x.ExternalInvoiceId})
                .IsUnique();
        }

generated migration

protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.RenameColumn(
                name: "TheirAmount",
                table: "Invoices",
                newName: "ConceptAmount");

            migrationBuilder.AlterColumn<string>(
                name: "Username",
                table: "Invoices",
                maxLength: 250,
                nullable: true,
                oldClrType: typeof(string),
                oldNullable: true);

            migrationBuilder.AlterColumn<string>(
                name: "TheirReference",
                table: "Invoices",
                type: "varchar(24)",
                nullable: true,
                oldClrType: typeof(string),
                oldNullable: true);

            migrationBuilder.AlterColumn<string>(
                name: "ExternalInvoiceId",
                table: "Invoices",
                type: "varchar(12)",
                nullable: true,
                oldClrType: typeof(string),
                oldNullable: true);

            migrationBuilder.AlterColumn<string>(
                name: "DebitorId",
                table: "Invoices",
                type: "varchar(50)",
                nullable: true,
                oldClrType: typeof(string),
                oldNullable: true);

            migrationBuilder.AddColumn<string>(
                name: "InvoiceType",
                table: "Invoices",
                type: "varchar(50)",
                nullable: true);
        }

[bricelam]

@Anderman Can you provide a project so I dig into this? The index should be dropped before the ALTER COLUMN statement.

[bricelam]

I'm not able to reproduce this on version 2.1.11. The index is correctly dropped and re-created:

DROP INDEX [IX_Invoices_ExternalInvoiceId] ON [Invoices];
ALTER TABLE [Invoices] ALTER COLUMN [ExternalInvoiceId] varchar(12) NULL;
CREATE UNIQUE INDEX [IX_Invoices_ExternalInvoiceId] ON [Invoices] ([ExternalInvoiceId]) WHERE [ExternalInvoiceId] IS NOT NULL;

[maliming]

hi @bricelam

I can provide a project to reproduce the problem. https://github.com/maliming/EFCore_12586/commits/master

The first step is to create TestEntity entity, configure the entity and add the migration.

The second step is to create TestEntityChildren entity, configure the entity and add the migration.

If we update the MaxLength of the Name property of the TestEntity entity.
At this time add a new migration and then try to update the database will appear the following error.

info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 3.0.0 initialized 'ApplicationDbContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: None
info: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT OBJECT_ID(N'[__EFMigrationsHistory]');
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT OBJECT_ID(N'[__EFMigrationsHistory]');
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT OBJECT_ID(N'[__EFMigrationsHistory]');
info: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT OBJECT_ID(N'[__EFMigrationsHistory]');
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [MigrationId], [ProductVersion]
FROM [__EFMigrationsHistory]
ORDER BY [MigrationId];
info: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [MigrationId], [ProductVersion]
      FROM [__EFMigrationsHistory]
      ORDER BY [MigrationId];
info: Microsoft.EntityFrameworkCore.Migrations[20402]
      Applying migration '20191018092536_Update_TestEntity_Name_MaxLength_To_100'.
Applying migration '20191018092536_Update_TestEntity_Name_MaxLength_To_100'.
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
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'[TestEntities]') AND [c].[name] = N'Name');
IF @var0 IS NOT NULL EXEC(N'ALTER TABLE [TestEntities] DROP CONSTRAINT [' + @var0 + '];');
ALTER TABLE [TestEntities] ALTER COLUMN [Name] nvarchar(100) NOT NULL;
info: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      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'[TestEntities]') AND [c].[name] = N'Name');
      IF @var0 IS NOT NULL EXEC(N'ALTER TABLE [TestEntities] DROP CONSTRAINT [' + @var0 + '];');
      ALTER TABLE [TestEntities] ALTER COLUMN [Name] nvarchar(100) NOT NULL;
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (43ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      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'[TestEntities]') AND [c].[name] = N'Name');
      IF @var0 IS NOT NULL EXEC(N'ALTER TABLE [TestEntities] DROP CONSTRAINT [' + @var0 + '];');
      ALTER TABLE [TestEntities] ALTER COLUMN [Name] nvarchar(100) NOT NULL;
Failed executing DbCommand (43ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
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'[TestEntities]') AND [c].[name] = N'Name');
IF @var0 IS NOT NULL EXEC(N'ALTER TABLE [TestEntities] DROP CONSTRAINT [' + @var0 + '];');
ALTER TABLE [TestEntities] ALTER COLUMN [Name] nvarchar(100) NOT NULL;
Microsoft.Data.SqlClient.SqlException (0x80131904): The object 'FK_TestEntityChildren_TestEntities_AppId_Name' is dependent on column 'Name'.
ALTER TABLE ALTER COLUMN Name failed because one or more objects access this column.
   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:7cf26f2e-f68b-498b-abc6-5a354640fdcb
Error Number:5074,State:1,Class:16
The object 'FK_TestEntityChildren_TestEntities_AppId_Name' is dependent on column 'Name'.
ALTER TABLE ALTER COLUMN Name failed because one or more objects access this column.

[estassen-adx]

Just a note on this, I've encountered it on 3.1 when trying to change a PK with a FK reference from nvarchar(450) to nvarchar(250). When generating the script for the migration with 'dotnet ef migrations script', It generates this code to try to remove the PK:

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'[Publishers]') AND [c].[name] = N'Key');
IF @var0 IS NOT NULL EXEC(N'ALTER TABLE [Publishers] DROP CONSTRAINT [' + @var0 + '];');
ALTER TABLE [Publishers] ALTER COLUMN [Key] nvarchar(250) NOT NULL;

Digging into the [sys].[default_constraints] and [sys].[columns] tables, there's no info about the primary key constraint in there, so this command fails to execute this: ALTER TABLE [Publishers] DROP CONSTRAINT [' + @var0 + '];

Maybe ef needs to generate an If/Else, and in the else it will attempt the normal way of removing a constraint like:

ALTER TABLE [Publisher] DROP CONSTRAINT [PK_Publisher];

[taylorchasewhite]

FWIW, this is an issue for my team too.

[JonTvermose]

Also experiencing issues with this when I am trying to alter maxlength of a primary key string column that is used as foreign keys in other tables.

This is in .NET 5

[marchy]

What's a viable workaround for when running into this issue?

Any way to add manual SQL commands or create a multi-step migration to get around the issue?
(ie: drop foreign relations altogether from all referencing tables, then upgrade the original table, then re-add references back in)

It would be really helpful if you could include a script with an example script that does this.

@daunish would you be able to share the script for your solution? (even showing just the lines for one of the dependent tables etc.)

[NaserParhizkar]

I have similar problem in my project. I only want to change ApplicationUser primary key from string to int and also for IdentityRole as a result I have to change context to ApplicationDbContext(DbContextOptions options) : IdentityDbContext<ApplicationUser,ApplicationRole,int>(options) but when I execute migration I expect migration should have contained a drop index constraint for those which use this primary key. But it only contain a alter code for Id type which leads to a migration Update-Database error.

[wqoq]

Just stumbled across this while looking for a solution/workaround to a similar problem that I'm having. In my case, I'm initiating a multi-phase process to convert a bunch of existing tables whose PKs are strings (aka varchars), but they're really GUIDs and thus could be more efficiently stored as uniqueidentifiers.

Similar to what people have done above by changing the HasMaxLength value, in my configuration I added a HasConversion<Guid> on the property that acts at the PK on the table.

The generated migration correctly identifies what I intend to do (change the data type), but when I run or script the migration the resulting SQL only contains an ALTER COLUMN statement which eventually fails the same as other people above:

ALTER TABLE ALTER COLUMN Id failed because one or more objects access this column

I'm currently using EF Core 7.

Noteworthy is that this column has no FKs referencing it. However, the PK index on the column itself is what is referencing it. I have other tables I'll need to convert as well that are referenced by other FKs, but I thought I'd start with something simple—a table with no FKs.

So now I'm contemplating whether I should try to figure out a way to generate the necessary SQL (maybe in a IMigrationsSqlGenerator), or just hand bomb the custom SQL I'll need. Leaning towards the former because I have over 100 PKs I'm going to need to do this for.

[InspiringCode]

I am also having the exact same issue as @wqoq here but with EF8. Has anybody found a solution to this? EF should really support this scenario...

[wqoq]

I'll follow-up that I did eventually go with a custom SQL script, but not because of the problem I mentioned above—EF not rebuilding the FKs. Ultimately it was because in my particular scenario, due to some poor choices early on, the GUID values were needlessly being stored in a variable-width data type (varchar) and I'm trying to convert them to a fixed-width data type (uniqueidentifier). When done as an ALTER COLUMN, this is incredibly slow.

Copying the contents to a new table with the desired data types is much faster. So to get the speed benefits, I had to abandon EF's generated SQL anyway.