Deserialize Postgres ARRAY [@tags] to List<string> with Dapper?

Question

I'm using Dapper with Npsql (Postgres). I'm saving a serialized list of strings to one column called tags:

values.Tags = new List<string>() { "first", "second" };

await Connection.ExecuteAsync(
    @"INSERT INTO account (text, tags) VALUES (@text, ARRAY [@tags]);",
    new
    {
        text = values.Text,
        tags = values.Tags
    }
);

which is resulting in {{first,second}} in the database.

My question is how to read it back to the model? Do I need some special SQL, or can I use SELECT id, text, tags FROM account;?

How can I dapper tell to deserialize it back to List<string>?

Thanks

Answer 1

The c# dapper code would look like the following (where notes & comments are a list of strings):

string query = "INSERT INTO calendar.testtable VALUES (@A, @B, @C)";
await connection.ExecuteAsync(query, new { A = model.Key, B = model.Notes, C = model.Comments });

To let .NET, Dapper, and Postgres know how to handle the list of strings, create the following class.

public class StringListTypeHandler<T> : SqlMapper.TypeHandler<List<string>>
{
    public override List<string> Parse(object value)
    {
        return ((string[])value).ToList();
    }

    public override void SetValue(IDbDataParameter parameter, List<string> value)
    {
        parameter.Value = value.ToArray();
    }
}

Finally, add the following to your ConfigureServices() method within your Startup class

SqlMapper.AddTypeHandler(new StringListTypeHandler<List<string>>());

Answer 2

Here is the working TypeHandler class:

public class StringListTypeHandler : SqlMapper.TypeHandler<IList<string>>
{
    public override IList<string> Parse(object value)
    {
        var values = (string[,])value;

        IList<string> list = new List<string>();
        for (int i = 0; i < values.Length; i++)
        {
            list.Add(values[0, i]);
        }
        return list;
    }

    public override void SetValue(IDbDataParameter parameter, IList<string> value)
    {
        parameter.Value = value;
    }
}

Register it as: Dapper.SqlMapper.AddTypeHandler(new StringListTypeHandler());