Enum Array insert/update

[darko1979]

I have a use case where enum array column would be perfect. Select works without problems but I'm unable to perform insert or update.

Here is a sample schema:

DROP TABLE IF EXISTS "item" CASCADE;
DROP TYPE IF EXISTS "item_type_enum" CASCADE;

CREATE TYPE "item_type_enum" AS ENUM (
  'type1',
  'type2',
  'type3'
);


CREATE TABLE IF NOT EXISTS "item"
(
  "id" INT NOT NULL,
  "type" "item_type_enum",
  "types" "item_type_enum"[],
  CONSTRAINT pk_item PRIMARY KEY (id)
);

And here is sample code:

using LinqToDB;
using LinqToDB.Data;
using LinqToDB.DataProvider.PostgreSQL;
using LinqToDB.Mapping;
using Npgsql;
using NpgsqlTypes;
using System.Text.Json;
using System.Text.Json.Serialization;

namespace test
{

    [PgName("item_type_enum")]
    public enum ItemTypeEnum
    {
        [PgName("type1")]
        [MapValue("type1")]
        Type1,
        [PgName("type2")]
        [MapValue("type2")]
        Type2,
        [PgName("type3")]
        [MapValue("type3")]
        Type3,
    }

    [Table(Schema = "public", Name = "item")]
    public partial class Item
    {
        [Column("id", DataType = LinqToDB.DataType.Int32, Precision = 32, Scale = 0), PrimaryKey, NotNull] public int Id { get; set; } // integer
        [Column("type", DataType = LinqToDB.DataType.Enum), Nullable] public ItemTypeEnum? Type { get; set; } // item_type_enum
        [Column("types", DataType = LinqToDB.DataType.Enum), Nullable] public ItemTypeEnum[] Types { get; set; } // USER-DEFINED
    }

    class Program
    {
        static DataOptions GetOptions(string connectionString)
        {
            var builder = new NpgsqlDataSourceBuilder(connectionString);
            builder.MapEnum<ItemTypeEnum>();

            var mapping = new MappingSchema();

            var dataProvider = PostgreSQLTools.GetDataProvider(PostgreSQLVersion.v95);
            var dataSource = builder.Build();
            return new DataOptions()
                .UseConnectionFactory(dataProvider, _ => dataSource.CreateConnection())
                .UseMappingSchema(mapping);
        }

        static async Task Main(string[] args)
        {
            var options = new JsonSerializerOptions { Converters = { new JsonStringEnumConverter() }, WriteIndented = true };
            var connectionString = "Server=localhost;Port=5432;Database=test;User Id=postgres;Password=postgres;";
            DataConnection.TurnTraceSwitchOn(System.Diagnostics.TraceLevel.Info);
            DataConnection.WriteTraceLine = (s1, s2, level) => Console.WriteLine($"{level}: {s1}, {s2}");
            var db = new DataConnection(GetOptions(connectionString));

            var table = db.GetTable<Item>();
            await table.DeleteAsync();

            {
                await table
                    .Value(x => x.Id, 1)
                    .Value(x => x.Type, ItemTypeEnum.Type1)
                    .InsertAsync();

                await db.ExecuteAsync("""
                    insert into "item"("id", "types") values (2, '{type1,type2}')
                    """);
            }
            {
                var result = await table.ToArrayAsync();
                Console.WriteLine(JsonSerializer.Serialize(result, options));
            }
            {
                await table
                    .Value(x => x.Id, 3)
                    .Value(x => x.Types, [ItemTypeEnum.Type1, ItemTypeEnum.Type2])
                    .InsertAsync();
            }

            Console.WriteLine("Done!");
            Console.ReadKey();
        }
    }
}

Insert/update/select with single enum type columns (not enum array) works fine.
Trying to insert value into enum array columns:

                await table
                    .Value(x => x.Id, 3)
                    .Value(x => x.Types, [ItemTypeEnum.Type1, ItemTypeEnum.Type2])
                    .InsertAsync();

throws following error:

Info: BeforeExecute
--  PostgreSQL.9.5 PostgreSQL (asynchronously)
DECLARE @Id Integer -- Int32
SET     @Id = 3
DECLARE @Types  -- Object
SET     @Types = {'type1','type2'}

INSERT INTO "public".item
(
        id,
        types
)
VALUES
(
        :Id,
        :Types
)
, DataConnection
Error: Error
Exception: System.NotSupportedException
Message  : The CLR type System.Object isn't natively supported by Npgsql or your PostgreSQL. To use it with a PostgreSQL composite you need to specify DataTypeName or to map it, please refer to the documentation.

If I add following mapping:

            mapping.SetConverter<ItemTypeEnum[], DataParameter>(x => new DataParameter()
            {
                IsArray = true,
                DbType = "item_type_enum[]",
                DataType = DataType.Enum,
                Value = "{" + string.Join(',', x.Select(x => x.ToString().ToLower())) + "}",
            });

then I get following error:

Info: BeforeExecute
--  PostgreSQL.9.5 PostgreSQL (asynchronously)
DECLARE @Id Integer -- Int32
SET     @Id = 3
DECLARE @Types Text(13) -- String
SET     @Types = '{type1,type2}'

INSERT INTO "public".item
(
        id,
        types
)
VALUES
(
        :Id,
        :Types
)
, DataConnection
Error: Error
Exception: Npgsql.PostgresException
Message  : 42804: column "types" is of type item_type_enum[] but expression is of type text

Tried using SetValueToSqlConverter but it's never triggered:

            mapping.SetValueToSqlConverter(typeof(ItemTypeEnum[]), (sb, _, _, v) => {
                sb.Append("{" + string.Join(',', (ItemTypeEnum[])v).Select(x => x.ToString().ToLower()) + "}");
            });

Any suggestions to make this work?

Environment details

Linq To DB version: 5.4.1
Database (with version): PostgreSQL 16
Operating system: Windows 10
.NET Version: 8.0