I have a problem with dapper when working with XML Query.
This query is working fine
SELECT COUNT(1)
FROM Agreements a INNER JOIN
AgreementParts ap ON ap.AgreementId = a.Id
WHERE a.ToRenew = 1
AND ap.AccountId = N'1234'
AND (a.Workflow.exist('//workflow/step/actor[@creator="true" and @mode="position" and text()="POS1"]') = 1 OR
a.Workflow.exist('//workflow/step/actor[@creator="true" and @mode="email" and text()="user@mail.com"]') = 1)
But calling this query with dapper and parameters returns always 0
exec sp_executesql N'
SELECT COUNT(1)
FROM Agreements a INNER JOIN
AgreementParts ap ON ap.AgreementId = a.Id
WHERE a.ToRenew = 1
AND ap.AccountId = @accountId
AND (a.Workflow.exist(''//workflow/step/actor[@creator="true" and @mode="position" and text()=@position]'') = 1 OR
a.Workflow.exist(''//workflow/step/actor[@creator="true" and @mode="email" and text()=@email]'') = 1)
',N'@accountId nvarchar(4000),@position nvarchar(4000),@email nvarchar(4000)',@accountId=N'1234',@position=N'POS1',@email=N'user@mail.com'
Here is the code I use to execute the query:
_dbConnection.Query<int>(toRenewSql, new { accountId = accountId, email = email, position = position })).First();
Hard coding the email and the position makes the query working.
I've finally found the problem, to use a sql parameter in a sql query you have to write the parameter name inside a sql:variable like that sql:variable("@Param");
exec sp_executesql N'
SELECT COUNT(1)
FROM Agreements a INNER JOIN
AgreementParts ap ON ap.AgreementId = a.Id
WHERE a.ToRenew = 1
AND ap.AccountId = @accountId
AND (a.Workflow.exist(''//workflow/step/actor[@creator="true" and @mode="position" and text()=sql:variable("@position")]'') = 1 OR
a.Workflow.exist(''//workflow/step/actor[@creator="true" and @mode="email" and text()=sql:variable("@email")]'') = 1)
',N'@accountId nvarchar(4000),@position nvarchar(4000),@email nvarchar(4000)',@accountId=N'1234',@position=N'POS1',@email=N'user@mail.com'