EF Core trying to drop index that doesn't exist

Question

Experiencing an error when running a migration attempting to modify a column from being nullable to not-null. My model class looks like this:

public class SaleLandNonDeeded
{
    public Guid SaleLandNonDeededId { get; set; }
    public Guid SaleId { get; set; }
    public decimal? TotalValue { get; set; }

    public virtual Sale Sale { get; set; }
}

Previously, SaleId was of type Guid?. Here is the model configuration in the DbContext:

modelBuilder.Entity<SaleLandNonDeeded>(entity =>
{
    entity.Property(e => e.SaleLandNonDeededId).HasDefaultValueSql("(newid())");

    entity.Property(e => e.TotalValue).HasColumnType("money");

    entity.HasOne(d => d.Sale)
        .WithMany(p => p.SaleLandNonDeeded)
        .HasForeignKey(d => d.SaleId)
        .HasConstraintName("FK_SaleLandNonDeeded_SaleId");
});

After changing the type, a new migration was created:

// This line was added manually to delete null values before changing
// the column type.
migrationBuilder.Sql("DELETE FROM SaleLandNonDeeded WHERE SaleId IS NULL");

migrationBuilder.AlterColumn<Guid>(
    name: "SaleId",
    table: "SaleLandNonDeeded",
    type: "uniqueidentifier",
    nullable: false,
    defaultValue: new Guid("00000000-0000-0000-0000-000000000000"),
    oldClrType: typeof(Guid),
    oldType: "uniqueidentifier",
    oldNullable: true);

This fails with the following:

Microsoft.EntityFrameworkCore.Database.Command: Error: Failed executing DbCommand (34ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] DROP INDEX [IX_SaleLandNonDeeded_SaleId] ON [SaleLandNonDeeded]; DECLARE @var1 sysname; SELECT @var1 = [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'[SaleLandNonDeeded]') AND [c].[name] = N'SaleId'); IF @var1 IS NOT NULL EXEC(N'ALTER TABLE [SaleLandNonDeeded] DROP CONSTRAINT [' + @var1 + '];'); ALTER TABLE [SaleLandNonDeeded] ALTER COLUMN [SaleId] uniqueidentifier NOT NULL; ALTER TABLE [SaleLandNonDeeded] ADD DEFAULT '00000000-0000-0000-0000-000000000000' FOR [SaleId]; CREATE INDEX [IX_SaleLandNonDeeded_SaleId] ON [SaleLandNonDeeded] ([SaleId]); Exception thrown: 'Microsoft.Data.SqlClient.SqlException' in System.Private.CoreLib.dll Exception thrown: 'Microsoft.Data.SqlClient.SqlException' in System.Private.CoreLib.dll Exception thrown: 'Microsoft.Data.SqlClient.SqlException' in System.Private.CoreLib.dll An exception of type 'Microsoft.Data.SqlClient.SqlException' occurred in System.Private.CoreLib.dll but was not handled in user code Cannot drop the index 'SaleLandNonDeeded.IX_SaleLandNonDeeded_SaleId', because it does not exist or you do not have permission.

There is no IX_SaleLandNonDeeded_SaleId anywhere in the model, context, migration, or snapshot. Doing a Ctrl+F on the Entire Solution returns no matches except for the above log entries. I do not know why EF Core is attempting to drop and create this index. I did find an issue pertaining to this here: https://github.com/dotnet/efcore/issues/7535 which was way back in EF Core 1.0 and closed as fixed. I am using EF Core 5.0.

Answer 1

Definitely not the cleanest answer, but I had the same issue as you and I just added the missing index as the first line of the migration:

migrationBuilder.CreateIndex("IX_SaleLandNonDeeded_SaleId", "SaleLandNonDeeded", "SaleId");

Don't forget to also update the Down() method:

migrationBuilder.DropIndex("IX_SaleLandNonDeeded_SaleId", "SaleLandNonDeeded");

I had upgraded from EF 6 to EF core 5.0. Don't ask me why they decided to change naming conventions.