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.
Top comments (0)