SqlServer Migrations: Rebuild foreign keys

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);
        }

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

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;

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 TestEntityChildrenentity, 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.

hi @bricelam is there a temporary solution? Thanks.

@bricelam This repros for me on 3.0 bits. The issue happens when attempting to change the length of a string primary key column that is referenced by an FK. More minimal code below. Use as-is to generate the first migration and create the database. Then change the value passed to HasMaxLength, add a new migration, and update the database.

public class Blog
{
    public string Id { get; set; }

    public ICollection<Post> Posts { get; set; }
}

public class Post
{
    public string Id { get; set; }
    public Blog Blog { get; set; }
}

public class BloggingContext : DbContext
{
    private readonly ILoggerFactory Logger = LoggerFactory.Create(c => c.AddConsole());

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseLoggerFactory(Logger)
            .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Blog>().Property(e => e.Id).HasMaxLength(10);
    }
}

public class Program
{
    public static async Task Main()
    {
    }
}

Output:

PM> add-migration One
Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 3.0.0 initialized 'BloggingContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: None
To undo this action, use Remove-Migration.
PM> update-database
Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 3.0.0 initialized 'BloggingContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: None
Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='60']
      CREATE DATABASE [Test];
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='60']
CREATE DATABASE [Test];
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='60']
IF SERVERPROPERTY('EngineEdition') <> 5
BEGIN
    ALTER DATABASE [Test] SET READ_COMMITTED_SNAPSHOT ON;
END;
Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='60']
      IF SERVERPROPERTY('EngineEdition') <> 5
      BEGIN
          ALTER DATABASE [Test] SET READ_COMMITTED_SNAPSHOT ON;
      END;
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE [__EFMigrationsHistory] (
    [MigrationId] nvarchar(150) NOT NULL,
    [ProductVersion] nvarchar(32) NOT NULL,
    CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId])
);
Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [__EFMigrationsHistory] (
          [MigrationId] nvarchar(150) NOT NULL,
          [ProductVersion] nvarchar(32) NOT NULL,
          CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId])
      );
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT OBJECT_ID(N'[__EFMigrationsHistory]');
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];
Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [MigrationId], [ProductVersion]
      FROM [__EFMigrationsHistory]
      ORDER BY [MigrationId];
Microsoft.EntityFrameworkCore.Migrations[20402]
      Applying migration '20191028223437_One'.
Applying migration '20191028223437_One'.
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE [Blog] (
    [Id] nvarchar(10) NOT NULL,
    CONSTRAINT [PK_Blog] PRIMARY KEY ([Id])
);
Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [Blog] (
          [Id] nvarchar(10) NOT NULL,
          CONSTRAINT [PK_Blog] PRIMARY KEY ([Id])
      );
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE [Post] (
    [Id] nvarchar(450) NOT NULL,
    [BlogId] nvarchar(10) NULL,
    CONSTRAINT [PK_Post] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_Post_Blog_BlogId] FOREIGN KEY ([BlogId]) REFERENCES [Blog] ([Id]) ON DELETE NO ACTION
);
Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [Post] (
          [Id] nvarchar(450) NOT NULL,
          [BlogId] nvarchar(10) NULL,
          CONSTRAINT [PK_Post] PRIMARY KEY ([Id]),
          CONSTRAINT [FK_Post_Blog_BlogId] FOREIGN KEY ([BlogId]) REFERENCES [Blog] ([Id]) ON DELETE NO ACTION
      );
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE INDEX [IX_Post_BlogId] ON [Post] ([BlogId]);
Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE INDEX [IX_Post_BlogId] ON [Post] ([BlogId]);
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20191028223437_One', N'3.0.0');
Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
      VALUES (N'20191028223437_One', N'3.0.0');
Done.
PM> add-migration Two
Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 3.0.0 initialized 'BloggingContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: None
An operation was scaffolded that may result in the loss of data. Please review the migration for accuracy.
To undo this action, use Remove-Migration.
PM> update-database
Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 3.0.0 initialized 'BloggingContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: None
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]');
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];
Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [MigrationId], [ProductVersion]
      FROM [__EFMigrationsHistory]
      ORDER BY [MigrationId];
