Mapping issues with schema-scoped enums

[ZekeLu]

According to what I have read, the original issue #554 has been addressed by the pull request #605. But it seems that this issue still exists in the latest preview (3.0.0-preview5).

Here is the model and configuration:

public enum Mood
{
    Happy,
    Sad
}

public class Blog
{
    public int BlogId { get; set; }
    public Mood Mood { get; set; }
}

public class BloggingContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    // map enum without specifying pgName
    static BloggingContext() => NpgsqlConnection.GlobalTypeMapper.MapEnum<Mood>();

    // it works when specified a correct pgName
    // static BloggingContext() => NpgsqlConnection.GlobalTypeMapper.MapEnum<Mood>("my.mood");

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder.UseNpgsql("Host=my_host;Database=my_db;Username=my_user;Password=my_pw");

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // specify a default schema
        modelBuilder.HasDefaultSchema("my");
        modelBuilder.ForNpgsqlHasEnum<Mood>();
    }
}

The script generated by dotnet ef migrations script is:

CREATE TABLE IF NOT EXISTS "__EFMigrationsHistory" (
    "MigrationId" character varying(150) NOT NULL,
    "ProductVersion" character varying(32) NOT NULL,
    CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY ("MigrationId")
);

CREATE SCHEMA IF NOT EXISTS my;

CREATE SCHEMA IF NOT EXISTS my;

CREATE TYPE my.mood AS ENUM ('happy', 'sad');

CREATE TABLE my."Blogs" (
    "BlogId" serial NOT NULL,
    "Mood" mood NOT NULL, -- ERROR: type "mood" does not exist. should use my.mood
    CONSTRAINT "PK_Blogs" PRIMARY KEY ("BlogId")
);

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20190718112346_init', '3.0.0-preview5.19227.1');

The expected script to create the my."Blogs" table is:

CREATE TABLE my."Blogs" (
    "BlogId" serial NOT NULL,
    "Mood" my.mood NOT NULL,  -- mood --> my.mood
    CONSTRAINT "PK_Blogs" PRIMARY KEY ("BlogId")
);

And dotnet ef database update will fail with this message:

Failed executing DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE my."Blogs" (
    "BlogId" serial NOT NULL,
    "Mood" mood NOT NULL,
    CONSTRAINT "PK_Blogs" PRIMARY KEY ("BlogId")
);
Npgsql.PostgresException (0x80004005): 42704: type "mood" does not exist

I have attached the project so that you can examine this issue.

ConsoleApp.zip

[dstockhammer]

The same thing is happening on 2.2.4. I haven't checked earlier versions (since the fix is easy enough, albeit manual), but it seems that the regression is in current stable as well.

[roji]

@austindrenski any chance you can look at this, as you handled the original issue and PR?

[ZekeLu]

Just tested with Preview 8, the issue is not addressed yet.

[roji]

Confirmed that this is still an issue in 3.1.

[roji]

(Finally) took a deep look at this... tl;dr the full enum name (including schema) must be specified in the call to NpgsqlConnection.GlobalTypeMapper.MapEnum, so that it corresponds to the actual enum created by EF Core. This must be done whenever the enum is in a custom schema (i.e. not public) - whether defined via HasDefaultSchema or explicitly in the HasPostgresEnum call.

For more detail... enum support involves two layers working together - the Npgsql ADO driver and the EF Core provider. NpgsqlConnection.GlobalTypeMapper.MapEnum creates the mapping at the ADO level, and the EF Core provider pulls in those mappings for use in its own internal type mapper. EF Core's default schema settings affect what happens in migrations (this is why the CREATE TYPE AS ENUM is correct), but does not affect the type name as returned by the type mapping source - only the ADO-level mapping can do that at present. This, by the way, affects not only enum types appearing in migrations (e.g. in CREATE TABLE), but also in queries (e.g. if you try to cast to an enum). Passing the fully-qualified name to MapEnum ensures that the ADO layer has the correct name, and that flows into the EF Core provider's type mapping source, for integration in all SQL generated by the provider.

This is a somewhat unfortunate/complicated situation, though it does work - #1026 tracks improving general enum mapping support. I've added a note in the EF enum doc page to make this clear.