Support collations not in the default schema

[GasyTek]

Hello ,

I try to create a collation and use it on a column by using fluent configuration. The issue is that when I execute EF Core migration command "update-database", the generated SQL to associate the collation to my column is not correct.

Here is the code on my Db context :

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.HasDefaultSchema("app");

    modelBuilder.HasCollation(name: "case_insensitive", locale: "en-u-ks-primary", provider: "icu", deterministic: false);
    
    modelBuilder.ApplyConfigurationsFromAssembly(Assembly.GetExecutingAssembly());
    base.OnModelCreating(modelBuilder);
}

And here is the configuration of my entity

public class ProtocolConfiguration : IEntityTypeConfiguration<Protocol>
    {

        public void Configure(EntityTypeBuilder<Protocol> builder)
        {
            builder.Property(it => it.ProtocolBusinessId)
                .IsRequired()
                .HasMaxLength(100)
                .UseCollation("case_insensitive")
                .HasConversion(
                    clrValue => clrValue.Value,
                    dbValue => new ProtocolBusinessId(dbValue));           
        }
    }

The 'case_insensitive' collation is generated within the 'app' database schema but the SQL generator do not generate the correct SQL and fails.

' This is the SQL executed when running update-database command
' Note that the collation name do not include the schema name and that makes the query fail
ALTER TABLE app."Protocol" ALTER COLUMN "ProtocolBusinessId" TYPE character varying(100) COLLATE **case_insensitive**;

Any idea or suggestion on how I can work around this issue ?

Thanks !

Riana

[roji]

@GasyTek yeah, collections in the non-default schema aren't well-supported - I'll try to take a look at this soon. In the meantime define the collation in public explicitly (by adding schema: public to the HasCollation call).

[roji]

Note to self:

• Add a model-building UseCollation overload that accepts a schema (and make the default one use the default schema)
• Same for the query operator...

[GasyTek]

Thanks for your quick reply and the workaround.

Riana

[ElectroBuddha]

I'm also having the similar issue, any info on this? Extension method .UseCollation() should probably be able to take additional schema parameter;

[seriouz]

I have the same problem. In my database-server there are databases with a custom schema and in this custom schema there a custom collations. Now when i do a migration the migration executor does not respect the builder.HasDefaultSchema(...) for the COLLATE command: ALTER TABLE tenant_schema."Persons" ALTER COLUMN "PhoneNumber" TYPE text COLLATE "utf8_collate_ci"; it should be ALTER TABLE tenant_schema."Persons" ALTER COLUMN "PhoneNumber" TYPE text COLLATE tenant_schema."utf8_collate_ci";

[georg-jung]

I ran into the same problem and wanted to check if it might make sense to create a PR (probably not, the change seems rather non-trivial?). Is this something that would depend on a change in EF.Relational e.g. here and here or something clearly Npgsql specific?