Unable to make a column sparse (in SQL Server) if it has an index

[dougwaldron]

Bug description

If you try to convert an existing column that has an index to a sparse column, it fails with:

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

Note that it's possible to manually accomplish this in the database by deleting the index first and then recreating it after modifying the column:

drop index IX_MyTable_RareProperty on dbo.MyTable

alter table dbo.MyTable alter column RareProperty add sparse

create index IX_MyTable_RareProperty on dbo.MyTable (RareProperty)

The only workaround I can think of is to hand-edit the migration file to delete and recreate each index.

Your code

I updated the OnModelCreating method to enable sparse columns:

builder.Entity<Fce>().Property(e => e.DeletedById).IsSparse();

I didn't explicitly create this index; it was added by Entity Framework I assume because it's a foreign key:

public ApplicationUser? DeletedBy { get; set; }

Stack traces

Microsoft.Data.SqlClient.SqlException (0x80131904): The index 'IX_Fces_DeletedById' is dependent on column 'DeletedById'.
ALTER TABLE ALTER COLUMN DeletedById 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.Connection.SqlConnectionInternal.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, 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.InternalEndExecuteNonQuery(IAsyncResult asyncResult, Boolean isInternal, String endMethod)
   at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult)
   at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryAsync(IAsyncResult asyncResult)
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<InternalExecuteNonQueryAsync>b__251_1(IAsyncResult asyncResult)
   at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location ---
   at Datadog.Trace.ClrProfiler.CallTarget.Handlers.Continuations.TaskContinuationGenerator`4.SyncCallbackHandler.ContinuationAction(Task`1 previousTask, TTarget target, CallTargetState state) in c:\mnt\tracer\src\Datadog.Trace\ClrProfiler\CallTarget\Handlers\Continuations\TaskContinuationGenerator`1.cs:line 140
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteAsync(IReadOnlyList`1 migrationCommands, IRelationalConnection connection, MigrationExecutionState executionState, Boolean beginTransaction, Boolean commitTransaction, Nullable`1 isolationLevel, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteAsync(IReadOnlyList`1 migrationCommands, IRelationalConnection connection, MigrationExecutionState executionState, Boolean beginTransaction, Boolean commitTransaction, Nullable`1 isolationLevel, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IReadOnlyList`1 migrationCommands, IRelationalConnection connection, MigrationExecutionState executionState, Boolean commitTransaction, Nullable`1 isolationLevel, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.MigrateImplementationAsync(DbContext context, String targetMigration, MigrationExecutionState state, Boolean useTransaction, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.MigrateImplementationAsync(DbContext context, String targetMigration, MigrationExecutionState state, Boolean useTransaction, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.<>c.<<MigrateAsync>b__22_1>d.MoveNext()
--- End of stack trace from previous location ---
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.MigrateAsync(String targetMigration, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.MigrateAsync(String targetMigration, CancellationToken cancellationToken)
   at AirWeb.WebApp.Platform.AppConfiguration.DataPersistence.ApplyEfMigrations(IHostApplicationBuilder builder) in D:\projects\air-web\src\WebApp\Platform\AppConfiguration\DataPersistence.cs:line 28
   at AirWeb.WebApp.Platform.AppConfiguration.DataPersistence.ApplyEfMigrations(IHostApplicationBuilder builder) in D:\projects\air-web\src\WebApp\Platform\AppConfiguration\DataPersistence.cs:line 29
   at AirWeb.WebApp.Platform.AppConfiguration.DataPersistence.ConfigureDataPersistenceAsync(IHostApplicationBuilder builder) in D:\projects\air-web\src\WebApp\Platform\AppConfiguration\DataPersistence.cs:line 22
   at Program.<Main>$(String[] args) in D:\projects\air-web\src\WebApp\Program.cs:line 48
   at Program.<Main>(String[] args)

Verbose output

EF Core version

10.0.10

Database provider

Microsoft.EntityFrameworkCore.SqlServer

Target framework

.NET 10

Operating system

N/A

IDE

N/A