How to write a query with the IN operator using Dapper and NPGSql

Scenario

We want to get details of users which match a given list of ids.

The SQL

Select *
FROM users
WHERE id IN (1, 2, 3)

Naive implementation in csharp

void GetUsers(IReadOnlyCollection<int> userIds)
{
    const sql = @"Select * FROM users
                WHERE id IN @UserIds"

    var parameters = new Dictionary<string, object>
                    {
                        { "UserIds", userIds }
                    };

    using (var connection = new NpgsqlConnection(_connectionString))
    {
        var users = connection.Query<User>(
                        sql,
                        parameters.ToDynamicParameters())
                    .ToList();
    }
}

Notes:

• We are using NPGSql and Dapper.
• ToDynamicParameters is an extension method to transform the dictionary into Dapper.DynamicParameters

Problem #1

On running this, we get an error

System.NotSupportedException: Npgsql 3.x removed support for writing
    a parameter with an IEnumerable value, use .ToList()/.ToArray()
    instead.

Luckily, the error message is telling us what we need to do. So let’s make userIds an Array

void GetUsers(IReadOnlyCollection<int> userIds)
{
    //unchanged

    var parameters = new Dictionary<string, object>
                    {
                        { "UserIds", userIds.ToArray() } };

    // unchanged
}

Problem #2

We now get the error

Npgsql.PostgresException : 42601: syntax error at or near "$2"

Unfortunately, this is a more cryptic error.

There are 2 changes we need to make to the SQL to fix this error.

• parens are needed around the parameter
• IN needs to be replaced with = ANY

Our resulting SQL now becomes

void GetUsers(IReadOnlyCollection<int> userIds)
{
    const sql = @"Select * FROM users
                WHERE id = ANY(@UserIds)"

     // unchanged
}

Final Solution

void GetUsers(IReadOnlyCollection<int> userIds)
{
    const sql = @"Select * FROM users
                WHERE id = ANY(@UserIds)"

    var parameters = new Dictionary<string, object>
                    {
                        { "UserIds", userIds.ToArray() }
                    };

    using (var connection = new NpgsqlConnection(_connectionString))
    {
        var users = connection.Query<User>(
                        sql,
                        parameters.ToDynamicParameters())
                    .ToList();
    }
}

This allows us to get details of users which match a given list of ids.