Dapper and NPGSql : Write a query with the IN operator

Published on

Last Updated on

Estimated Reading Time: 1 min

Scenario

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

The SQL

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

Implementation in C#

void GetUsers(IReadOnlyCollection<int> userIds)
{
    const sql = &#64;@"@Html.Raw("Select * FROM users")
                WHERE id IN &#64;@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 = &#64;@"@Html.Raw("Select * FROM users")
                WHERE id IN &#64;@UserIds"
   // unchanged
}

Final Solution

void GetUsers(IReadOnlyCollection<int> userIds)
{
    const sql = &#64;@"@Html.Raw("Select * FROM users")
                WHERE id IN &#64;@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 that match a given list of ids.