'C# Entityframework core error executing raw sqlstring with more parameter values
I am trying to build a SqlQuery at runtime and trying to execute the _context.Database.ExecuteSqlCommandAsync() but getting the following error. There are around 620 rows to be inserted with total number of parameter values around 4400. Not sure how to handle this. Can anyone help me how to solve this.
code:
var sqlSb = new StringBuilder();
sqlSb.AppendLine("INSERT INTO [dbo].[UserTag](UserId, TagId, Disabled, Created, CreatedBy, Modified, ModifiedBy)");
sqlSb.AppendLine("VALUES");
var index = 0;
var noOfcolumnsToInsert = 7;
var query = new SqlQuery();
foreach (var userTag in userTags)
{
var countFrom = index * noOfcolumnsToInsert;
index++;
sqlSb.AppendLine($"({{{countFrom}}}, {{{countFrom + 1}}}, {{{countFrom + 2}}}, {{{countFrom + 3}}}, {{{countFrom + 4}}}, {{{countFrom + 5}}}, {{{countFrom + 6}}}){(index < userTags.Count ? "," : "")}");
query.ParameterValues.AddRange(new List<object> { userTag.UserId, userTag.TagId, userTag.Disabled, currentDateTime, sessionUserGuidAsString, currentDateTime, sessionUserGuidAsString });
}
query.Sql = sqlSb.ToString();
await _context.Database.ExecuteSqlCommandAsync(query.Sql, query.ParameterValues.ToArray());
SqlQuery class:
public class SqlQuery
{
public string Sql { get; set; }
public List<object> ParameterValues { get; set; }
public SqlQuery()
{
ParameterValues = new List<object>();
}
}
Error:
The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
