Search code examples
c#.netdatabasepostgresqlnpgsql

Postgresql FromSqlInterpolated query with braces

Trying to use FromSqlInterpolated to query a postgres database, specifically a jsonb column of a table and consequently, I need to use curly braces in my query.

Here is the query in postgresql:

SELECT a.* from "assignment" a WHERE a."data"-> 'SubAssignmentCodes' @> '[{"Code": "remoteName"}]' or code = '{remoteName}'

Now trying to put that query into FromSqlInterpolated.

The problem is that c# thinks the braces in this part of the query ('[{"Code": "remoteName"}]') are interpolation braces rather than braces for the query.

I can obviously escape them but in doing so, it removes the braces all together from the final query that is submitted to the database.

I have even tried separating that part of the query out:

.FromSqlInterpolated($"SELECT a.* from \"assignment\" a WHERE a.\"data\" -> 'SubAssignmentCodes' @> {lastBit}::jsonb  or code = '{assignmentCode}'")

and

var lastBit = "'[{{\"Code\": \"{assignmentCode}\"}}]'";

This query almost works but gives this error:

Npgsql.PostgresException: '22P02: invalid input syntax for type json'

and here is my original attempt:

.FromSqlInterpolated($"SELECT a.* from \"assignment\" a WHERE a.\"data\"-> 'SubAssignmentCodes' @> '[{{\"Code\": {assignmentCode}}}]' or code = {assignmentCode}")
Solution

  • I changed lastBit to this:

    var lastBit = System.Text.Json.JsonSerializer.Serialize(new[] { new { Code = "assignmentCode" } });

    It now works. Turns out it really was invalid JSON and serializing it properly rather than writing the JSON into a string myself was the solution.