Dapper.net "where ... in" query doesn't work with PostgreSQL

Question

The following query always produces the error "42601: syntax error at or near "$1" ".

connection.Query<CarStatsProjection>(
                @"select manufacturer, model, year, AVG(price) as averageprice, AVG(miles) as averagemiles, COUNT(*) as count
                        from products
                        where manufacturer IN @manufacturers 
                            AND model IN @models
                            AND year IN @years
                        group by manufacturer, model, year",
                new { manufacturers = new[] { "BMW", "AUDI" }, 
                      models = new[] { "M4", "A3" }, 
                      years = new[] { 2016, 2015 } });

I have got around this by creating a method below and calling it inline to build the SQL query for now. Would like to know if Dapper can handle this with the object param though?

 public static string ToInSql(this IEnumerable<object> values)
    {
        var flattened = values.Select(x => $"'{x}'");
        var flatString = string.Join(", ", flattened);

        return $"({flatString})";
    }

Answer 1

PostgreSQL IN operator doesn't support array (or any other collection) as parameter, only a normal list (the one which you're generating with the ToInSql method), for PostgreSQL you need to use ANY operator, like this:

SELECT manufacturer, model, year, AVG(price) as averageprice, AVG(miles) as averagemiles, COUNT(*) as count
FROM products
WHERE manufacturer = ANY(@manufacturers)
AND model = ANY(@models)
AND year = ANY(@years)
GROUP BY manufacturer, model, year