I have a List<Guid>
(also tried it with a plain array) that I would like to use in a query. Dapper generates the SQL and substitutes the parameters for numbered params correctly. However, PG complains about the syntax at the list parameter. No further details are given. Copying the generated SQL and substituting the numbers with actual values (adding parens to the list param), the query works.
Here's the offending clause in the generated SQL:
WHERE (
names.tenant = $1
AND scan_results.tenant = $1
AND scan_results.scan IN $2
)
The original statement string for Dapper is this:
WHERE (
file_names.tenant = @tenantId
AND scan_results.tenant = @tenantId
AND scan_results.scan IN @validScans
)
The query is executed using con.Query
like this:
con.Query(stmt, new
{
tenantId = tenant.Id,
validScans = tenant.LatestScans // .ToArray()
});
The exact Postgres error is:
ERROR: syntax error at or near "$2" at character ...
Turns out I had some misconceptions about both PGSQL and Dapper. First, parens are needed around the parameter, and second, I need to use ANY
, not IN
.
So the correct SQL looks like this: AND scan_results.scan = ANY (@validScans)