Microsoft.EntityFrameworkCore.Migrations[20402]
      Applying migration '20191028223518_Two'.
Applying migration '20191028223518_Two'.
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
DROP INDEX [IX_Post_BlogId] ON [Post];
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'[Post]') AND [c].[name] = N'BlogId');
IF @var0 IS NOT NULL EXEC(N'ALTER TABLE [Post] DROP CONSTRAINT [' + @var0 + '];');
ALTER TABLE [Post] ALTER COLUMN [BlogId] nvarchar(100) NULL;
CREATE INDEX [IX_Post_BlogId] ON [Post] ([BlogId]);
Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      DROP INDEX [IX_Post_BlogId] ON [Post];
      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'[Post]') AND [c].[name] = N'BlogId');
      IF @var0 IS NOT NULL EXEC(N'ALTER TABLE [Post] DROP CONSTRAINT [' + @var0 + '];');
      ALTER TABLE [Post] ALTER COLUMN [BlogId] nvarchar(100) NULL;
      CREATE INDEX [IX_Post_BlogId] ON [Post] ([BlogId]);
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (72ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      DROP INDEX [IX_Post_BlogId] ON [Post];
      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'[Post]') AND [c].[name] = N'BlogId');
      IF @var0 IS NOT NULL EXEC(N'ALTER TABLE [Post] DROP CONSTRAINT [' + @var0 + '];');
      ALTER TABLE [Post] ALTER COLUMN [BlogId] nvarchar(100) NULL;
      CREATE INDEX [IX_Post_BlogId] ON [Post] ([BlogId]);
Failed executing DbCommand (72ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
DROP INDEX [IX_Post_BlogId] ON [Post];
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'[Post]') AND [c].[name] = N'BlogId');
IF @var0 IS NOT NULL EXEC(N'ALTER TABLE [Post] DROP CONSTRAINT [' + @var0 + '];');
ALTER TABLE [Post] ALTER COLUMN [BlogId] nvarchar(100) NULL;
CREATE INDEX [IX_Post_BlogId] ON [Post] ([BlogId]);
Microsoft.Data.SqlClient.SqlException (0x80131904): The object 'FK_Post_Blog_BlogId' is dependent on column 'BlogId'.
ALTER TABLE ALTER COLUMN BlogId 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:7575ca23-57e9-4014-8943-5df20b66e653
Error Number:5074,State:1,Class:16
The object 'FK_Post_Blog_BlogId' is dependent on column 'BlogId'.
ALTER TABLE ALTER COLUMN BlogId failed because one or more objects access this column.
PM>

I just got bit by this today in a pretty bad way, are there any known workarounds other than just manually adding drop/create of all the affected indices?

manually adding drop/create of all the affected indices

Probably the only workaround for now

I had the same issue on a column which has index linked to it. When I tried to change the length of the column, I received this error:

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

The Email column was allowing Null (which was wrong from the beginning in my case), so I added ".IsRequired()" to the column in the migration and it worked.

Old (Not Working)

entity.Property(e => e.Email).HasMaxLength("254");

New (Working)

entity.Property(e => e.Email).IsRequired().HasMaxLength("254");

@maliming In your code you have the same issue. In the first migration you created this index on Nullable column "NormalizedName " (Which could be right but you can change it as a workaround and try again):

NormalizedName = table.Column<string>(maxLength: 256, nullable: true)

migrationBuilder.CreateIndex(
                name: "RoleNameIndex",
                table: "AspNetRoles",
                column: "NormalizedName",
                unique: true,
                filter: "[NormalizedName] IS NOT NULL");

Also running into this.. trying to reduce my Id columns from varchar(450) -> varchar(27)

Does anyone know if there is a way to tell the modelBuilder to drop all indexes so I can at least try to programatically create the migrations that drop all the indexes (and re-create them in the Down() method.)

Edit: I worked around this by writing hand crafted SQL to drop all FK and PK constraints and then re-create them. This is done by querying the sys tables and constructing the ALTER TABLE statements to re-create them before dropping from the table

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];

FWIW, this is an issue for my team too.

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

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.)

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.

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.

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...

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.

dotnet / efcore

SqlServer Migrations: Rebuild foreign keys #12586