PostgreSQL Index

[LuciferTheSatan]

I am using PostgreSQL version 16.4.

What is the correct way to post-create an index for an existing field? First I tried this:

public class People
{
[Index]
public string DisplayName { get; set; }
}
public override void Up()
{
Db.AlterColumn(x => x.DisplayName);
}
public override void Down()
{
Db.DropIndex(“idx_people_displayname”);
}
then the migration runs to the following error:

I tried this:

Db.AlterColumn(table: “People”, new FieldDefinition { Name = “DisplayName”, FieldType = >typeof(string), IsIndexed = true});

then the following error:

I have modified:

Db.AlterColumn(table: “People”, new FieldDefinition { Name = “DisplayName”, FieldType = >typeof(string), IsIndexed = true, IsNullable=true });

Then it no longer runs on error, but it also doesn’t create an index.

The third method works only:

Db.CreateIndex(x => x.DisplayName, “idx_people_displayname”);

in this case, however, the revert drop index generates incorrect SQL:

Db.DropIndex(“idx_people_displayname”);

What am I messing up?

NamingStrategy is changed to OrmLiteNamingStrategy because otherwise ASP.NET Core Identity is not showing properly in Admin UI, I don’t know if this is causing the problem?

[layoric]

Best to just use a Db.ExecuteSql to handle the dropping of the index since currently there isn’t a way for the Postgres provider to override the default syntax around dropping an index. Something we’ll have to look into how to support this in a non disruptive way.

As for the Naming strategy and Admin UI, could you create a minimal reproduction of the issue you are experiencing and push it to a GitHub repository? That way we can take a look and hopefully resolve the problem.

[LuciferTheSatan]

Yes, of course: TestApp

I created a Blazor WASM project from this template:

I have created an empty PostgreSQL database, run the migrate

dotner run --AppTasks=migrate,

and run the admin-ui

But if I change the, the problem is solved

PostgreSqlDialect.Provider.NamingStrategy = new OrmLiteNamingStrategyBase();
services.AddSingleton(new OrmLiteConnectionFactory(
connectionString, PostgreSqlDialect.Provider));

[layoric]

I understand the issue now thanks for the reproduction. Since OrmLite is driving admin UI and the template was created with the default setup for EF Core, it doesn’t adhere to change in naming strategies without overriding the ApplicationDbContext and related ModelBuilder methods. Using OrmLiteNaming strategy to make your naming consistent across both is likely the path of least resistance since the API request uses the configured dialect and naming strategy to make the query when quoting the table name.

[LuciferTheSatan]

Ok, that’s about right, thanks!