英文:
How do I convert a string of comma separated integers to just comma separated ints for a ExecuteSql command?
问题
I'm getting an exception because the ids are a string
> ex. '(1,2)'
I need it be comma separated but not a string, otherwise I get an exception saying:
> Exception Message: Conversion failed when converting the nvarchar value '1,2' to data type int.
Code
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})");
Here is what I see in the terminal
> [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, Action1 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)
If I add a Console.WriteLine like this:
Console.WriteLine($"UPDATE dbo.AspNetUsers SET Attended = Attended + 1, EditedDate = CURRENT_TIMESTAMP WHERE Id IN ({ids})");
I see this (bottom line)
英文:
I'm getting an exception because the ids are a string
> ex. '(1,2)'
I need it be comma separated but not a string, otherwise I get an exception saying:
> Exception Message: Conversion failed when converting the nvarchar value '1,2' to data type int.
Code
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})");
Here is what I see in the terminal
> [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, Action1 wrapCloseInAction)
1 wrapCloseInAction)
[2023-08-03T19:29:38.832Z] at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action
[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)
If I add a Console.WriteLine like this:
Console.WriteLine($"UPDATE dbo.AspNetUsers SET Attended = Attended + 1, EditedDate = CURRENT_TIMESTAMP WHERE Id IN ({ids})");
I see this (bottom line)
答案1
得分: 3
ExecuteSql
将使用字符串插值并从中构建参数。
你在这里犯了一个基本错误: SQL参数不是注入到查询中的,它们是绑定到占位符的。在这种情况下,逗号分隔的字符串只是一个字符串,不是数字,因为其中有逗号,它永远不会成为一个数字。
实际上,您的查询变成了WHERE Id IN (@ids)
,带有一个字符串值,与WHERE Id = @ids
相同。所以很明显为什么这不起作用。
你需要的是将它作为单独的值传递。有多种方法可以做到这一点。
- 将整个数组传递进去,希望EF Core可以将其转换(取决于提供程序)。通常,这是通过传递单独的参数
WHERE Id IN (@id0, @id1)
来内部完成的。SQL Server最多有2100个参数,但在那之前性能会受到影响。
var ids = 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})");
- 将值插入表格并与其连接。如果有多个查询同时访问数据库,这可能会变得复杂。
- 最好的选择可能是使用表值参数(TVP)。
首先,在SSMS中创建一个表类型。我通常保留几个标准的一列和两列类型供此目的使用。
CREATE TYPE dbo.IntList (Value int PRIMARY KEY);
然后,创建一个具有DataTable
作为值的SqlParameter
,并将其传递给ExecuteSql
。
var table = new DataTable { Columns = {
{"value", typeof(int)},
} };
foreach (var s in students)
table.Rows.Add(s.Id);
var idParam = new SqlParameter("@ids", table) { TypeName = "dbo.IntList" };
var studentResult = await _dbContext.Database.ExecuteSqlRawAsync(@"
UPDATE dbo.AspNetUsers
SET Attended = Attended + 1,
EditedDate = CURRENT_TIMESTAMP
WHERE Id IN (SELECT Value FROM @ids);
",
idParam);
在EF Core 7.0+中,您还可以尝试使用新的ExecuteUpdate
,通过对其进行组合来尝试这两个选项。
var ids = students.Select(p => p.Id).ToArray();
var studentResult = await _dbContext.Set<Student>.FromSqlRaw(@"
SELECT *
FROM Student
WHERE Id IN ({ids})
"
).ExecuteUpdateAsync(setter => setter
.SetProperty(s => s.Attended, s => s.Attended + 1)
.SetProperty(s => s.EditedDate, s => DateTime.Now)
);
或者,使用TVP,您可以完全组合查询:
var idQuery = _dbContext.Database.SqlQueryRaw<int>(
"SELECT Value FROM @ids",
new SqlParameter("@ids", table) { TypeName = "dbo.IntList" }
); // 这是一个IQueryable,尚未执行任何操作
// 必须在一个独立的步骤中执行此操作,不要合并
var studentResult = await _dbContext.Set<Student>
.Where(s => idQuery.Any(i => i == s.Id))
.ExecuteUpdateAsync(setter => setter
.SetProperty(s => s.Attended, s => s.Attended + 1)
.SetProperty(s => s.EditedDate, s => DateTime.Now)
);
这最后的选项可能是最佳的。
请注意,如果您想要一个多列的TVP,那么您不能使用_dbContext.Database.SqlQuery
。相反,您需要映射一个完整的实体,并使用Set<MyEntity>.FromSqlRaw
。
英文:
ExecuteSql
will take your string interpolation and construct parameters out of it.
You are making a fundamental mistake here: SQL parameters are not injected into the query, they are bound to placeholders. In this case, the comma-separated string is just that: a string, not a number, and it can never be a number because it has commas in it.
Effectively your query has become WHERE Id IN (@ids)
with a single string value, which is the same as WHERE Id = @ids
. So it's obvious why this doesn't work.
What you need is to pass it in as separate values. There are number of ways to do this.
- Pass in the full array and hope that the EF Core can translate this (depends on provider). Usually this is done internally by passing separate parameters
WHERE Id IN (@id0, @id1)
. SQL Server has a maximum of 2100 parameters, but you will hit performance issues long before that.
var ids = 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})");
- Insert the values into a table and join on it. This can get complicated if you have multiple queries hitting the database at the same time.
- The best option is probably 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 you create an SqlParameter
with a DataTable
as the value. And pass that to ExecuteSql
.
var table = new DataTable { Columns = {
{"value", typeof(int)},
} };
foreach (var s in students)
table.Rows.Add(s.Id);
var idParam = new SqlParameter("@ids", table) { TypeName = "dbo.IntList" };
var studentResult = await _dbContext.Database.ExecuteSqlRawAsync(@"
UPDATE dbo.AspNetUsers
SET Attended = Attended + 1,
EditedDate = CURRENT_TIMESTAMP
WHERE Id IN (SELECT Value FROM @ids);
",
idParam);
In EF Core 7.0+, you can also try both of these options using the new ExecuteUpdate
, by composing over them.
var ids = students.Select(p => p.Id).ToArray();
var studentResult = await _dbContext.Set<Student>.FromSqlRaw(@"
SELECT *
FROM Student
WHERE Id IN ({ids})
"
).ExecuteUpdateAsync(setter => setter
.SetProperty(s => s.Attended, s => s.Attended + 1)
.SetProperty(s => s.EditedDate, s => DateTime.Now)
);
Alternatively using a TVP, you can fully compose the query:
var idQuery = _dbContext.Database.SqlQueryRaw<int>(
"SELECT Value FROM @ids",
new SqlParameter("@ids", table) { TypeName = "dbo.IntList" }
); // this is an IQueryable, nothing is executed yet
// must do this in a separate step, do not combine
var studentResult = await _dbContext.Set<Student>
.Where(s => idQuery.Any(i => i == s.Id))
.ExecuteUpdateAsync(setter => setter
.SetProperty(s => s.Attended, s => s.Attended + 1)
.SetProperty(s => s.EditedDate, s => DateTime.Now)
);
This final option is probably the best.
Note that if you want a multi-column TVP then you cannot use _dbContext.Database.SqlQuery
. Instead you need to map a full entity and use Set<MyEntity>.FromSqlRaw
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论