Running multiple queries at the same time in EF Core
Overcoming the limitation of EF Core query execution
Since the time of ADO in .NET one of the limitations was that one connection can execute single command at the time. Same limitation Entity Framework inherited and you can execute single command at the time per instance of your DbContext.
Typical dependency injection lifetime of DbContext is scoped, which means you can execute one command at the time as the DbContext is instantiated per HTTP request in ASP.NET Core application.
As a sample for this article I used same database I used in my previous article Accessing multiple databases from the same DbContext in EF Core which has only one database with list of planets in the Solar system.
To keep the project simple, I did not include code first database migrations and seeding
Seeding and migrations are not included in the project for simplicity, but if you want to include migrations and data seeding I suggest you have a look at articles Seeding data with Entity Framework Core using migrations and Seeding data in EF Core using SQL scripts.
The most common scenario where you might need to execute the query more than once is loop. Therefore I will use a sample that executes one stored procedure multiple times. As an example I used simple stored procedure to select planets from the Planets table with like on Name column. To simulate long running I used WAITFOR DELAY T-SQL command.
CREATE OR ALTER PROCEDURE sp__GetPlanetsByName
(
@term varchar(20)
)
AS
BEGIN
WAITFOR DELAY '00:00:02'
SELECT [Id],[Name] FROM dbo.Planets WHERE [Name] LIKE @term + '%'
END
GO
To keep things simple I will skip the Swagger setup and DbContext setup in the Startup.cs class. You can get the whole solution from GitHub public repository https://github.com/dejanstojanovic/efcore-multiple-queries.
Running queries in sequential fashion
I will jump directly to controller code where the stored procedure above is invoked for multiple parameters. Initial version is implemented as v1 API version and it invokes stored procedure in traditional way in while loop.
namespace MultiQuery.Sample.Api.Controllers.v1
{
[ApiVersion("1.0")]
[ApiController]
[Route("[controller]")]
[Route("v{version:apiVersion}/[controller]")]
public class PlanetsController : ControllerBase
{
readonly PlanetsContext _planetsContext;
public PlanetsController(PlanetsContext planetsContext)
{
_planetsContext = planetsContext;
}
[HttpGet]
public async Task<IEnumerable<Planet>> Get([FromQuery] String[] terms)
{
var watch = new System.Diagnostics.Stopwatch();
watch.Start();
IEnumerable<Planet> result = new List<Planet>();
foreach(var term in terms)
{
IEnumerable<Planet> planets = await _planetsContext.Planets.FromSqlInterpolated($"sp__GetPlanetsByName {term}").ToArrayAsync();
result = result.Concat(planets);
}
watch.Stop();
Console.WriteLine($"Execution time:{watch.ElapsedMilliseconds} milliseconds");
return result;
}
}
}
When we run out project and navigate in browser to https://localhost:5001/swagger/ we'll have Swagger UI rendered out the browser
If we check the console of the application we will see the execution time we measured. Since we have delay of 2 seconds, multiplied by 3 parameters we get the execution time of roughly 6 seconds.
I also fired up SQL Server profiler to see how the execution will ectually happend on the local SQL Server itself. You can notice that SPID (Server Process ID) is the same.
Running multiple queries at the same time
Now following analogy that only single DbContext instance can run one SQL command, we'll organize our code in controller v2 in a way to create instance of DbContext for each command and therefore execute each one in a separate task.
This can simply be done using IServiceScopeFactory injected instance in the constructor. ASP.NET Core configures injection of IServiceScopeFactory instance under the hood so we do not need to do anything special in Startup.cs. With injected instance of IServiceScopeFactory we will create a new scope which can resolve instance of DbContext.
namespace MultiQuery.Sample.Api.Controllers.v2
{
[ApiVersion("2.0")]
[ApiController]
[Route("[controller]")]
[Route("v{version:apiVersion}/[controller]")]
public class PlanetsController : ControllerBase
{
readonly IServiceScopeFactory _serviceScopeFactory;
public PlanetsController(IServiceScopeFactory serviceScopeFactory)
{
_serviceScopeFactory = serviceScopeFactory;
}
[HttpGet]
public async Task<IEnumerable<Planet>> Get([FromQuery] String[] terms)
{
object locker = new object();
var watch = new System.Diagnostics.Stopwatch();
watch.Start();
IEnumerable<Planet> result = new List<Planet>();
var tasks = terms.Select(async term =>
{
using (var scope = _serviceScopeFactory.CreateScope())
{
var planetsContext = scope.ServiceProvider.GetRequiredService<PlanetsContext>();
IEnumerable<Planet> planets = await planetsContext.Planets.FromSqlInterpolated($"sp__GetPlanetsByName {term}").ToArrayAsync();
lock (locker)
result = result.Concat(planets);
}
});
await Task.WhenAll(tasks);
watch.Stop();
Console.WriteLine($"Execution time:{watch.ElapsedMilliseconds} milliseconds");
return result;
}
}
}
Since we project our parameters to multiple tasks, each will have it's own scope which can resolve it's own DbConext instance.
As for the result, we concatenate results of each query except that we need to use locking in order to synchronize all tasks running at the same time. If we check the console, we'll see the execution time is equal to time of one stored procedure call which is roughly only 2 seconds.
Comparing it to previous console output we can see that time for getting the same data from the same stored procedure is reduced significantly as queries are executing in their own tasks with it's own scopes.
As for the SQL Server profiler the trace of this way of executing the query looks as following
In comparison to sequential execution, you can see that SPID is different as we are initiating multiple connections with each DbContext instance.
References
Disclaimer
Purpose of the code contained in snippets or available for download in this article is solely for learning and demo purposes. Author will not be held responsible for any failure or damages caused due to any other usage.
About the author
DEJAN STOJANOVICDejan is a passionate Software Architect/Developer. He is highly experienced in .NET programming platform including ASP.NET MVC and WebApi. He likes working on new technologies and exciting challenging projects
Comments for this article