Npgsql enum mapping

Question

I'm using Dapper 2.0.123, Dapper.Contrib 2.0.78 and Npgsql 6.0.4 in ASP.Net API (.Net 6) project. Table in Postgres database has column status defined as campaigns_status_enum. Class and its enum looks like this:

[Table("campaigns")]
public class Campaign
{
    [Key]
    public int id { get; set; }
    // ommited, irrelevant to this question

    //[NpgsqlTypes.PgName("campaigns_status_enum")]
    public ECampaignStatus status { get; set; }

    public enum ECampaignStatus
    {
        [NpgsqlTypes.PgName("active")]
        active,
        [NpgsqlTypes.PgName("inactive")]
        inactive,
        [NpgsqlTypes.PgName("draft")]
        draft
    }
}

Enum in database is defined like this:

CREATE TYPE public.campaigns_status_enum AS ENUM
('active', 'inactive', 'draft');

In other questions here and in Npgsql documentation I've read that I need to use NpgsqlConnection.GlobalTypeMapper and according to that, in my Startup.cs I've set the mapping like this:

NpgsqlConnection.GlobalTypeMapper.MapEnum<Common.Campaigns.Campaign.ECampaignStatus>("campaigns_status_enum");

inside ConfigureServices, before injecting DatabaseService which handles connection etc. I've also set

IDbConnection db = new NpgsqlConnection(ConnectionString);
if (db.State != ConnectionState.Open)
{
        db.Open();
        ((NpgsqlConnection)db).ReloadTypes();
}

inside this DatabaseService but apparently it has no effect. When I try to INSERT or UPDATE that table using Dapper.Contrib functions UpdateAsync, InsertAsync, I get following exception:

{"42804: column \"status\" is of type campaigns_status_enum but expression is of type integer\r\n\r\nPOSITION: 172"}

What I'm doing wrong, why this .Net enum isn't mapped to Postgres enum?

score 0 · Answer 1 · answered Jun 12 '22 at 11:51

0

I don't think it's worth it. i would keep it simple

without postgres enums any additional mappings c# enum would be mapped to integer. your entity has enum property and it maps to simple number in database

answered Jun 12 '22 at 11:51

h37l3x

84
2

Unfortunately, we have to use it and we cannot change database fields. – nighthawk Jun 12 '22 at 13:48
i used postgres enums with EF and it worked just like you configured it, but...maybe Dapper.Contrib just does not support enums and converts them into intergers and you get such exception. for example, another simmilar question https://stackoverflow.com/questions/39880743/how-to-map-c-sharp-enum-to-postgresql-enum-using-dapper-fastcrud – h37l3x Jun 12 '22 at 15:10

Npgsql enum mapping

1 Answers1