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