How to handle custom enum value with Postgres enum

[Kampfmoehre]

We use NPGSQL in an ASP.NET Core application that shares a database with a NodeJS application. To be able to have the same types, enums are defined in a special model class lib and defined like this:

public enum ExampleEnum
{
  [Description("EXAMPLE_VALUE_1")]
  ExampleValue1,

  [Description("EXAMPLE_VALUE_2")]
  ExampleValue2,
}

Then there is a TypeScript enum generated from the C# code that looks like this

export enum ExampleEnum {
  ExampleValue1 = "EXAMPLE_VALUE_1",
  ExampleValue2 = "EXAMPLE_VALUE_2"
}

There is a custom converter involved that converts the enum to the value defined in the Description attribute and this value is stored in the database. This way both applications can use the "same" enum. Until now this worked on the ASP.NET Core side by adding a ValueConverter

ValueConverter<ExampleEnum, string> converter = new(
  v => v.GetEnumDescription(),
  v => GetEnumFromDescriptionString<ExampleEnum>(v));

    entity.Property(e => e.Example).HasConversion(converter);

The reason this worked so far was, that a normal text column was used in Postgres. Now we tested to use a Postgres Enum and have successfully set it up for reading. But writing the value in .NET fails with a message like

column "example" is of type entity_example_enum but expression is of type text

I have tried to add

modelBuilder.HasPostgresEnum<ExampleEnum>(name: "entity_example_enum");

dataSourceBuilder.MapEnum<ExampleEnum>("entity_example_enum");

but to no avail. Is there any way I can make this work with a Postgres Enum?

[hillpatel]

To use a PostgreSQL Enum type with Npgsql in an ASP.NET Core application, you need to ensure that the Npgsql provider is aware of the PostgreSQL Enum type and can properly map it between C# and the database.

Here's how you can use a PostgreSQL Enum with Npgsql in an ASP.NET Core application:

1. Define the PostgreSQL Enum type in the database: Ensure that the PostgreSQL Enum type is created in your database. You can do this using a migration script or by running a SQL script. For example:

CREATE TYPE entity_example_enum AS ENUM ( 'EXAMPLE_VALUE_1', 'EXAMPLE_VALUE_2' );

2. Map the PostgreSQL Enum type in your DbContext: In your DbContext, use the HasConversion method to specify the conversion between your C# enum and the PostgreSQL Enum type. Additionally, use HasColumnType to specify the PostgreSQL Enum type for the column.

using Microsoft.EntityFrameworkCore;
using Npgsql.EntityFrameworkCore.PostgreSQL.Metadata;

public class YourDbContext : DbContext
{
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasPostgresEnum<ExampleEnum>(name: "entity_example_enum");

        modelBuilder.Entity<YourEntity>()
            .Property(e => e.Example)
            .HasConversion<string>()
            .HasColumnType("entity_example_enum");
    }
}

3. Ensure Npgsql is configured to use Enum types: Ensure that Npgsql is configured to use Enum types. You can do this in your Startup.cs or wherever you configure your database connection. For example:

services.AddDbContext<YourDbContext>(options =>
    options.UseNpgsql(Configuration.GetConnectionString("YourConnectionString"),
        npgsqlOptions => npgsqlOptions.UseNetTopologySuite())
);

Make sure that the UseNetTopologySuite is used if you have any spatial types.

4. Ensure the PostgreSQL Enum type is used in migrations: When creating or updating the database, ensure that the PostgreSQL Enum type is used for the enum column. This may involve running migrations.

dotnet ef migrations add YourMigrationName
dotnet ef database update

5. Handle PostgreSQL Enum types in your queries: Ensure that your queries handle PostgreSQL Enum types correctly. When querying for entities with an Enum property, Npgsql should be able to map the Enum values between C# and PostgreSQL.

[teberl]

I came across this issue but the provided solution was not working.

At the end, the setup is even cleaner as mentioned in the comments above.

The docs are awesome and the examples, as simple as they look, worked for me perfectly. Npsql Enum Mapping

I would not recommend to do point 1, and create the enum types manually. The EF migrations should take care for you of this, at least they did for me. Point 2, is not required, check the Npsql docs. There was no reason for me to use Entity.Property. You can ignore point 3, since this is only related to Spatial data pgsqlOptions.UseNetTopologySuite()

I was also able to Map Enums correctly from jsonb fields even when i store the Enum value as strings inside the json object to have it more redable.

Npgsql.EntityFrameworkCore.PostgreSQL 8.0.2

Overall i think there are many different use cases out there, how and where and why to use this feature. But i think this is actually not an issue this package and just a topic of how to configure it correctly for you specific use case.

[roji]

Closing as this is all covered in the docs; note also that EF 9.0 brings some improvements to how enum mapping is configure (see release notes). If you're still having difficulties, feel free to post back here and I'll do my best to help.