Dapper.net "where…in"查询不能在PostgreSQL中工作

本文关键字：quot PostgreSQL 工作 不能 net where Dapper in 查询 | 更新日期: 2023-09-27 17:54:49

下面的查询总是产生错误"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})";
    }

Dapper.net "where…in"查询不能在PostgreSQL中工作

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
相关文章：