c# – Dapper.net“where … in”查询不适用于PostgreSQL
发布时间:2020-12-15 08:28:48 所属栏目:百科 来源:网络整理
导读:以下查询始终生成错误“42601:语法错误在或接近”$1“ ”. connection.QueryCarStatsProjection( @"select manufacturer,model,year,AVG(price) as averageprice,AVG(miles) as averagemiles,COUNT(*) as count from products where manufacturer IN @manuf
以下查询始终生成错误“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,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,COUNT(*) as count FROM products WHERE manufacturer = ANY(@manufacturers) AND model = ANY(@models) AND year = ANY(@years) GROUP BY manufacturer,year (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |