How to read an enum array from Postgres into a list of enums in C#?

Question

net core 6.0 application using Npgsql v6.0.4.

Table:

CREATE TABLE kontrolle.negativtext (
    id serial NOT NULL,
    check_doc "_negative_doc_check" NULL DEFAULT 
    CONSTRAINT negativtext_pkey PRIMARY KEY (id)
);

Column check_doc is an array of the custom enum type _negative_doc_check.

Enum:

select enum_range(null::negative_doc_check)

returns {CONTENT_HTML_DIRTY,CONTENT_HTML_CLEAN,CONTENT_TXT_DIRTY,CONTENT_TXT_CLEAN}

Code:

using System;
using System.Collections.Generic;
using Npgsql;

var constring = "SERVER=192.168.2.121;DATABASE=myuser;USER ID=mydb;PASSWORD=mypasswd;";
NpgsqlConnection.GlobalTypeMapper.MapEnum<negative_doc_check>("negative_doc_check");

await using var conn = new NpgsqlConnection(constring);
await conn.OpenAsync();

await using (var cmd = new NpgsqlCommand("SELECT check_doc from kontrolle.negativtext where id = 643", conn))
await using (var reader = await cmd.ExecuteReaderAsync()) {
    while (await reader.ReadAsync()) {
        var enumValue = reader.GetFieldValue<List<negative_doc_check>>(0);
        Console.WriteLine(enumValue);
    }
}

public enum negative_doc_check {
    CONTENT_HTML_DIRTY = 0,
    CONTENT_HTML_CLEAN = 1,
    CONTENT_TXT_DIRTY = 2,
    CONTENT_TXT_CLEAN = 4
}

public class Negativtext {
    public int Id { get; set; }
    public List<negative_doc_check> CheckDoc { get; set; }

}

Exception:

System.InvalidCastException: "Received enum value 'CONTENT_TXT_DIRTY' from database which wasn't found on enum negative_doc_check"

The enum values are identical in both my C# code and the Postgres type definition. Am I missing something or is it just not possible to read a list of enums?

Answer 1

By default, when mapping .NET to PG enums, Npgsql assumes .NET enums are named like standard .NET types (Pascal Case, i.e. NativeDocCheck and not negative_doc_check), whereas your PG enums are assumed to be snake case (i.e. negative_doc_check). It's recommended to change your .NET enum to conform to that:

public enum NegativeDocCheck {
    ContentHtmlDirty = 0,
    ContentHtmlClean = 1,
    ContentTxtDirty = 2,
    ContentTxtClean = 4
}

Otherwise, to keep your non-standard naming, you can tell Npgsql to map the names as is when mapping the enum:

NpgsqlConnection.GlobalTypeMapper.MapEnum<negative_doc_check>("negative_doc_check", new NpgsqlNullNameTranslator());