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}

Athanasios Kataras · Accepted Answer · 2020-08-26 06:03:03Z

6

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

answered Aug 26, 2020 at 6:03

Athanasios Kataras

26.7k5 gold badges40 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

rosepalette Over a year ago

Hi @Athansios, I tried adding the static constructor or overriding the OnConfiguring but it keeps fails and throw an exception that

{"The CLR enum type SourceDestinationEnum must be registered with Npgsql before usage, please refer to the documentation."}

Athanasios Kataras Over a year ago

Try removing the HasConversion to which converts to string.

rosepalette Over a year ago

I tried removing the HasConversion, but it still prompted me

{"The CLR enum type SourceDestinationEnum must be registered with Npgsql before usage, please refer to the documentation."}

I added some snippet to share with you to see if you have any idea where I had made mistake. gist.github.com/weihan1394/fdb2990c0b4ad5680d4c3f11a8e9c8d4

Shay Rojansky Over a year ago

You definitely seem to be mixing several modes. If you want your C# enums to be mapped to PostgreSQL enums, you need to follow npgsql.org/efcore/mapping/enum.html?tabs=tabid-1 (see the full sample there which works). If you want to map your C# enums to PostgreSQL text, you can use a value converter (in which case remove all the HasPostgresEnum directives, etc.)

Eddie Over a year ago

I happened to use NpgsqlConnection.GlobalTypeMapper.MapEnum but it is marked obsolete on Npgsql 7. What should be my approach?

Colin · Accepted Answer · 2020-08-26 05:17:42Z

0

If the enum just needs to be converted to a string, something like this should work:

var converter = new EnumToStringConverter<HttpActionEnum>();

modelBuilder
    .Entity<Response>()
    .Property(e => e.RespondedData)
    .HasConversion(converter);

answered Aug 26, 2020 at 5:17

Colin

4,16524 silver badges44 bronze badges

3 Comments

rosepalette Over a year ago

Hi @Colin, thank you for your help! But after i converted it to a string, it prompted me similar problem MessageText: column "destination" is of type source_dest_enum but expression is of type text

Nowshad Hossain Rahat Dec 21, 2024 at 18:08

@rosepalette I'm also facing this same issue, even ChatGPT can't give any solution yet

Mikhail Galay Feb 3 at 19:36

I had this problem on version 8. On version 9 everything worked as in the documentation.

Adrian Mole · Accepted Answer · 2023-10-11 15:53:53Z

0

I was facing this issue in a WebApi application. I had to move the below line to Startup.cs ConfigureServices() method.

NpgsqlConnection.GlobalTypeMapper.MapEnum<Mood>();

edited Oct 11, 2023 at 15:53

Adrian Mole

52.1k193 gold badges61 silver badges101 bronze badges

answered Nov 14, 2022 at 11:11

RT13

11 bronze badge

Comments

Samuerik · Accepted Answer · 2024-11-19 17:27:37Z

I looked for the solution for days, but I managed to find it.

Depending on the case, it will be necessary to create a CAST in the database.

CREATE CAST (your_postgresql_enum AS text) WITH INOUT AS IMPLICIT;

Read about the postgresql CAST here: https://www.postgresql.org/docs/current/sql-createcast.html

I'm using version 9 of npgsql available from .Net 8 and it was not necessary to create the CAST in the database.

Read more about the version 9 of npgsql here: https://www.npgsql.org/efcore/release-notes/9.0.html

Add enum to json conversion settings in your Program.cs:

builder.Services.AddControllers()
     .AddJsonOptions(options =>
         {
            options.JsonSerializerOptions.Converters.Add(new JsonStringEnumConverter());
         });

Also in your Program.cs map the enum like this:

 builder.Services.AddDbContextPool<DbContext>(opt =>
    opt.UseNpgsql(
        "your_connection_string",
        o => o
            .MapEnum<YourC#Enum>("your_postgresql_enum")
        )
    );

And finally in your DbContext put the conversion:

entity.Property(e => e.YourColumn)
   .HasColumnType("your_type")
   .HasConversion(new EnumToStringConverter<YourC#Enum>());

Collectives™ on Stack Overflow

C# enum to postgres enum

4 Answers 4

5 Comments

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related