ExecuteInterpolatedSql with a WHERE IN Int[] statement-CodePudding

I'm trying to execute a query using ExecuteInterpolatedSqlAsync that checks if a specific substring is included in an array of integers.

What I've tried is this:

var value = "example";
var integers = new List<int> { 100, 404, 777 };
FormattableString query = 
  $"DELETE FROM Table1
  WHERE Type = {value}
  AND CAST(SUBSTRING(Value, 5, 7) AS INTEGER) IN ({string.Join(",", integers)})";

await _signInDbContext.ExecuteSqlInterpolatedAsync(query);

When I debug this, it shows the string as follows:

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

But when it runs the query against the Database it puts quotes around both parameters:

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

This makes sense because both are a string. But it's not what I want (obviously), I get this error:

Conversion failed when converting the nvarchar value '100,404,777' to data type int

How can I include an integer array in this ExecuteSqlInterpolatedAsync query?

CodePudding user response：

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')

CodePudding user response：

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);