postgresql where .. in error

[ensleep]

My sql where usercode in @usercodes",new {usercodes=new string[]{"1","2"} failed in dapper with npgsql。

List Support
Dapper allows you to pass in IEnumerable<int> and will automatically parameterize your query.

For example:

connection.Query<int>("select * from (select 1 as Id union all select 2 union all select 3) as X where Id in @Ids", new { Ids = new int[] { 1, 2, 3 } });
Will be translated to:

select * from (select 1 as Id union all select 2 union all select 3) as X where Id in (@Ids1, @Ids2, @Ids3)" // @Ids1 = 1 , @Ids2 = 2 , @Ids2 = 3

The docs says that the array params will be translated to like Id in (@Ids1, @Ids2, @Ids3). But I find that it is not when I debug the dapper csproj . In

在 Dapper.SqlMapper.ExecuteReaderWithFlagsFallback(IDbCommand cmd, Boolean wasClosed, CommandBehavior behavior) 在 F:\Download\Dapper-main\Dapper-main\Dapper\SqlMapper.cs 中: 第 1066 行

The image shows that the commandText is in @usercodes, but not like in (@Ids1, @Ids2, @Ids3) as the docs says.

--------------- 2022-06-16 14:25:53 ----------------- I found that it was transleted in oracle ,but not in pgsql. Can it be trans in pgsql too?

[znyet]

postgresql you can use ANY

where id=ANY(@ids)

[ensleep]

postgresql you can use ANY

where id=ANY(@ids)

Yes,Thank you .But I want to know is it true that dapper trans array params to in (@a1,@a2,@3)？

[JoshuaRogers]

I would love to know as well. I'm trying to write SQL that works on either Postgres or Oracle and, like the parent, I'm finding that the SQL gets rendered different on each platform.

Using the example query for Postgres remains unchanged on Postgres:

SELECT * FROM users WHERE user_type = ANY($UserTypes)

On Oracle, however, the rendered query has an extra set of parenthesis and the individual parameters similar to the following. (I might not have the SQL exact, as I'm running from memory at the moment, however the extra parenthesis were definitely there for Oracle.)

SELECT * FROM users WHERE user_type = ANY((:UserType1, :UserType2, :UserType3))

The effect of this is that, on Oracle, I have to write WHERE user_type = ANY :UserType (no parenthesis) and that on Postgres, I have to make sure to include them.

[JoshuaRogers]

@ensleep : Looking through the code, the answer is "sort of".

According to the following, Dapper is aware that NPGSQL supports arrays and knows that it can just pass the array to it. If you expand the code snippet though, you'll see that only one other database is listed with that knowledge. For all other databases, the default assumes that they do not support it:

https://github.com/DapperLib/Dapper/blob/6ec3804f2c44f2bf6b757dc3522bf009cc64b27d/Dapper/FeatureSupport.cs#L13

This value is then used in SqlMapper.cs where it determines whether it needs to perform expansion or not, based on whether the driver already knows how to handle it:

Then, reviewing https://github.com/DapperLib/Dapper/blob/6ec3804f2c44f2bf6b757dc3522bf009cc64b27d/Dapper/SqlMapper.cs#L1993

So yes, translation is supported, but only if the driver itself does not directly support accepting arrays.