EF Core not including schema when generating DROP INDEX command in migration

I am using Entity Framework Core with Oracle and configuring the default schema in my context using HasDefaultSchema with the schema PRODUCTMAX. Index creation works correctly, but when I try to drop an index, the command generated by the migration does not include the schema, resulting in an error.

In my code, I am using migrationBuilder.DropIndex with the schema, index name, and table name parameters:

migrationBuilder.DropIndex(
    name: "IX_ANALISENAOCONFORMIDADE_ANALISEID_TEXTO",
    schema: "PRODUCTMAX",
    table: "ANALISENAOCONFORMIDADE");

I expect this to generate the command:

DROP INDEX PRODUCTMAX.IX_ANALISENAOCONFORMIDADE_ANALISEID_TEXTO

But what is actually generated in the migration is:

DROP INDEX IX_ANALISENAOCONFORMIDADE_ANALISEID_TEXTO

Does anyone know why this is happening and how I can fix it so that the schema is correctly included in the DROP INDEX command?

Assuming you are using Oracle.EntityFrameworkCore database provider. EF Core database providers are responsible for implementing database specifics, and this particular provider even in the latest at this time version has a bug in the implementation of the service method responsible for generating SQL for DropIndexOperation (ignoring the Schema property).

The best is to get this fixed in the provider. The fix is quite simple, but for some reason the maintainers are not doing it for a long time. Luckily, EF Core is built with services architecture where you can replace each service with your own, which allows you to enhance / fix / customize their behaviors.

So the workaround I could offer is following the typical approach of replacing some service implementation class with custom class inheriting from the original, and overriding one or more methods needed. In this particular case, we need to replace OracleMigrationsSqlGenerator and override Generate(DropIndexOperation operation... method.

Add the following in a code file of your choice (for instance CustomOracleServices.cs) in the project where your derived DbContext class resides:

using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.EntityFrameworkCore.Migrations.Operations;
using Oracle.EntityFrameworkCore.Infrastructure.Internal;
using Oracle.EntityFrameworkCore.Migrations;

namespace Microsoft.EntityFrameworkCore
{
    public static class CustomOracleServices
    {
        public static DbContextOptionsBuilder UseCustomOracleServices(this DbContextOptionsBuilder optionsBuilder)
            => optionsBuilder.ReplaceService<IMigrationsSqlGenerator, OracleMigrationsSqlGenerator, CustomOracleMigrationsSqlGenerator>();
    }
}

namespace Microsoft.EntityFrameworkCore.Migrations
{
    public class CustomOracleMigrationsSqlGenerator : OracleMigrationsSqlGenerator
    {
#pragma warning disable EF1001 // Internal EF Core API usage.
        public CustomOracleMigrationsSqlGenerator(MigrationsSqlGeneratorDependencies dependencies, IOracleOptions options) : base(dependencies, options) { }
#pragma warning restore EF1001 // Internal EF Core API usage.

        protected override void Generate(DropIndexOperation operation, IModel? model, MigrationCommandListBuilder builder, bool terminate)
        {
            builder.Append("DROP INDEX ").Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name, operation.Schema));
            if (terminate) builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator).EndCommand();
        }
    }
}

Most of this is just usings and plumbing code, with the only essential part being passing operation.Schema to DelimitIdentifier which is missing in the original code. Too much for my (and I guess anyone) taste, but at least it allows you to fix something which otherwise you'd be stuck.

Anyway, the last part needed to make this work is to override OnConfiguring method of your db context class and call the convenient UseCustomOracleServices method I've added, e.g.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    // ...
    optionsBuilder.UseCustomOracleServices();
    // ...
}

And that's it - problem solved. In case someone complains about "internal API" warning, all that means is when upgrading the EF Core / db provider version, check if there is some change in the "internal API" and your code is still compiling / functioning. And of course if they fix the issue in some future version, just remove all the custom code used to workaround it.