How to use the @> 'contains' operator with Dapper ORM

Question

c# dapper postgresql

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?

Update

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" }]}'

View more on Stack Overflow

Andrew · Accepted Answer · 4/14/2017 12:14:13 PM

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
";

Andrew