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})";
}
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With