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

Question

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

You should be able to parameterize the query. Does the sql work in the pgsql console? — Dustin Kingen, Apr 13 '17 at 15:08
Doesn't Dapper only do something special with a query when it uses curly braces? That may be where the problem is? — DavidG, Apr 13 '17 at 15:10
Try profiling to see exactly what is being passed to your DB. — juharr, Apr 13 '17 at 15:23
@juharr any recommendation for a good/simple profiler for this case? — Andrew, Apr 13 '17 at 15:24
@DavidG maybe, can't find anything yet regarding this but will look further on that as well — Andrew, Apr 13 '17 at 15:25
Note that Dapper is open source, so you can check it out to see what might be happening. As for profilers I just use the one that comes with SQL Server. — juharr, Apr 13 '17 at 15:26
[PostgreSQL equivalent of SQL Server profiler](http://stackoverflow.com/questions/2430380/is-there-a-postgresql-equivalent-of-sql-server-profiler) — stuartd, Apr 13 '17 at 15:33
@AndriiLitvinov that makes no difference unfortunately, I _think_ dapper is confused because of the `@>` operator — Andrew, Apr 14 '17 at 10:06
This issue on gitbub might be useful https://github.com/pgjdbc/pgjdbc/issues/575. Try to prepare entire json as a parameter but beware of sql injection. — Andrii Litvinov, Apr 14 '17 at 12:02
Hacky workaround - dapper just wants to see the parameter name in the sql; if the member name is `foo`, you can include a SQL comment of `/* @foo */` and the member will be sent... — Marc Gravell, Apr 14 '17 at 15:45

score 1 · Answer 1 · answered Apr 14 '17 at 12:14

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

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

Update

1 Answers1