Unable to use an array as a parameter in WhereRaw

[jonjomckay]

I'm trying to compare a JSON property in PostgreSQL against a list of values, but the values I'm passing seem to always be flattened into individual parameters instead of the array I'm giving.

A paraphrasing of my code is:

var ids = persistenceQuery.Where
                .Where(where => where.ColumnName.EqualsIgnoreCase("typeElementId"))
                .Select(where => Guid.Parse(where.Value))
                .ToArray();

query.WhereRaw("(json->'typeElementId'->>'guid')::uuid = ANY(?)", ids);

I've also tried this, but it seems to still be flattening the nested array:

query.WhereRaw("(json->'typeElementId'->>'guid')::uuid = ANY(?)", new object[] { new object[] { ids } });

I think this would be solved if I could disable the quoting of column names inside WhereIn also, as PostgreSQL doesn't seem the JSON column I'm querying as an actual column.

[ahmad-moussawi]

What you can do for now, is to pass the values directly in the expression like

var idsList = string.Join(",", ids);
query.WhereRaw($"(json->'typeElementId'->>'guid')::uuid = ANY({idsList})");

of course be aware of sql injection.

[jonjomckay]

Mmmm, I was hoping to not have to do that, as it means each query needs to be replanned and prepared and this is part of quite a complex one.

I'm happy to do a PR if you could point me in the way of how you think it would best be solved!

[ahmad-moussawi]

@jonjomckay am trying to simulate your query, but am getting syntax error, however using the `in` operator works fine, could you confirm please ?

[jonjomckay]

Sorry for the lack of reply @ahmad-moussawi, I was away on Christmas holidays. This looks like it'll fix it for me - thanks!