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?
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.
The answer is in the comments, but TL;DR this can't be done. The array has to be converted to a string and then deserialized into the list.
I'm answering this question instead of closing it, since I believe it's a valid question if you've mostly used Postgres and maybe expect arrays to be a thing in other RDMSs.