How to stop ExecuteSqlInterpolated from wrapping string params with single quotes?

Question

I have a call in EF Core 3 to ProgresSQL server that has multiple string parameters. One of them is a string which is a concatenation of strings already wrapped with single quotes like this:

var listOfParams = "'a',','b','c'";

and later passed to a query which looks like this:

var id = "Customer1";
DbContext.Database.ExecuteSqlInterpolated(
   "SELECT ""CustomerNumber"" FROM dbo.""Customer"" 
      WHERE ""Id"" = {id} AND ""CustomerNumber"" IN ({listOfParams})");

The problem is that while wrapping id with quotes is correct, for the second parameter which has quotes already a second wrapping occurs of some sort or possibly escaping of single quotes and this is what I would like to avoid. Yet, I can't find any relatively simple way. Perfectly if it is possible to keep listOfParams as an SQL param, otherwise caching of query plan would be impossible. Passing a list directly doesn't seem to work either. Looks like EF Core doesn't support it.

Thanks, Radek

Answer 1

Have you tried just using a LINQ approach?

var listOfParams =  "a b c".Split();
var r = DBContext.Customer
  .Where(c => c.Id == id && listOfParams.Contains(c.CustomerNumber))
  .Select(c => c.CustomerNumber);

In SQLServer .Contains is mapped to IN - PG may well be the same

Answer 2

If what you're looking for is to parameterize your list (a, b, c), then you probably want to pass an array parameter - this should be fully supported by Npgsql and the EF Core provider, and properly reuse query plans/prepared statements.

Note that for arrays, you need to use PG's = ANY (<array>) construct rather than IN.

var id = "Customer1";
var listOfParams = new[] { "a", "b", "c" };

DbContext.Database.ExecuteSqlInterpolated(
   "SELECT ""CustomerNumber"" FROM dbo.""Customer"" 
      WHERE ""Id"" = {id} AND ""CustomerNumber"" = ANY ({listOfParams})");