loading...
Cover image for How to write a query with the IN operator using Dapper and NPGSql

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

ankursheel profile image Ankur Sheel Originally published at ankursheel.com on ・2 min read

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.

Discussion

markdown guide