Using the PostgreSQL array type with Dapper

PostgreSQL has some fancy types like the array type. But its not obvious how to return e.g. an int[] or an uuid[] in a single row from Dapper.

In fact the first hit on StackOverflow said it was impossible.

Not so… with a little insight, luck and duct tape you can do anything. It’s actually quite easy.

If you use Dapper and NpgSql “out of the box” you will get an error “Invalid type owner for DynamicMethod” if you go for an array in your select like this:

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

The only thing you need to fix this is a TypeHandler which you can use to tell Dapper how to handle the array type.

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

I made it generic to make it work for all kinds of types.

Then you just add this handler 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.

I have also tested the handler with an uuid[] which works just as fine:

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

And now StackOverflow is updated with the correct answer 😏