下面的查询总是产生错误"42601: "$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 } });
我已经解决了这个问题,通过在下面创建一个方法,并调用它内联来构建SQL查询。想知道如果Dapper可以处理这个对象参数虽然?
public static string ToInSql(this IEnumerable<object> values)
{
var flattened = values.Select(x => $"'{x}'");
var flatString = string.Join(", ", flattened);
return $"({flatString})";
}
PostgreSQL IN
操作符不支持数组(或任何其他集合)作为参数,只有一个正常的列表(你用ToInSql
方法生成的),对于PostgreSQL你需要使用ANY
操作符,像这样:
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