Why I get the following error Npgsql.PostgresException: '42601: syntax error at or near "$1"' with Dapper?

c# dapper postgresql

Question

Having the following query:

select count(t.*)
from template t
where t.available = true
    and exists (select *
                from tag
                join template_tag on template_tag.tag_id = tag.id
                where template_tag.template_id = t.id and tag.name in @tags)

And having set the dapper dynamic parameters to:

new { Tags = new List<string> { "tagExample" } }

I am getting the following error when running the query:

Npgsql.PostgresException: '42601: syntax error at or near "$1"'

I already checked PostgreSQL, Npgsql returning 42601: syntax error at or near "$1" and that didn't help so this must be a different problem.

Minimal example:

const string query = @"
    select count(t.*)
    from template t
    where exists (select *
                  from template_module tm
                  where tm.template_id = t.id and tm.module_properties in @props)
";

var parameters = new { props = new List<string> { "{}"} };

var result = await conn.ExecuteScalarAsync<int>(query, parameters);
1
-1
12/14/2018 11:35:24 AM

Accepted Answer

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.

26
8/4/2016 3:17:57 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow