Dapper.net和Quot；where...查询不能与PostgreSQL一起使用(&Q；) [英] Dapper.net "where ... in" query doesn't work with PostgreSQL

问题描述

以下查询始终生成错误"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})";
    }

推荐答案

运算符不支持数组(或任何其他集合)作为参数，只支持普通列表(用ToInSql方法生成的列表)，需要使用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

这篇关于Dapper.net和Quot；where...查询不能与PostgreSQL一起使用(&Q；)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！