Execute a command multiple times using EF Core

userfiIU 0
Viewed 96

In Dapper we have execute a command multiple times title that allow us to run query many time with different values. Is this possible in EF Core using ExecuteSqlRawAsync function as well?

Something like this:

_context.Database.ExecuteSqlRawAsync(myStringQuery, myParameters)

and this code should translate following query:

INSERT INTO MyTable (MyColumn)
VALUES (@p0, @p2)

Currently this method does work, but only adding first index of my list (first parameter only) and generate below query:

INSERT INTO MyTable (MyColumn)
VALUES (@p0)

My string query look like:

StringBuilder myStringQuery = new();
sb.AppendLine("query ... VALUES ({0})")

I have to solve problem in this way for now

StringBuilder sb = new();
sb.AppendLine("INSERT INTO \"Blogs\"");
sb.AppendLine($"(\"{nameof(Blog.Url)}\")");
sb.AppendLine("VALUES");
int i;

for (i = 0; i < obj.Length - 1; i++)
{
    sb.AppendFormat("({{{0}}}),", i);
}

sb.AppendFormat("({{{0}}})", i++);
await _context.Database.ExecuteSqlRawAsync(sb.ToString(), obj.Select(x => x.Url!));

but I want to solve this like in the Dapper version seen in this link

ef-core-3.1 entity-framework-core c#
2 Answers

EF-Core supports Commands Batching:

optionsBuilder.UseSqlServer(
   @"Server=(localdb)\mssqllocaldb;Database=Demo.Batching;Trusted_Connection=True;",
   options => options.MaxBatchSize(1)
);

Its default value is 1000 which means it sends a batch of 1000 insert/update/deletes at once. Each batch should have 2100 parameters. More than that will be sent in a different batch.

VahidN0

You will have to do it manually.

Let's say there is an entity:

public class Test
{
    public Guid Id { get; set; }
    public string Name { get; set; }
}

There is a collection of parameters:

var myParameters = Enumerable.Range(1, 3)
    .Select(x => new object[] { Guid.NewGuid(), "name" + x });

Go through this collection in a loop and execute a query to the database:

foreach (var item in myParameters)
    context.Database.ExecuteSqlRaw("insert into Tests values ({0},{1})", item);
Alexander Petrov0
Related