Efcore.pg: Enum with a schema, the schema isn't included in CREATE TABLE migration script

[akhansari]

Technical details

Npgsql.EntityFrameworkCore.PostgreSQL 2.2.4
Microsoft.EntityFrameworkCore 2.2.6
Postgresql 12.1
netcoreapp2.2 + fsharp

Steps to reproduce

DbContext configuration :

    type Bai (options: DbContextOptions<Bai>) =
        inherit DbContext(options)

        static do
            NpgsqlConnection.GlobalTypeMapper.MapEnum<AccountState>() |> ignore

        let configurePostgres (modelBuilder: ModelBuilder) =
            modelBuilder
                .ForNpgsqlUseIdentityByDefaultColumns()
                .WithSnakeCaseNamingConvention()

        let configureEnums (modelBuilder: ModelBuilder) =
            modelBuilder.ForNpgsqlHasEnum<AccountState>(schema = DefaultSchema)

        let configureEntities (modelBuilder: ModelBuilder) =
            modelBuilder
                .HasDefaultSchema(DefaultSchema)
                .ApplyConfigurationsFromAssembly(typeof<Bai>.Assembly)

        override __.OnModelCreating modelBuilder =
            modelBuilder
            |> configureEnums
            |> configureEntities
            |> configurePostgres
            |> ignore

Trying to update the database :
dotnet ef database update ...

Generated scripts :

...
CREATE TYPE sch_bai.account_state AS ENUM ('unknown', 'ok', 'requested');
...
CREATE TABLE sch_bai.accounts (
    id uuid NOT NULL,
    ...
    state account_state NOT NULL,
    ...
    CONSTRAINT pk_accounts PRIMARY KEY (id)
);

The issue

Exception : Npgsql.PostgresException (0x80004005): 42704: type "account_state" does not exist

The schema is missing for
state account_state NOT NULL
and must rather be
state sch_bai.account_state NOT NULL.

[akhansari]

Is this bug fixed in the 3.0.1 version?

[akhansari]

It doesn't work either with 3.0.1.

I did the migration by generating the script and then fixing it.\
But the GlobalTypeMapper.MapEnum doesn't take the default schema to account either as it's a static method executed always before HasDefaultSchema and HasPostgresEnum.\
So there is also an exception when doing select queries.

I believe the current architecture isn't suited with a multi schema database!\
Also considering pg best practices, it's better to not use the public schema.

[akhansari]

Some updates, It wasn't really a bug but lack of documentation and bad design.

Steps to have an explicite schema:

• set MigrationsHistoryTable with schema = mySchema
• set HasDefaultSchema with schema = mySchema
• set HasPostgresEnum with schema = mySchema
• set GlobalTypeMapper.MapEnum with pgName = mySchema + "." + nameof myEnum

[roji]

This does look like a bug. The creation of the enum type itself is fine (CREATE TYPE has the schema prefix), but inside the CREATE TABLE it's missing.

Also considering pg best practices, it's better to not use the public schema.

I'm not aware of this, can you point to any resources saying this?

[roji]

Looking at this again, you're right. The type names integrated in CREATE TABLE (and other migrations) come from the type mapper, and the type mapper gets enum store types from the ADO.NET layer.

In fact, I've already investigated this before (#930), and hope to look at changing the way we handle enums and other similar types (#1026).