将逗号分隔的整数字符串转换为逗号分隔的整数以供 ExecuteSql 命令使用。

huangapple go评论109阅读模式
英文:

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, Action1 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)

将逗号分隔的整数字符串转换为逗号分隔的整数以供 ExecuteSql 命令使用。

英文:

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)

将逗号分隔的整数字符串转换为逗号分隔的整数以供 ExecuteSql 命令使用。

答案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 =&gt; p.Id).ToArray();
var studentResult = await _dbContext.Database.ExecuteSqlAsync(@$&quot;
UPDATE dbo.AspNetUsers
SET Attended = Attended + 1,
    EditedDate = CURRENT_TIMESTAMP
WHERE Id IN ({ids})&quot;);
  • 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 = {
    {&quot;value&quot;, typeof(int)},
} };

foreach (var s in students)
    table.Rows.Add(s.Id);

var idParam = new SqlParameter(&quot;@ids&quot;, table) { TypeName = &quot;dbo.IntList&quot; };

var studentResult = await _dbContext.Database.ExecuteSqlRawAsync(@&quot;
UPDATE dbo.AspNetUsers
SET Attended = Attended + 1,
    EditedDate = CURRENT_TIMESTAMP
WHERE Id IN (SELECT Value FROM @ids);
&quot;,
    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 =&gt; p.Id).ToArray();
var studentResult = await _dbContext.Set&lt;Student&gt;.FromSqlRaw(@&quot;
SELECT *
FROM Student
WHERE Id IN ({ids})
&quot;
    ).ExecuteUpdateAsync(setter =&gt; setter
        .SetProperty(s =&gt; s.Attended, s =&gt; s.Attended + 1)
        .SetProperty(s =&gt; s.EditedDate, s =&gt; DateTime.Now)
    );

Alternatively using a TVP, you can fully compose the query:

var idQuery = _dbContext.Database.SqlQueryRaw&lt;int&gt;(
    &quot;SELECT Value FROM @ids&quot;,
    new SqlParameter(&quot;@ids&quot;, table) { TypeName = &quot;dbo.IntList&quot; }
);  // this is an IQueryable, nothing is executed yet

// must do this in a separate step, do not combine
var studentResult = await _dbContext.Set&lt;Student&gt;
    .Where(s =&gt; idQuery.Any(i =&gt; i == s.Id))
    .ExecuteUpdateAsync(setter =&gt; setter
        .SetProperty(s =&gt; s.Attended, s =&gt; s.Attended + 1)
        .SetProperty(s =&gt; s.EditedDate, s =&gt; 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&lt;MyEntity&gt;.FromSqlRaw

huangapple
  • 本文由 发表于 2023年8月4日 03:14:59
  • 转载请务必保留本文链接:https://go.coder-hub.com/76831019.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定