Forum Stats

  • 3,854,267 Users
  • 2,264,344 Discussions
  • 7,905,625 Comments

Discussions

EntityFramework DropIndex ommits Schema while generating migration script

Piotr Lewandowski
Piotr Lewandowski Member Posts: 7 Green Ribbon
edited Jun 1, 2022 2:52PM in ODP.NET

I have a problem with generating migration script from existing migration files, via command line, either Script-Migration or dotnet ef migrations script.

The migration file contains the following:

      migrationBuilder.DropIndex(
        name: "IX_CAR_CAR_ID",
        schema: "AUTO",
        table: "CARS",
        column: "CAR_ID");

The generated SQL script contains SQL that fails during database update.

Expected result:

SQL script should contain schema in the drop index command:

DROP INDEX "AUTO"."IX_CAR_CAR_ID"

Actual result:

SQL script does not contain schema in the drop index command:

DROP INDEX "IX_CAR_CAR_ID"

I have fixed that by manual modification of the migration file, so instead of migrationBuilder.DropIndex, I use

migrationBuilder.Sql("DROP INDEX \"AUTO\".\"IX_CAR_CAR_ID\""). 

Well, that solves the problem for me, but does not remove the cause.

I have tried the same with MsSQL server and the result is valid:

DROP INDEX [IX_CAR_CAR_ID] ON [AUTO].[CARS];

so, I assume it might be Oracle only related issue.

Using:

  • Microsoft.EntityFrameworkCore version 5.0.17
  • Oracle.EntityFrameworkCore version 5.21.61

My question: does anybony know whether this is a bug in one of the Oracle packages?

Tagged:

Answers

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 3,129 Employee

    When I run the DROP statement generated by Oracle EF Core, it runs successfully without requiring the schema name. I'm curious why you need the schema appended to the index. What is your specific situation in which you have to define the schema name?

    As for whether this is a bug, that's hard to say. There's no EF Core provider spec to define what the expected behavior should be. It could answer questions, such as whether the schema needs to be appended when a DB server's schema is synonymous with the user (i.e. Oracle) and when they are two separate entities (i.e. SQL Server).

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 3,129 Employee

    I've filed a bug (34269399) for this issue so that we can correct the behavior.

  • Piotr Lewandowski
    Piotr Lewandowski Member Posts: 7 Green Ribbon

    @Alex Keh-Oracle thanks for looking into this.

    I should have explained why I need the schema in the DROP index expression.

    We execute the migration script under another user, which is not the same as the schema, so that we need to provide the schema, otwerwise we get "index not found" message.