Postgresql using Dapper to force "ANY(VALUES(), ()...)" for a list

Question

I have a query simply like this:

SELECT restaurant_id as Id FROM restaurants WHERE unique_key = ANY(ARRAY['key1', 'key2', ...])

According to the article 100x faster Postgres performance by changing 1 line, using VALUES instead of ARRAY can improve the performance. I've also tried the solution and the query has better performance:

SELECT restaurant_id as Id FROM restaurants WHERE unique_key = ANY(VALUES('key1'), ('key2'), ...)

But I am passing the keyList via Dapper:

SELECT restaurant_id as Id FROM restaurants WHERE unique_key = ANY(@keyList)

and it translete the query with array:

SELECT restaurant_id as Id FROM restaurants WHERE unique_key = ANY($1)

My question is how can I force Dapper to use the query with VALUES?

And this is the implementation roughly:

var query = @"SELECT restaurant_id as Id FROM restaurants WHERE unique_key = ANY(@keyList)";

var keyList = new[] { "key1", "key2", ... };
List<int> list;
using (var connection = new NpgsqlConnection(connectionString))
{
    list = await connection.QueryAsync<int>(query, new {keyList});
}

how do you pass with dapper? can you add the relevant part of the code? — Derviş Kayımbaşıoğlu, Sep 24 '20 at 11:54

Palle Due · Answer 1 · 2020-09-25T07:57:27.483

This is not supported the same way in various databases and Dapper doesn't know which DBMS you are using, so I don't think you can get Dapper to do it. I would just build it myself like:

var keyList = new[] { "key1", "key2", ... };
var keyValues = String.Join(",",keyList.Select(x=>$"('{x}')"));
var query = $@"SELECT restaurant_id as Id FROM restaurants WHERE unique_key = ANY(VALUES{keyValues})";

List<int> list;
using (var connection = new NpgsqlConnection(connectionString))
{
    list = await connection.QueryAsync<int>(query);
}

You should of course take precautions if keyList is user input and protect against SQL Injection.

Postgresql using Dapper to force "ANY(VALUES(), ()...)" for a list

1 Answers1