Skip to Main Content

ODP.NET

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Drop unique index problem after unique index create with efcore code first

seyda CakirNov 14 2023

We created unique index with ef core in oracle . Now , we want to modify index. but When ı tried the drop index , we are getting error "ORA-01418: specified index does not exist". When I executed create index script on dbeaver manually everything is ok and later drop index script also works .
( CREATE UNIQUE INDEX "SEYDAINDEX" ON "SEYDADENEME"."AHMET" ("NAME"))
So there is a problem when I create it via EF Core. How can I drop the index, what is the problem in creating index with efcore?

Also when we create with efcore, I can view index on dbeaver , there is index. But When I try to create ddl, I get the following error.

ef core migration code:

C#
 public partial class deneme : Migration
   {
       protected override void Up(MigrationBuilder migrationBuilder)
       {
           string schema = "SEYDADENEME";
           migrationBuilder.CreateTable(
                name: "AHMET",
                schema: "SEYDADENEME",
                columns: table => new
                {
                    Id = table.Column<long>(type: "NUMBER(19)", nullable: false)
                        .Annotation("Oracle:Identity", "1, 1"),
                    NAME = table.Column<string>(type: "NVARCHAR2(255)", maxLength: 255, nullable: false),
              
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_AHMET", x => x.Id);
                });
// index create
           migrationBuilder.Sql($"CREATE UNIQUE INDEX \"SEYDAINDEX\" ON \"{schema}\".\"AHMET\" (\"NAME\")");

       
       }
       protected override void Down(MigrationBuilder migrationBuilder)
       {
       }
   }
public partial class deneme2 : Migration
   {
       protected override void Up(MigrationBuilder migrationBuilder)
       {
           string schema = "SEYDADENEME";
          
       
           migrationBuilder.Sql($"DECLARE i INTEGER;\r\nBEGIN\r\nSELECT count(*) INTO i  FROM dba_indexes  WHERE INDEX_NAME = 'SEYDAINDEX' AND TABLE_NAME='AHMET' AND TABLE_OWNER ='{schema}';\r\n  IF i > 0   THEN\r\n  EXECUTE IMMEDIATE  ('DROP INDEX  {schema}.SEYDAINDEX');\r\n  END IF;\r\nEND;");
           migrationBuilder.Sql($"CREATE UNIQUE INDEX \"SEYDAINDEX\" ON \"{schema}\".\"AHMET\" (\"NAME\")");
       
       }
       protected override void Down(MigrationBuilder migrationBuilder)
       {
       }
   }

### Package manager console output


PM> update-database -Context NotificationOracleDbContext
Build started...
Build succeeded.
Microsoft.EntityFrameworkCore.Model.Validation[10622]
     Entity 'Adapter' has a global query filter defined and is the required end of a relationship with the entity 'TenantAdapter'. This may lead to unexpected results when the required entity is filtered out. Either configure the navigation as optional, or define matching query filters for both entities in the navigation. See https://go.microsoft.com/fwlink/?linkid=2131316 for more information.
Microsoft.EntityFrameworkCore.Infrastructure[10403]
     Entity Framework Core 5.0.4 initialized 'NotificationOracleDbContext' using provider 'Oracle.EntityFrameworkCore' with options: CommandTimeout=30 MigrationsHistoryTable=NOTIFICATION.__EFMigrationsHistory 
