ExecuteInterpolatedSql with a WHERE IN Int[] statement

Believe that in SQL, you need STRING_SPLIT the parsed value with separator: ,, then cast the value to INTEGER

string integerStr = string.Join(",", integers);

FormattableString query = 
  $"DELETE FROM Table1
  WHERE Type = {value}
  AND CAST(SUBSTRING(Value, 5, 7) AS INTEGER) 
    IN (SELECT CAST(value AS INTEGER) FROM STRING_SPLIT({integerStr}, ','))";

SQL

DELETE FROM Table1
  WHERE Type = @value
  AND CAST(SUBSTRING(Value, 5, 7) AS INTEGER) 
    IN (SELECT CAST(value AS INTEGER) FROM STRING_SPLIT('100,404,777', ','))

Updated: Miss out on the explanation of why string concatenation doesn't work correctly.

According to Passing parameters,

While this syntax may look like String.Format syntax, the supplied value is wrapped in a DbParameter and the generated parameter name inserted where the {0} placeholder was specified.

The value was passed as the DbParameter to the query as

DELETE FROM Table1
  WHERE Type = @value
  AND CAST(SUBSTRING(Value, 5, 7) AS INTEGER) IN (@P1)

So the final query will be looked like:

DELETE FROM Table1
  WHERE Type = @value
  AND CAST(SUBSTRING(Value, 5, 7) AS INTEGER) IN ('100,404,700')

The real solution here is to use a Table Valued Parameter.

First, create a Table Type in SSMS. I usually keep a few standard one and two column types for this purpose.

CREATE TYPE dbo.IntList (value int PRIMARY KEY);

Then use it like this

var value = "example";
var integers = new List<int> { 100, 404, 777 };

var table = new DataTable { Columns = {
    {"value", typeof(int)},
} };

foreach (var v in integers)
    table.Rows.Add(v);

var tableParam = new SqlParameter("@tbl", SqlDbType.Structured)
{
    TypeName = "dbo.IntList",
    Value = table,
};

FormattableString query = $@"
DELETE FROM Table1
  WHERE Type = {value}
  AND CAST(SUBSTRING(Value, 5, 7) AS INTEGER) IN
      (SELECT value FROM {tableParam})";

await _signInDbContext.ExecuteSqlInterpolatedAsync(query);

// alternately use Raw

const string query = @"
DELETE FROM Table1
  WHERE Type = {0}
  AND CAST(SUBSTRING(Value, 5, 7) AS INTEGER) IN
      (SELECT value FROM {1})";

await _signInDbContext.ExecuteSqlRawAsync(query, value, tableParam);