Migration: Changing collation of a PK column fails

[clement911]

EF Core version: 7.0.3 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: NET 7.0 Operating system: All

Let's say you have a entity with a string primary key.

   public class MyEntity
    {
        [Key]
        public string MyPk { get; set; }
    }

You create and run a migration so that the table is created.

Then you change the collation of the column:

builder.Entity<MyEntity>().Property(n => n.MyPk).UseCollation("Latin1_General_100_BIN2");

You create a new migration, but it fails to run because it tries to ALTER the column without first dropping the PK constraint and creating it again at the end.

[ajcvickers]

Note from triage: this is tricky because it requires dropping all the FK constraints before the PK constraint can be dropped, and then rebuilding them all again afterwards. (Unless a way can be found to allow the PK constraint to be dropped while keeping the FK constraints. Maybe by temporarily creating a unique constraint on the PK column?)

[clement911]

Regarding temporarily creating a unique index on the PK to avoid recreating the FKs, I don't think this will work. Altering the collation changes the sorting order of the column, so sql server won't let you alter the collation if there is any index using that column.

So, I think the only way is to drop all the FKs, all the indexes, and the PK itself, alter the collation, and then recreate the PK, indexes and FKs.

Yes it's tricky but doesn't EF already handles similar migration scenarios where all the FKs and the PK need to be recreated? Isn't it a similar scenario to changing the data type of a PK column?

[ajcvickers]

@clement911 As far as I am aware, nothing that requires rebuilding the PK constraint is currently handled.

[clement911]

@ajcvickers if you change the data type of the PK, it would recreate the PK constraint, right? I'm pretty sure I've seen this. For example, if I change the PK column from int to long, it would drop and recreate the PK. Unless I'm missing something?

[ajcvickers]

@clement911 Nope; just tried it:

Microsoft.Data.SqlClient.SqlException (0x80131904): The object 'PK_Posts' is dependent on column 'Id'.
ALTER TABLE ALTER COLUMN Id 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 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)

[clement911]

@ajcvickers you're right. However, it does drop/recreate the PK if you add an additional column to the primary key.

[clement911]

Interestingly, if any indexes defined with HasIndex() use the column for which the collation was changed, EF WILL recreate those indexes.