-----------------------------------------------------------
Dependency Injection Problems
-----------------------------------------------------------
Value cannot be null. (Parameter 'stream')
-----------------------------------------------------------
Value cannot be null. (Parameter 'stream')
-----------------------------------------------------------
Value cannot be null. (Parameter 'stream')
-----------------------------------------------------------
Value cannot be null. (Parameter 'stream')
-----------------------------------------------------------
Value cannot be null. (Parameter 'stream')
-----------------------------------------------------------
Value cannot be null. (Parameter 'stream')
-----------------------------------------------------------
Could not find file 'C:\Projects\mimarigit\NotificationService\NotificationService\ApplicationServices\Host\ef.xml'.
-----------------------------------------------------------
Could not find file 'C:\Projects\mimarigit\NotificationService\NotificationService\ApplicationServices\Host\ef.xml'.
-----------------------------------------------------------
Could not find file 'C:\Projects\mimarigit\NotificationService\NotificationService\ApplicationServices\Host\ef.xml'.
-----------------------------------------------------------
Could not find file 'C:\Projects\mimarigit\NotificationService\NotificationService\ApplicationServices\Host\ef.xml'.
-----------------------------------------------------------
Could not find file 'C:\Projects\mimarigit\NotificationService\NotificationService\ApplicationServices\Host\ef.xml'.
-----------------------------------------------------------
Could not find file 'C:\Projects\mimarigit\NotificationService\NotificationService\ApplicationServices\Host\ef.xml'.
-----------------------------------------------------------
Could not find file 'C:\Projects\mimarigit\NotificationService\NotificationService\ApplicationServices\Host\ef.xml'.
-----------------------------------------------------------
Could not find file 'C:\Projects\mimarigit\NotificationService\NotificationService\ApplicationServices\Host\ef.xml'.
-----------------------------------------------------------
Microsoft.EntityFrameworkCore.Infrastructure[10403]
     Entity Framework Core 5.0.4 initialized 'NotificationOracleDbContext' using provider 'Oracle.EntityFrameworkCore' with options: CommandTimeout=30 MigrationsHistoryTable=NOTIFICATION.__EFMigrationsHistory 
