npgsql says enum is not registered

Question

I'm trying to insert some data with enums via Npgsql. At first, everything worked fine, but I have noted, that some of my Enums don't work. I have mapped all my c# enums to the postgres enums but when trying to use them, some of them give me the following message:

The CLR enum type One_More_Enum must be registered with Npgsql before usage, please refer to the documentation

I think i have mapped my enums according to the documentation. At least the enums Some_Enum and Another_Enum work just fine.

Here is my code:

//Enum declaration
public enum Some_Enum
{
    [PgName("SOME_VALUE")]
    SOME_VALUE,
    [PgName("ANOTHER_VALUE")]
    ANOTHER_VALUE,
    [PgName("ONE_MORE_VALUE")]
    ONE_MORE_VALUE
}

public enum Other_Enum
{
    [PgName("SOME_VALUE")]
    SOME_VALUE,
    [PgName("ANOTHER_VALUE")]
    ANOTHER_VALUE,
    [PgName("ONE_MORE_VALUE")]
    ONE_MORE_VALUE
}

public enum One_More_Enum
{
    [PgName("SOME_VALUE")]
    SOME_VALUE,
    [PgName("ANOTHER_VALUE")]
    ANOTHER_VALUE,
    [PgName("ONE_MORE_VALUE")]
    ONE_MORE_VALUE
}

//Register my enums according to the documentation
NpgsqlConnection.GlobalTypeMapper.MapEnum<Some_Enum>("some_enum");
NpgsqlConnection.GlobalTypeMapper.MapEnum<Other_Enum>("other_enum");
NpgsqlConnection.GlobalTypeMapper.MapEnum<One_More_Enum>("one_more_enum");

using (var connection = new Npgsql.NpgsqlConnection(CONNECTION_STRING))
{
    connection.Open();

    var command = connection.CreateCommand();
    command.CommandText = $"INSERT INTO TABLE_A (NAME, SOME_ENUM) VALUES (@NAME, @SOME_ENUM) RETURNING ID";
    command.Parameters.AddWithValue("NAME", "Foo Bar");
    command.Parameters.AddWithValue("SOME_ENUM", Some_Enum.SOME_VALUE);
    var A_id = command.ExecuteScalar(); //Works fine...

    command.Parameters.Clear();

    command.CommandText = $"INSERT INTO TABLE_B (A_ID, NAME, FOO, BAR, OTHER_ENUM)" +
                        $"VALUES (@A_ID, @NAME, @FOO, @BAR, @OTHER_ENUM) RETURNING ID";
    command.Parameters.AddWithValue("NAME", "Some Name Bla Bla");
    command.Parameters.AddWithValue("A_ID", A_id);
    command.Parameters.AddWithValue("FOO", false);
    command.Parameters.AddWithValue("BAR", false);
    command.Parameters.AddWithValue("OTHER_ENUM", Other_Enum.ANOTHER_VALUE);
    var B_id = command.ExecuteScalar(); //Works fine

    command.Parameters.Clear();

    command.CommandText = $"INSERT INTO TABLE_C (B_ID, NAME, ONE_MORE_ENUM) VALUES (@B_ID, @NAME @ONE_MORE_ENUM) RETURNING ID";
    command.Parameters.AddWithValue("B_ID", B_id);
    command.Parameters.AddWithValue("NAME", "Hey Ho Name");
    command.Parameters.AddWithValue("ONE_MORE_ENUM", One_More_Enum.ONE_MORE_VALUE);
    var D_id = command.ExecuteScalar(); //!!! Error is thrown here...
}

I found this question here on stack, where the problem seemed to be solved by registering the enums not globally, but via connection (But the poster of the question doesn't think this solved his problem (look at his answere)). I have tried it like this:

//Register my enums according to the documentation
NpgsqlConnection.GlobalTypeMapper.MapEnum<Some_Enum>("some_enum");
NpgsqlConnection.GlobalTypeMapper.MapEnum<Other_Enum>("other_enum");
//NpgsqlConnection.GlobalTypeMapper.MapEnum<One_More_Enum>("one_more_enum");

using (var connection = new Npgsql.NpgsqlConnection(CONNECTION_STRING))
{
    connection.Open();
    connection.TypeMapper.MapEnum<One_More_Enum>("one_more_enum");
    connection.ReloadTypes();

    //...
}

But I get the same error message...

---

Here is the declaration of the enums in postgre:

CREATE TYPE SOME_ENUM AS ENUM ('SOME_VALUE', 'ANOTHER_VALUE', 'ONE_MORE_VALUE');
ALTER TYPE SOME_ENUM OWNER TO postgres;
CREATE TYPE ANOTHER_ENUM AS ENUM ('SOME_VALUE', 'ANOTHER_VALUE', 'ONE_MORE_VALUE');
ALTER TYPE ANOTHER_ENUM OWNER TO postgres;
CREATE TYPE ONE_MORE_ENUM AS ENUM ('SOME_VALUE', 'ANOTHER_VALUE', 'ONE_MORE_VALUE');
ALTER TYPE ONE_MORE_ENUM OWNER TO postgres;

---

I have boield everything down that you should be able to test this yourself wihtout a lot of effort. With this setup i get the error described above.

Sql:

CREATE TYPE GRABART_MULTIPLIKATOR AS ENUM ('NICHT_MULTIPLIZIEREN', 'ANZAHL_GRABSTELLEN', 'FLAECHE');
ALTER TYPE GRABART_MULTIPLIKATOR OWNER TO postgres;

CREATE TABLE grab_art (
    id                                          SERIAL                 NOT NULL
        CONSTRAINT grabart_pkey
            PRIMARY KEY,
    bezeichnung                                 TEXT,
    multiplikator                               GRABART_MULTIPLIKATOR
);

ALTER TABLE grab_art
    OWNER TO postgres;

C#:

class Program
{
    const string CONNECTION_STRING = "Server=localhost;Port=5432;Database=my_db;search path=my_demo;User ID=postgres;Password=pw;";

    public enum Grabart_Multiplikator
    {
        [PgName("NICHT_MULTIPLIZIEREN")]
        NICHT_MULTIPLIZIEREN,
        [PgName("ANZAHL_GRABSTELLEN")]
        ANZAHL_GRABSTELLEN,
        [PgName("FLAECHE")]
        FLAECHE
    }

    static void Main(string[] args)
    {
        try
        {
            NpgsqlConnection.GlobalTypeMapper.MapEnum<Grabart_Multiplikator>("grabart_multiplikator");

            using (var connection = new Npgsql.NpgsqlConnection(CONNECTION_STRING))
            {
                connection.Open();

                var command = connection.CreateCommand();
                command.CommandText = $"INSERT INTO GRAB_ART (BEZEICHNUNG, MULTIPLIKATOR) VALUES (@BEZEICHNUNG, @MULTIPLIKATOR) RETURNING ID";
                command.Parameters.AddWithValue("BEZEICHNUNG", "Neuer Rechtsträger");
                command.Parameters.AddWithValue("MULTIPLIKATOR", Grabart_Multiplikator.FLAECHE);
                var grabart_id = command.ExecuteScalar();

                command.Parameters.Clear();


                Console.WriteLine($"Success...");
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
        }
        finally
        {
            Console.WriteLine("Press any key to exit");
            Console.ReadKey();
        }
    }


} 

Any ideas what i am doing wrong?

Answer 1

I also had this problem. Two enums with the same name in different Schemas.

Just set "schema.enum" in your entity configuration:

connection.TypeMapper.MapEnum<One_More_Enum>("schema.one_more_enum");

It works for me!