使用WHERE IN Int[]语句执行InterpolatedSQL

Question

我正在尝试使用ExecuteInterpolatedSqlAsync执行一个查询，该查询检查整数数组中是否包含特定的子字符串。

我试过的是：

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

当我调试它时，它显示如下字符串：

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

但当它对数据库运行查询时，它会在两个参数周围加引号：

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

这是有道理的，因为两者都是一个字符串。但这不是我想要的(显然(，我得到了这个错误：

将nvarchar值"100404777"转换为数据类型int 时转换失败

如何在此ExecuteSqlInterpolatedAsync查询中包含整数数组？

Answer 1

相信在SQL中，您需要STRING_SPLIT使用分隔符,解析的值，然后将value强制转换为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', ','))

---

更新：遗漏了字符串串联无法正常工作的原因解释

根据通过参数，

虽然此语法看起来像String.Format语法，但提供的值被包装在DbParameter中，生成的参数名称插入到指定｛0｝占位符的位置。

该值作为DbParameter作为传递给查询

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

因此，最后的查询将看起来像：

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

Answer 2

这里的real解决方案是使用表值参数

首先，在SSMS中创建一个表类型。为此，我通常保留一些标准的一列和两列类型。

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

然后像这个一样使用它

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