Error in EF Core index generation with Npgsql - incorrect COLLATE placement

[gabrielrobert]

Context

I'm trying to create an index using the EF Core API (Npgsql). The index can be created manually using this statement:

CREATE INDEX "IX_Affiliations_Name" ON "Affiliations" USING gist ("Name" COLLATE ignore_accent gist_trgm_ops);.

However, EF Core generates this:

CREATE INDEX "IX_Affiliations_Name" ON "Affiliations" USING gist ("Name" gist_trgm_ops COLLATE ignore_accent);.

The COLLATE statement should be placed before the gist_trgm_ops operation.

Code

builder.HasPostgresExtension("pg_trgm");
builder.HasCollation("ignore_accent", locale: "und-u-ks-level1-kc-true", provider: "icu", deterministic: false);

builder.Entity<Affiliation>(entity =>
{
    entity.Property(a => a.Name)
        .UseCollation("ignore_accent")
        .IsRequired();

    entity.HasIndex(a => a.Name)
        .HasMethod("gist")
        .UseCollation("ignore_accent")
        .HasOperators("gist_trgm_ops");
});

Stack trace

[16:24:14 ERR] Failed executing DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='0']
CREATE INDEX "IX_Affiliations_Name" ON "Affiliations" USING gist ("Name" gist_trgm_ops COLLATE ignore_accent);

[16:24:14 ERR] an error occured while initializing the app
Npgsql.PostgresException (0x80004005): 42601: syntax error at or near "COLLATE"

POSITION: 102
   at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|234_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.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.RelationalDatabaseFacadeExtensions.Migrate(DatabaseFacade databaseFacade)
  Exception data:
    Severity: ERROR
    SqlState: 42601
    MessageText: syntax error at or near "COLLATE"
    Position: 102
    File: scan.l
    Line: 1176
    Routine: scanner_yyerror
Unhandled exception. Npgsql.PostgresException (0x80004005): 42601: syntax error at or near "COLLATE"

POSITION: 102
   at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|234_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.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.RelationalDatabaseFacadeExtensions.Migrate(DatabaseFacade databaseFacade)
  Exception data:
    Severity: ERROR
    SqlState: 42601
    MessageText: syntax error at or near "COLLATE"
    Position: 102
    File: scan.l
    Line: 1176
    Routine: scanner_yyerror

Include provider and version information

EF Core version: 7.0.11
Database provider: Npgsql
Target framework: (e.g. .NET 7.0)

[roji]

Thanks, moved to npgsql/efcore.pg#3027 as this is an Npgsql-specific bug.