如何将逗号分隔整数字符串转为ExecuteSql可用的逗号分隔整数

问题场景 #

当尝试将字符串形式的ID列表（如'1,2'）作为SQL IN条件时，触发类型转换异常，错误信息：

Conversion failed when converting the nvarchar value '1,2' to data type int.

原始代码 #

var ids = string.Join(',', students.Select(p => p.Id).ToArray());
var studentResult = await _dbContext.Database.ExecuteSqlAsync($"UPDATE dbo.AspNetUsers SET Attended = Attended + 1, EditedDate = CURRENT_TIMESTAMP WHERE Id IN ({ids})");

终端错误日志 #

[2023-08-03T19:29:26.800Z] Failed executing DbCommand (205ms) [Parameters=[@p0='1,4' (Size = 4000)], CommandType='Text', CommandTimeout='30']
[2023-08-03T19:29:26.801Z] UPDATE dbo.AspNetUsers SET Attended = Attended + 1, EditedDate = CURRENT_TIMESTAMP WHERE Id IN ('@p0')
[2023-08-03T19:29:38.832Z] AZURE FUNCTION: ServiceBusConsumer Error: Conversion failed when converting the varchar value '@p0' to data type int.
[2023-08-03T19:29:38.832Z] Result: AZURE FUNCTION: ServiceBusConsumer Error: Conversion failed when converting the varchar value '@p0' to data type int.
[2023-08-03T19:29:38.832Z] Exception: Microsoft.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting the varchar value '@p0' to data type int.
[2023-08-03T19:29:38.832Z]    at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
[2023-08-03T19:29:38.832Z]    at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
[2023-08-03T19:29:38.832Z]    at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
[2023-08-03T19:29:38.832Z]    at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

调试代码 #

添加以下代码可查看生成的SQL语句：

Console.WriteLine($"UPDATE dbo.AspNetUsers SET Attended = Attended + 1, EditedDate = CURRENT_TIMESTAMP WHERE Id IN ({ids})");

问题根源 #

Entity Framework的ExecuteSqlAsync会自动对字符串进行参数化处理，导致原本期望的IN (1,4)被处理成IN ('@p0')，SQL Server将整个ID列表视为单个字符串值，尝试转换为int类型时触发异常。

解决方法 #

方法1：手动构建参数化查询（避免SQL注入） #

为每个ID创建独立参数，让EF正确识别类型：

var idList = students.Select(p => p.Id).ToList();
var parameters = new List<SqlParameter>();
var paramNames = new List<string>();

for (int i = 0; i < idList.Count; i++)
{
    var paramName = $"@id{i}";
    parameters.Add(new SqlParameter(paramName, idList[i]));
    paramNames.Add(paramName);
}

var sql = $"UPDATE dbo.AspNetUsers SET Attended = Attended + 1, EditedDate = CURRENT_TIMESTAMP WHERE Id IN ({string.Join(',', paramNames)})";
var studentResult = await _dbContext.Database.ExecuteSqlAsync(sql, parameters.ToArray());

方法2：使用EF Core LINQ查询（推荐） #

EF Core会自动将Contains转换为正确的参数化IN查询，无需手动拼接SQL：

var idList = students.Select(p => p.Id).ToList();
var targetUsers = _dbContext.AspNetUsers.Where(u => idList.Contains(u.Id));

foreach (var user in targetUsers)
{
    user.Attended += 1;
    user.EditedDate = DateTime.UtcNow;
}

await _dbContext.SaveChangesAsync();

方法3：表值参数（适合大量ID场景） #

当ID数量较多时，使用SQL Server表值参数更高效：

1. 在SQL Server中创建表类型：

CREATE TYPE IntList AS TABLE (Id INT);

2. C#代码实现：

var idTable = new DataTable();
idTable.Columns.Add("Id", typeof(int));
foreach (var id in students.Select(p => p.Id))
{
    idTable.Rows.Add(id);
}

var tvpParam = new SqlParameter("@Ids", SqlDbType.Structured)
{
    TypeName = "IntList",
    Value = idTable
};

var sql = "UPDATE dbo.AspNetUsers SET Attended = Attended + 1, EditedDate = CURRENT_TIMESTAMP WHERE Id IN (SELECT Id FROM @Ids)";
var studentResult = await _dbContext.Database.ExecuteSqlAsync(sql, tvpParam);

内容的提问来源于stack exchange，提问作者chuckd