sqlkata/querybuilder

Unable to use a string array as a parameter in WhereRaw

Opened this issue · 0 comments

FYI I have seen issue #162

This is similar but I am trying to use .WhereRaw() with a string array.

This is the raw SQL I am trying to accomplish:

SELECT * FROM "v0"."Users" WHERE "Roles" && ('{"Member", "Admin"}');

My attempt at using SQL Kata looks like this

string str = String.Join(",", request.Roles.Select(r => $"\"{r.ToString()}\""));
str = $"'{{{str}}}'";
string raw = "\"Users\".\"Roles\" && (?)";
query = query.WhereRaw(raw, str);

The postgres server is telling me:

ERROR: operator does not exist: text[] && text;

The column I am querying is of type text[].

I have also tried just going totally RAW SQL without a parm, but that still isn't working:

string str = String.Join(",", request.Roles.Select(r => $"\"{r.ToString()}\""));
str = $"'{{{str}}}'";
string raw = $"\"Users\".\"Roles\" && ({str})";
query = query.WhereRaw(raw);

But my SQL ends up looking like this:

AND "Users"."Roles" && ('""Support","Admin""')

Looks like the {} chars are getting replaced with quotes?