Using Dapper and Npgsql unable to select from a list of Guids

Viewed 513

Taking an example from a previous post; I am unable to query using IN and a list of Guids; I get different errors depending on what I have tried...

public class DataAccess
{
    string _connectionString = "{your connection string}";

    public async Task<IEnumerable<CustomerDto>> GetListAsync(List<Guid> customers)
    {
        const string query = @"
            SELECT Id,
                    Name
            FROM Customers
            WHERE Id IN @CustomerIdList
        ";

        using (var c = new SqlConnection(_connectionString))
        {
            return await c.QueryAsync<CustomerDto>(query, new { CustomerIdList = customers.ToArray() });
        }
    }
}

The above fails with 42601: syntax error at or near "$1"

I have tried various things like the below which also fails with 42601: syntax error at or near "$1":

return await c.QueryAsync<CustomerDto>(query, new { CustomerIdList = new[] { customers[ 0 ], customers[ 1 ], customers[ 2 ], customers[ 3 ] } } );

Can anyone help, what am I doing wrong?

EDIT: Fixed query due to copying example code example from another question

1 Answers

Found out that you have to use a different clause as IN does not work with an array of parameters in Postgresql which sounds like a bit of a flaw to me:

WHERE Id = ANY(@CustomerIdList)
Related