"其中x Y'QUOT;用短小精悍和PostgreSQL抛出42601条款:在或接近语法错误\\" $ 1 \\" [英] "WHERE x IN y" clause with dapper and postgresql throwing 42601: syntax error at or near \"$1\"
问题描述
我有一个字符串数组,我想有一个包含IN子句,就像一个查询:
... WHERE t.name IN('富','酒吧','巴兹')..>中
下面是我的查询,其中包含一个,其中X的Y条款的最后一位:
...
左连接上hb.id = tj.band_id或ob.id = tj.band_id genre_tag_band_join TJ
左连接genre_tags T于tj.genre_tag_id = t.id
内部联接场馆V,而e.venue_id = v.id其中,在t.name ... @tagsParam
我打个电话小巧玲珑像这样
VAR显示= con.Query<事件,乐队,乐队,GenreTag,地点,事件>(查询(即HB,OB,GT,V)=>
{
事件显示;
...
返回e的;
},
新{tagsParam = tagsArr})AsQueryable已();
其中tagsArr是一个字符串[]。
我得到异常:
{$ 1 \\,42601或接近\\语法错误}
块引用>解决方案在PostgreSQL里,你不能在检查一个值是否为数组里面,你必须使用以下PostgreSQL相关语法:
其中t.name = ANY @tagsParam
。请参见 PostgreSQL的文档节8.15.5。I have an array of strings, and I'd like to have a query containing an IN clause, like:
"... WHERE t.name IN ('foo', 'bar', 'baz')..>"
Here's the final bit of my query, which contains a "where X in Y" clause:
... left join genre_tag_band_join tj on hb.id = tj.band_id or ob.id = tj.band_id left join genre_tags t on tj.genre_tag_id = t.id inner join venues v on e.venue_id = v.id where t.name IN @tagsParam...
I make a Dapper call like this
var shows = con.Query<Event, Band, Band, GenreTag, Venue, Event>(query, (e, hb, ob, gt, v) => { Event show; ... return e; }, new { tagsParam = tagsArr}).AsQueryable();
where tagsArr is a string[].
I get exception:
{"42601: syntax error at or near \"$1\""}
解决方案In PostgreSQL, you can't use IN to check whether a value is inside an array, you have to use the following PostgreSQL-specific syntax:
where t.name = ANY @tagsParam
. See the section 8.15.5 in the PostgreSQL docs.这篇关于&QUOT;其中x Y'QUOT;用短小精悍和PostgreSQL抛出42601条款:在或接近语法错误\\&QUOT; $ 1 \\&QUOT;的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!