SQL Server: ALTER COLUMN on a column under a complex index fails at apply (Msg 5074) #84

Problem

EF Core drops and re-creates its own indexes around an ALTER COLUMN on SQL Server. A complex index is not in EF's model, so it stays in place, and SQL Server refuses the statement:

b.ComplexProperty(x => x.Email, c => c.Property(x => x.Value).HasMaxLength(200));   // then 100
b.HasComplexIndex(x => x.Email.Value, isUnique: true, indexName: "ux_members_email");

The second migration scaffolds only

ALTER TABLE [members] ALTER COLUMN [Email_Value] nvarchar(100) NOT NULL;

and fails at apply: The index 'ux_members_email' is dependent on column 'Email_Value'. (Msg 5074, reproduced on Azure SQL Edge). Every change of length, type or nullability of a column a complex index reads (key, INCLUDE or filter) blocks deployment until the migration is edited by hand.

Loud. Found by the 5.5.0 pre-tag analysis (core and SQL Server). Present since the SQL Server satellite shipped.

Fix

An unchanged complex index whose column the base operations alter is dropped before them and created after them, like a changed one. A protected virtual on the core differ, RequiresIndexRecreation(operation, columns), says which base operations need that; off in the core, ALTER COLUMN on a column the index reads in the SQL Server satellite.

Related

SQLite has the silent version of this problem (its table rebuild drops complex indexes), which the differ cannot fix the same way; tracked separately.