How to deserialize array using Dapper

Question

I have a model that has a HashSet<int> member. Since I wasn't able to get Dapper to automatically create the set from an array, I thought I would just use a multi-mapping query and get the array as a separate column, split on it and create the set inside a lambda. Just to be clear, I want to deserialize the whole model, not just the bit for the set.

My first multi-mapping attempt also failed, so I decided to try something simpler, get an array and deserialize it into a List<int> or int[]. And this is where I'm stuck.

I am using a PostgreSQL 12 database.

Code snippet is as follows

var query = "SELECT ARRAY[1, 2, 3];";

var ints1 = await _conn.QueryAsync<List<int>>(query); // This returns an empty array
var ints2 = await _conn.QueryAsync<int[]>(query); // System.ArgumentException: Invalid type owner for DynamicMethod.

I'm not really sure what I'm doing wrong and Google doesn't really seem to help, I only find questions about using lists in queries.

Edit: Ok, I got it working by converting the array into a JSON, but I still find this solution ugly. Is there a way to avoid creating JSONs?

Answer 1

Don't believe everything you hear, although its stated above that its not possible it's actually easily achieveable.

It's true that the query below will give you an error: (Invalid type owner for DynamicMethod):

var arrayOfThreeElements =  
        (await _conn.QueryAsync<int[]>("SELECT ARRAY[1, 2, 3]"))                  
              .FirstOrDefault();

What you need to do is to tell dapper how to handle this using a small TypeHandler:

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;
}

The Parse method is the important one here.

Then you just add this handler where you have added all your other typehandlers - i.e in the Initialization code of your app or website like this:

SqlMapper.AddTypeHandler(new GenericArrayHandler<int>());

Now the query works and returns the 3 elements, 1,2 and 3 in an integer array.