SqlServer Migrations: Rebuild primary keys when column type changed

[chaim1221]

Describe what is not working as expected.

Okay, so the scenario is, I have a database-first EF Core migration called "Prehistoric" and then a migration on top of it with a newer schema called "Initial Migration." This is because I generated the "Prehistoric" migration from the schema of a .NET 4.5.1 project using EF 6. Then, I created a new migration to handle the changes to EF Core and get rid of some unused tables.

The new migration was done with ef migrations add. The old migration is just a database first migration that has the entire legacy schema. The migrations are in different tables for the different phases of the project, and the older migrations history is deleted by EF Core in the new migration. No worries, I don't really need it (yet).

In any case, I can't undo the migration created with EF Core, because I get this error. I can't drop the PK constraint on the table (as far as I know).

If you are seeing an exception, include the full exceptions details (message and stack trace).

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (49ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      DECLARE @var18 sysname;
      SELECT @var18 = [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'[AspNetUsers]') AND [c].[name] = N'Id');
      IF @var18 IS NOT NULL EXEC(N'ALTER TABLE [AspNetUsers] DROP CONSTRAINT [' + @var18 + '];');
      ALTER TABLE [AspNetUsers] ALTER COLUMN [Id] nvarchar(128) NOT NULL;
System.Data.SqlClient.SqlException (0x80131904): The object 'PK_dbo.AspNetUsers' is dependent on column 'Id'.
ALTER TABLE ALTER COLUMN Id failed because one or more objects access this column.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
ClientConnectionId:5da82a16-4a29-43a3-b7da-372e63aa2afe
Error Number:5074,State:1,Class:16
Failed executing DbCommand (49ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
DECLARE @var18 sysname;
SELECT @var18 = [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'[AspNetUsers]') AND [c].[name] = N'Id');
IF @var18 IS NOT NULL EXEC(N'ALTER TABLE [AspNetUsers] DROP CONSTRAINT [' + @var18 + '];');
ALTER TABLE [AspNetUsers] ALTER COLUMN [Id] nvarchar(128) NOT NULL;
System.Data.SqlClient.SqlException (0x80131904): The object 'PK_dbo.AspNetUsers' is dependent on column 'Id'.

etc.

Steps to reproduce

Include a complete code listing (or project/solution) that we can run to reproduce the issue.

Honestly, I have no idea. The project as it stands is located at https://github.com/chaim1221/Machete (master is having the problem). It's not really a problem for everyone who wants to stay on the new system, but we may need to roll one client back because our new code is having trouble handling the volume, and we can't, because of this issue.

Partial code listings, or multiple fragments of code, will slow down our response or cause us to push the issue back to you to provide code to reproduce the issue.

I understand but I really have no idea how to manually reproduce this.

Further technical details

EF Core version: 2.2.4
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Mac OS X for dev, Debian 9 in prod. Prod does not matter to this problem.
IDE: Rider 2019.1.1 (but I am not using the IDE, I am using the CLI).

[chaim1221]

It's worth noting that the above-linked project is an MVVM type project with the service logic and the entity definitions in separate layers. So the EF stuff is in "Data" (but naturally we run the migrations through the MVC project, Web).

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

[ajcvickers]

@marchy See Using MigrationBuilder.Sql().

[marchy]

Thanks @ajcvickers, I meant what are the actual SQL commands to issue.

However we ended up figuring out a much nicer way to do it with a two-part migration:

Part I: Create a migration (ie: MyMigration_Part1) where you temporarily sever the referencing foreign keys

class MyDBContext : DbContext {
	...

	class RegionConfig : IEntityTypeConfiguration<Region> {
		public void Configure( EntityTypeBuilder<Region> region ){
			region.HasKey( _ => _.Identifier )
				.IsClustered(); // NOTE: requires column rebuilding
			region.Property( _ => _.Identifier )
				.HasMaxLength( ColumnLength_RegionIdentifier ); // NOTE: requires foreign keys rebuilding
				
			// .. other field mappings
		}
	}

	// NOTE: Do this for all entities that reference the entity with the table columns that need to be rebuilt:
	class SomeReferencingEntityConfig : IEntityTypeConfiguration<SomeReferencingEntityConfig> {
		public void Configure( EntityTypeBuilder<SomeReferencingEntityConfig> referencingEntity ){
			// .. other field mappings
			
			/*TEMP*/referencingEntity.Ignore( _ => _.Region );
//			referencingEntity.HasOne( _ => _.Region )
//				.WithMany()
//				.HasForeignKey( _ => _.RegionIdentifier );

			// .. other field mappings
		}
	}
	
	...

This will remove all the existing foreign keys and indexes to the original table, allowing the table-rebuilding change to go through EF alone.

Part II: Create a migration (ie: MyMigration_Part2) where you revive the reference fields.

class MyDBContext : DbContext {
	...

	class SomeReferencingEntityConfig : IEntityTypeConfiguration<SomeReferencingEntityConfig> {
		public void Configure( EntityTypeBuilder<SomeReferencingEntityConfig> referencingEntity ){
			// .. other field mappings
			
			referencingEntity.HasOne( _ => _.Region )
				.WithMany()
				.HasForeignKey( _ => _.RegionIdentifier );

			// .. other field mappings
		}
	}
	
	...

Worked blissfully!

No data was dropped from any of the tables since it's only foreign keys that indexes that got dropped in the first-part migration – then re-added in the second part. And EF did all the model delta detections correctly with no hand-written SQL needed. 🚀