6

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?

Improve this question
14
  • I'm a bit rusty on SQL but SELECT ARRAY[1, 2, 3] does not seem to be common SQL, postgres or no? I'd probably suggest that Dapper possibly just doesn't support this pretty funky syntax Commented Jul 15, 2020 at 13:30
  • I'm not familiar with postgres, does query return 3 rows of one column? One row of 3 columns? Or one row, once column? Commented Jul 15, 2020 at 13:30
  • @Crowcoder It returns a single row with the value {1, 2, 3} Commented Jul 15, 2020 at 13:31
  • As a string? Well how would you expect Dapper to magically know that is a list of ints? Commented Jul 15, 2020 at 13:32
  • 1
    @Liam The following query SELECT pg_typeof(ARRAY[1,2,3]) returns "integer[]", so I was expecting Dapper to somehow know that is not a string. Also, getting the result as a string throws an exception. Commented Jul 15, 2020 at 13:52

2 Answers 2

Reset to default
10

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.

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

3 Comments

You could probably just do this with a Generic typehandler as well. I.e. GenericArrayHandler<T> : SqlMapper.TypeHandler<T[]> - and add new GenericArrayHandler<int>(). Then the goal could easily be achieved for any array type encountered.
I tested it with a GenericArrayHandler as mentioned, and it is indeed just as good. Which makes it extremely easy to use array columns for any type.
I changed the answer to use a GenericArrayHandler instead to accomodate other types than int
0

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.

Improve this answer

1 Comment

There's an answer above. If it works, please mark it as accepted, or comment if it is not.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.