Using the Dapper ORM I'm constructing the following query to get a specific order from my PostgreSQL datastore:
string.Format("SELECT * FROM OrderTable WHERE orderjson @> {0}",
"'{\"Lines\":[{\"SKUID\":\"@SkuId\"}]}'");
Passing 123456-0
into the paramater @SkuId
this construction should result into the following query
SELECT * FROM ordertable WHERE orderjson @> '{"Lines":[{"SKUID":"123456-0"}]}'
However Dapper seems to have some trouble with the @>
contains operator, as the query always returns no results.
How do I use this @>
operator with Dapper?
I updated my construction to the following:
$@"
SELECT * FROM ordertable
WHERE orderjson @> '{{""Lines"":[{{""SKUID"": ""@SkuId"" }}]}}'
";
Dapper seems to ignore the paramter @SkuId
as my query looks like
SELECT * FROM ordertable
WHERE orderjson @> '{"Lines":[{"SKUID": "@SkuId" }]}'
See also this Github issue which might be useful as well.
We end up splitting into two statements:
SkuLineJson = $@"{{""Lines"":[{{""SKUID"": ""{object.SkuId}""}}]}}"
And use this variable in our sql statement construction:
$@" SELECT * FROM ordertable
WHERE orderjson @> json_in(@SkuLineJson::cstring)::jsonb
";