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

c# dapper postgresql

Question

Possessing the following issue:

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)

After adjusting the dapper dynamic settings,

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

When I execute the query, I get the following error:

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

Since I had examined Npgsql receiving 42601 from PostgreSQL: syntax error at or around "$1" and it didn't help, the issue here must be distinct.

a simple illustration

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

To determine if a value is included inside an array in PostgreSQL, you must use the PostgreSQL-specific syntax shown below:where t.name = ANY (@tagsParam) . See the PostgreSQL manuals, section 8.15.5.

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