Cannot map PostgreSQL text[] type to List<string>

[madmox]

I have a PostgreSQL table named foo with a bar column of type text[]. I try to execute the following code:

public class Foo
{
    public List<string> Bar { get; set; }
}

public async Task<Foo> GetById(Guid id)
{
    var param = new { Id = id };
    string sql = "SELECT bar AS Bar FROM foo WHERE id = @Id";
    return await this._connection.QuerySingleOrDefaultAsync<Foo>(sql, param);
}

The QuerySingleOrDefaultAsync line fails with the following exception:

System.Data.DataException : Error parsing column 2 (bar=System.String[] - Object)
----> System.InvalidCastException : Unable to cast object of type 'System.String[]' to type 'System.Collections.Generic.List`1[System.String]'.

The code works if I change the Foo.Bar property to string[], but I find the behavior inconsistent because Npgsql is supposed to support both regular .NET arrays and List<> types when mapping to/from a PostgreSQL array. Furthermore, performing an INSERT with a List<> parameter does work (supposedly because Dapper mapping doesn't intervene in this case).

[madmox]

As a workaround, I am using this type handler:

public class StringListHandler : SqlMapper.TypeHandler<List<string>>
{
    public override List<string> Parse(object value)
    {
        string[] typedValue = (string[])value; // looks like Dapper did not indicate the property type to Npgsql, so it defaults to string[] (default CLR type for text[] PostgreSQL type)
        return typedValue?.ToList();
    }

    public override void SetValue(IDbDataParameter parameter, List<string> value)
    {
        parameter.Value = value; // no need to convert to string[] in this direction
    }
}

[Arcturuss]

This is still an issue in Dapper 2.0.123

[will-e-yams]

Neitherstring[] nor List<string> in a record (class) work for me OOTB.

v2.1.66

Here are the handlers I used to support this:

    /// <summary>
    /// Generic array handler to support mapping of SQL array types to Arrays in Dapper
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public class GenericArrayHandler<T> : SqlMapper.TypeHandler<T[]>
    {
        public override void SetValue(IDbDataParameter parameter, T[]? value)
        {
            parameter.Value = value;
        }

        public override T[] Parse(object value) => (T[])value;
    }

    /// <summary>
    /// Generic list handler to support mapping of SQL array types to Lists in Dapper
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public class GenericListHandler<T> : SqlMapper.TypeHandler<List<T>>
    {
        public override void SetValue(IDbDataParameter parameter, List<T>? value)
        {
            parameter.Value = value;
        }

        public override List<T> Parse(object value)
        {
            var typedValue = (T[])value;
            return typedValue.ToList();
        }
    }

Credit to @madmox's sample and Jesper Niedermann.