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

Question (and for context, I wrote Dapper, and don't use Postgresql): if you use select id, text, tags (as per the question), what is reader.GetValue(0).GetType() on a reader for that query? It influences how to do this — Marc Gravell
– Marc Gravell, Commented Apr 18, 2021 at 19:53
reader.GetValue(2).GetType() (not index zero, but index 2, index 0 is an 'id' column which you are probably not interested in) returns: {Name = "String[,]" FullName = "System.String[,]"}. reader.GetValue(2) value is {string[1, 2]} of type object {string[,]}. — o..o
– o..o, Commented Apr 18, 2021 at 20:41
interesting - a 2D string array; I would not have guessed that, but I can also confirm: Dapper has no specific handling built in for that type (or any other 2D array as output). We could certainly add support if it is a common scenario, but it isn't a need I've encountered (probably because I don't use postgresql) — Marc Gravell
– Marc Gravell, Commented Apr 19, 2021 at 6:39
Is there a way I can hookup into the parsing and do it for a specific model/property? — o..o
– o..o, Commented Apr 19, 2021 at 7:50
there is a type-handler API in Dapper, but: I honestly haven't tried it in this scenario — Marc Gravell
– Marc Gravell, Commented Apr 19, 2021 at 7:59

Zachary Glontz · Accepted Answer · 2022-03-23 02:27:19Z

2

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>>());

edited Mar 23, 2022 at 2:27

answered Mar 20, 2022 at 2:22

Zachary Glontz

364 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

o..o · Accepted Answer · 2021-04-19 12:52:28Z

0

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());

answered Apr 19, 2021 at 12:52

o..o

1,9317 gold badges37 silver badges65 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related