C# enum to postgres enum

Question

I am currently using postgres enum

CREATE TYPE http_action_enum AS ENUM ('GET', 'HEAD', 'POST', 'PUT', 'DELETE', 'CONNECT', 'OPTIONS', 'TRACE', 'PATCH');
CREATE TABLE IF NOT EXISTS response
(
    id                          UUID                PRIMARY KEY,
    http_action                 http_action_enum    NOT NULL
);

But when I using the ef framework to insert to postgres database I keep hit the below error:

Exception data:
Severity: ERROR
SqlState: 42804
MessageText: column "destination" is of type source_dest_enum but expression is of type integer
Hint: You will need to rewrite or cast the expression.

When I check the response data type it is actually the correct enum and not integer.

Repository.cs

public async Task<Response> InsertRecord(Response response, CancellationToken cancellationToken)
{
      await dBContext.response.AddAsync(response, cancellationToken).ConfigureAwait(true);
      await dBContext.SaveChangesAsync(cancellationToken).ConfigureAwait(true);

      return response;
}

DBContext.cs

      protected override void OnModelCreating(ModelBuilder modelBuilder)
      {
            modelBuilder.HasDefaultSchema("public");
            modelBuilder.HasPostgresEnum<SourceDestinationEnum>();
            modelBuilder.HasPostgresEnum<HttpActionEnum>();

            modelBuilder.Entity<Response>().Property(d => d.RespondedData).HasColumnType("json");

HttpActionEnum.cs

[JsonConverter(typeof(StringEnumConverter))]
    public enum HttpActionEnum
    {
        GET,
        POST,
    }

Does anyone come across mapping c# enum to postgres enum and could advice?

I tried converting to an enum but it's failing with an error that say the column is of type enum but expression is of type text.

https://learn.microsoft.com/en-us/ef/core/modeling/value-conversions

DBContext.cs (updated)

      protected override void OnModelCreating(ModelBuilder modelBuilder)
      {
            modelBuilder.HasDefaultSchema("public");
            modelBuilder.HasPostgresEnum<SourceDestinationEnum>();
            modelBuilder.HasPostgresEnum<HttpActionEnum>();

           modelBuilder.Entity<Response>(entity =>
            {
                entity.Property(e => e.RespondedData).HasColumnType("json");
                entity.Property(e => e.Destination).HasConversion(
                    v => v.ToString(),
                    v => (SourceDestinationEnum)System.Enum.Parse(typeof(SourceDestinationEnum), v));
            });

Error

+       InnerException  {"42804: column \"destination\" is of type source_dest_enum but expression is of type text"}    System.Exception {Npgsql.PostgresException}

Answer 1

Check the entity framework article about postgres enums here: https://www.npgsql.org/efcore/mapping/enum.html?tabs=tabid-1

Even if your database enum is created, Npgsql has to know about it, and especially about your CLR enum type that should be mapped to it. This is done by adding the following code, before any EF Core operations take place. An appropriate place for this is in the static constructor on your DbContext class:

static MyDbContext()
    => NpgsqlConnection.GlobalTypeMapper.MapEnum<Mood>();

For non EF situations check information here: https://www.npgsql.org/doc/types/enums_and_composites.html