Devart.Data.Oracle.Entity.EFCore can not but index at the same schema as the table

[jamir.araujo]

Hey, I'm having an issue using EFCore Migrations. I have a table under an specific schema, and I'm creating an index for some column of this table. When I generate the migration script, the script does not put the index in the same schema as the table.

From instance, this entity configuration:

Code: Select all

modelBuilder.Entity<Setting>(b =>
{
    b.ToTable("Settings", "SomeSchema");

    b.HasKey(s => s.Id);

    b.HasIndex(s => s.Key);
});

It will generate the following create index in the migration file:

Code: Select all

migrationBuilder.CreateIndex(
    name: "IX_Settings_Key",
    schema: "SomeSchema",
    table: "Settings",
    column: "Key");

And the create index statment in the script will be this:

Code: Select all

CREATE INDEX IX_Settings_Key ON SomeSchema.Settings (Key)

Now, If I perform this migration connected with the AllFather user, the index owner ends up being the AllFather schema, with the table owner being the SomeSchema. This becuase the create index statment does not provide the schema owner of the index.

When I was creating the index I was expecting the index to be placed at the same schema as the table.

Looking at the CreateIndexOperation class.

Code: Select all

public class CreateIndexOperation : MigrationOperation
{
    public CreateIndexOperation();
    public virtual bool IsUnique { get; set; }
    public virtual string Name { get; set; }
    public virtual string Schema { get; set; }
    public virtual string Table { get; set; }
    public virtual string[] Columns { get; set; }
    public virtual string Filter { get; set; }
}

It contains a Schema property, but it is used as the table schema, not the index schema.

The summary of the property says to following:

The schema that contains the index, or null if the default schema should be used.

I understand that changing the purpose of the property would probably cause a breaking change.

But it would be nice if you could put some extension on the IndexBuilder class, that added one annotation to inform the index schema. This annotation, if present, would be used to build the create index statement.

Something like this:

Code: Select all

b.HasIndex(s => s.Key)
    .HasSchema("SomeSchema")

This would generate the following migration script:

Code: Select all

CREATE INDEX SomeSchema.IX_Settings_Key ON SomeSchema.Settings (Key)

[entwicklungsensis]

This is the Oracle Forum!?

[jamir.araujo]

I'm sorry, I put PostgreSQL in the title by mistake.

[Shalex]

Code: Select all

migrationBuilder.CreateIndex(
    name: "IX_Settings_Key",
    schema: "SomeSchema",
    table: "Settings",
    column: "Key");

->

Code: Select all

CREATE INDEX IX_Settings_Key ON SomeSchema.Settings (Key)

We have reproduced the problem with applying the specified schema to CREATE INDEX. Your migration should have generated the following SQL:

Code: Select all

CREATE INDEX SomeSchema.IX_Settings_Key ON SomeSchema.Settings (Key)

We will notify you when the issue is fixed.

[jamir.araujo]

Any news on the issue?

[Shalex]

There is no timeframe at the moment. We will notify you about the progress.

[jamir.araujo]

Any news about this essue?

[Shalex]

We are going to provide a build with the fix in one month.

[Shalex]

The bug with ignoring schema set for indexes via CreateIndex(), DropIndex(), AddUniqueConstraint(), DropUniqueConstraint() operations of EF Core Code-First Migrations is fixed in dotConnect for Oracle v9.14.1180: viewtopic.php?f=1&t=44351.

Additional features in the new build:
* The new config.DatabaseScript.Index.UseCurrentSchema option (default value is false) is added for ability to ignore schema set for indexes via EF Core Code-First Migrations
* The behavior is changed: config.DatabaseScript.Index.UseCurrentSchema is set to false by default; as a result, the schema argument in CreateIndex(), DropIndex(), AddUniqueConstraint(), DropUniqueConstraint() operations is now applied not only to the table name, but also to the index name, i.e. previously, the index was always created in the current schema, and now it is created in the schema of the table to which the index belongs