Dapper List Parameter rejected by Postgres

Question

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 ...

turbo · Accepted Answer · 2018-09-25 13:15:25Z

7

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)

answered Sep 25, 2018 at 13:15

turbo

1,26316 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nij Over a year ago

Thanks! I can confirm this works for integers too, as you would probably expect.

Collectives™ on Stack Overflow

Dapper List Parameter rejected by Postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related