UseCollation gives error invalid LC_COLLATE locale name

[pantonis]

I m trying to create a new collation that is case insensitive. and when I run Update-Database it gives the following error

Failed executing DbCommand (16ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE DATABASE "BlogDb"
LC_COLLATE "en_US.utf8_ci";
Npgsql.PostgresException (0x80004005): 42809: invalid LC_COLLATE locale name: "en_US.utf8_ci"

I have created a sample project that demonstrates this. Repo here

[roji]

@pantonis EF just passes the values you specify to CREATE COLLATION, CREATE DATABASE - it's your responsibility to make sure they're correct and make sense.

Specifically in your code sample, it seems like you're trying to create a custom collation in a database, but also use that custom collation as the database collation - that's a chicken-and-egg problem (the database must first exist before you can create a collation inside of it). You may be able to first create the database with the default collation, and have a separate migration for creating the custom collation, and alter the databaes to use that; I'm no longer sure what PostgreSQL supports here exactly, and what it doesn't.

[roji]

Another note is that IIRC non-deterministic collations may not work well as the default database collation in PostgreSQL, because they affect internal PG catalog comparisons, and other aspects (this may have changed since I last checked).

I'd consider simply using EF's conventions to assign your collation to all string columns; this is covered in the docs.

[pantonis]

So we need a migration as soon as we create the dbcontext without any tables and just with only

 modelBuilder.HasCollation

Then we start creating the dbContext with the tables and create a new migation?

[roji]

You can give it a try - let me know if that works.