CODEWITHSUNDEEP
c#arraysparametersentity-framework-coretable-valued-parameters
chuckd
chuckd

Reputation: 14610

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)

enter image description here

Upvotes: 3

Views: 670

Answers (1)

Charlieface
Charlieface

Reputation: 72229

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

Upvotes: 4

Related Questions