Microsoft.EntityFrameworkCore.Database.Command[20101]
     Executed DbCommand (93ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
     SELECT t.table_name FROM all_tables t WHERE t.table_name = N'__EFMigrationsHistory' AND t.owner = N'NOTIFICATION'
Microsoft.EntityFrameworkCore.Database.Command[20101]
     Executed DbCommand (37ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
     SELECT t.table_name FROM all_tables t WHERE t.table_name = N'__EFMigrationsHistory' AND t.owner = N'NOTIFICATION'
Microsoft.EntityFrameworkCore.Database.Command[20101]
     Executed DbCommand (26ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
     SELECT "MigrationId", "ProductVersion"
     FROM "NOTIFICATION"."__EFMigrationsHistory"
     ORDER BY "MigrationId"
Microsoft.EntityFrameworkCore.Migrations[20402]
     Applying migration '20231113134213_deneme'.
Applying migration '20231113134213_deneme'.
Microsoft.EntityFrameworkCore.Database.Command[20101]
     Executed DbCommand (68ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
     BEGIN 
     EXECUTE IMMEDIATE 'CREATE TABLE 
     "SEYDADENEME"."AHMET" (
         "Id" NUMBER(19) GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL,
         "NAME" NVARCHAR2(255) NOT NULL,
         CONSTRAINT "PK_AHMET" PRIMARY KEY ("Id")
     )';
     END;
Microsoft.EntityFrameworkCore.Database.Command[20101]
     Executed DbCommand (55ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
     CREATE UNIQUE INDEX "SEYDAINDEX" ON "SEYDADENEME"."AHMET" ("NAME")
Microsoft.EntityFrameworkCore.Database.Command[20101]
     Executed DbCommand (29ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
     INSERT INTO "NOTIFICATION"."__EFMigrationsHistory" ("MigrationId", "ProductVersion")
     VALUES (N'20231113134213_deneme', N'5.0.4')
Applying migration '20231113134213_deneme2'.
Microsoft.EntityFrameworkCore.Migrations[20402]
     Applying migration '20231113134213_deneme2'.
fail: Microsoft.EntityFrameworkCore.Database.Command[0]
     2023-11-13 16:15:52.792795 ThreadID:1   (ERROR)   OracleRelationalCommand.ExecuteNonQuery() :  Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-01418: belirtilen dizin mevcut değil
ORA-06512: konum  satır 5
        at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
        at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQuery(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, Boolean isFromEF)
        at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery()
        at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
2023-11-13 16:15:52.792795 ThreadID:1   (ERROR)   OracleRelationalCommand.ExecuteNonQuery() :  Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-01418: belirtilen dizin mevcut değil
ORA-06512: konum  satır 5
  at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
  at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQuery(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, Boolean isFromEF)
  at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery()
  at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
     Failed executing DbCommand (312ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
     DECLARE i INTEGER;
     BEGIN
     SELECT count(*) INTO i  FROM dba_indexes  WHERE INDEX_NAME = 'SEYDAINDEX' AND TABLE_NAME='AHMET' AND TABLE_OWNER ='SEYDADENEME';
       IF i > 0   THEN
       EXECUTE IMMEDIATE  ('DROP INDEX  SEYDADENEME.SEYDAINDEX');
       END IF;
     END;
Failed executing DbCommand (312ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
DECLARE i INTEGER;
BEGIN
SELECT count(*) INTO i  FROM dba_indexes  WHERE INDEX_NAME = 'SEYDAINDEX' AND TABLE_NAME='AHMET' AND TABLE_OWNER ='SEYDADENEME';
 IF i > 0   THEN
 EXECUTE IMMEDIATE  ('DROP INDEX  SEYDADENEME.SEYDAINDEX');
 END IF;
END;
Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-01418: belirtilen dizin mevcut değil
ORA-06512: konum  satır 5
  at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
  at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQuery(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, Boolean isFromEF)
  at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery()
  at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
  at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
  at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
  at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
  at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)
  at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
  at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
  at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ORA-01418: belirtilen dizin mevcut değil
ORA-06512: konum  satır 5
PM> dotnet ef dbcontext list --verbose
Microsoft.EntityFrameworkCore.Tools.CommandException: No project was found. Change the current working directory or use the --project option.
  at Microsoft.EntityFrameworkCore.Tools.RootCommand.ResolveProjects(String projectPath, String startupProjectPath)
  at Microsoft.EntityFrameworkCore.Tools.RootCommand.Execute(String[] _)
  at Microsoft.EntityFrameworkCore.Tools.Commands.CommandBase.<>c__DisplayClass0_0.<Configure>b__0(String[] args)
  at Microsoft.DotNet.Cli.CommandLine.CommandLineApplication.Execute(String[] args)
  at Microsoft.EntityFrameworkCore.Tools.Program.Main(String[] args)
No project was found. Change the current working directory or use the --project option.
PM> dotnet ef dbcontext list --verbose
Microsoft.EntityFrameworkCore.Tools.CommandException: No project was found. Change the current working directory or use the --project option.
  at Microsoft.EntityFrameworkCore.Tools.RootCommand.ResolveProjects(String projectPath, String startupProjectPath)
  at Microsoft.EntityFrameworkCore.Tools.RootCommand.Execute(String[] _)
  at Microsoft.EntityFrameworkCore.Tools.Commands.CommandBase.<>c__DisplayClass0_0.<Configure>b__0(String[] args)
  at Microsoft.DotNet.Cli.CommandLine.CommandLineApplication.Execute(String[] args)
  at Microsoft.EntityFrameworkCore.Tools.Program.Main(String[] args)
No project was found. Change the current working directory or use the --project option.
PM> 
```

### Include provider and version information

EF Core version: Entity Framework Core 5.0.4
Database provider: (Oracle.EntityFrameworkCore 5.21.1)
Target framework: (NET Core 3.1)
Operating system: Windows
IDE: (e.g. Visual Studio 2022 17.6.5)

This post has been answered by Solomon Yakobson on Nov 14 2023
Jump to Answer
Comments
Post Details
Added on Nov 14 2023
10 comments
160 views