Bug: The ENUM type of Postgre (DB) is not being handled properly in RepoDb.PostgreSql.

[mikependon]

The codes below must be working, but it is now.

using Npgsql;
using PostgreSqlEnumTests.Enumerations;
using RepoDb;
using RepoDb.Attributes;
using System;

namespace PostgreSqlEnumTests
{
    class Program
    {
        [Map("person")]
        private class Person
        {
            public long id { get; set; }
            public string name { get; set; }
            public Hands usedhands { get; set; }
        }

        private const string m_connectionString = "Server=127.0.0.1;Port=5432;Database=RepoDb;User Id=postgres;Password=Password123;";

        static void Main(string[] args)
        {
            PostgreSqlBootstrap.Initialize();
            CreateEnumType();
            DropTable();
            CreateTable();
            InsertPerson();
            QueryPeople();
            Console.WriteLine("Press any key to exit.");
            Console.ReadLine();
        }

        static void CreateEnumType()
        {
            return; // Already created
            using (var connection = new NpgsqlConnection(m_connectionString))
            {
                connection.ExecuteNonQuery("CREATE TYPE Hands AS ENUM ('Left', 'Right');");
            }
        }

        static void DropTable()
        {
            return; // Already dropped
            using (var connection = new NpgsqlConnection(m_connectionString))
            {
                connection.ExecuteNonQuery(@"DROP TABLE public.Person;");
            }
        }

        static void CreateTable()
        {
            using (var connection = new NpgsqlConnection(m_connectionString))
            {
                connection.ExecuteNonQuery(@"CREATE TABLE IF NOT EXISTS public.Person
                    (
                        Id bigint GENERATED ALWAYS AS IDENTITY,
                        Name Text,
                        UsedHands Hands
                    )

                    TABLESPACE pg_default;

                    ALTER TABLE public.Person
                        OWNER to postgres;");
            }
        }

        static void InsertPerson()
        {
            using (var connection = new NpgsqlConnection(m_connectionString))
            {
                var id = connection.Insert(new Person
                {
                    name = Guid.NewGuid().ToString(),
                    usedhands = Hands.Left
                });
            }
        }

        static void QueryPeople()
        {
            using (var connection = new NpgsqlConnection(m_connectionString))
            {
                var people = connection.QueryAll<Person>();
                foreach (var person in people)
                {
                    Console.WriteLine($"{person.id}. {person.name} ({person.usedhands})");
                }
            }
        }
    }
}

[mikependon]

This is related to what you reported in #390. I myself encountered this and trying to understand more why it is passing on my Integration Tests and failing on this small project. @ffandreassoroko

[mikependon]

@ffandreassoroko - I now have fixed this on my DEV machine by setting the NpgsqlDbType property to Unknown.

[Map("person")]
private class Person
{
	public long id { get; set; }
	public string name { get; set; }
	[NpgsqlTypeMapAttribute(NpgsqlDbType.Unknown)]
	public Hands usedhands { get; set; }
}

That should also fix yours.

[andreas-soroko]

yes, that works!

[mikependon]

Great. I will close this one now. Thanks!

[orthoxerox]

@mikependon This still doesn't work if the enum is used in the where clause:

var people = connection.Query<Person>(where: p => p.DominantHand == Hand.Right);
//I've been renaming stuff to understand why my main program failed when this snipped didn't

results in:

Unhandled exception. System.InvalidCastException: Can't write CLR type Sandbox.Program+Hand with handler type TextHandler
   at lambda_method5(Closure , NpgsqlTypeHandler , Object , NpgsqlLengthCache& , NpgsqlParameter )
   at Npgsql.TypeHandling.NpgsqlTypeHandler`1.ValidateObjectAndGetLength(Object value, NpgsqlLengthCache& lengthCache, NpgsqlParameter parameter)
   at Npgsql.NpgsqlParameter.ValidateAndGetLength()
   at Npgsql.NpgsqlCommand.ValidateParameters(ConnectorTypeMapper typeMapper)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at RepoDb.DbConnectionExtension.ExecuteQueryInternalForType[TResult](IDbConnection connection, String commandText, Object param, Nullable`1 commandType, String cacheKey, Nullable`1 cacheItemExpiration, Nullable`1 commandTimeout, IDbTransaction transaction, ICache cache, String tableName, Boolean skipCommandArrayParametersCheck)
   at RepoDb.DbConnectionExtension.ExecuteQueryInternal[TResult](IDbConnection connection, String commandText, Object param, Nullable`1 commandType, String cacheKey, Nullable`1 cacheItemExpiration, Nullable`1 commandTimeout, IDbTransaction transaction, ICache cache, String tableName, Boolean skipCommandArrayParametersCheck)
   at RepoDb.DbConnectionExtension.QueryInternalBase[TEntity](IDbConnection connection, String tableName, QueryGroup where, IEnumerable`1 fields, IEnumerable`1 orderBy, Nullable`1 top, String hints, String cacheKey, Nullable`1 cacheItemExpiration, Nullable`1 commandTimeout, IDbTransaction transaction, ICache cache, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.QueryInternal[TEntity](IDbConnection connection, String tableName, QueryGroup where, IEnumerable`1 fields, IEnumerable`1 orderBy, Nullable`1 top, String hints, String cacheKey, Nullable`1 cacheItemExpiration, Nullable`1 commandTimeout, IDbTransaction transaction, ICache cache, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.Query[TEntity](IDbConnection connection, Expression`1 where, IEnumerable`1 fields, IEnumerable`1 orderBy, Nullable`1 top, String hints, String cacheKey, Nullable`1 cacheItemExpiration, Nullable`1 commandTimeout, IDbTransaction transaction, ICache cache, ITrace trace, IStatementBuilder statementBuilder)
   at Sandbox.Program.QueryPeople() in C:\...\Sandbox\Program.cs:line 99
   at Sandbox.Program.Main(String[] args) in C:\...\Sandbox\Program.cs:line 41

[mikependon]

@orthoxerox - what is the data type of DominantHand? If it is TEXT or INTEGER, then it should be handled by RepoDB itself. Here is the note for that.

Note: If the data type is different, then this issue is prevalent to Postgre SQL in which I reported directly to the Npgsql team (Shay Rojansky).

[orthoxerox]

It's ENUM:

CREATE TYPE hand AS ENUM ('Left', 'Right');

CREATE TABLE IF NOT EXISTS person
                    (
                        person_id bigint GENERATED ALWAYS AS IDENTITY,
                        name text,
                        dominant_hand hand
                    );

[mikependon]

Had you tried this on your model [NpgsqlTypeMapAttribute(NpgsqlDbType.Unknown)]? If yes, then I will do the investigation why is this happening on the expression trees.

[orthoxerox]

Yes, I copied the model from your example in this issue:

        [Map("person")]
        private class Person
        {
            [Map("person_id")]
            public long ID { get; set; }
            [Map("name")]
            public string Name { get; set; }
            [NpgsqlTypeMapAttribute(NpgsqlDbType.Unknown)][Map("dominant_hand")]
            public Hand DominantHand { get; set; }
        }