Can I execute from one DbContextFactory, two DbContext, each with their own query

Question

I am not talking about multiple different defined context's here. I have a single defined DbContext.

When I need to access my database, I have an IDbContextFactory<MyDataDbContext> that I use to create a MyDataDbContext. I then use that DbContext to access the database.

I also know that the instantiated DbContext can only handle one query at a time. It is not thread safe. So I am not asking about using a single instantiated DbContext in two tasks.

And this is for the DbContext set to have tracking off.

My question is, from that single IDbContextFactory, can I:

1. Create two (or more) DbContext objects.
2. Call dbContext.TableName.ToListAsync() on both.
3. Save the Task returns from each in listTasks.
4. Call await Task.WaitAll(listTasks);

And if I do all that, is there a win? Will Azure SQL Database be any faster? Or does it just put all requests in a queue and start/complete one before starting the next? Fundamentally there's a disk drive under all this and so the disk access does have to be queued. But there is all the processing before/after the disk I/O. And there's the Entity Framework before/after processing.

Or am I just getting myself in trouble if I go down this road?

My production system is a vanilla Azure web server and Azure SQL Database.

Answer 1

Parallelizing tasks with separate DbContext instances is fine, provided those tasks can run completely independently. However you still need to consider details like locking in the database. Where you have code-based processing time, parallelization can help but if the bulk of time is spent fetching/updating data you are likely best looking first at methods to do that as efficiently as possible. EF isn't the best option for batch-type operations vs. executing SQL statements if there is that as an option. First look at how you might do it without EF, then consider if EF improves anything.

As noted in the above comments, an additional option is to use the ExecuteUpdate() method available in EF Core 7+. However, note that this method only executes against the database and will not update tracked instances that might already be loaded. For example:

var record = context.MyTables.Single(x => x.Id == tableId);
// ...
context.MyTables
    .Where(x => x.CategoryId == categoryId)
    .ExecuteUpdate(x => x.SetProperty(t => t.Value, t.Value + 1);
var records = context.MyTables
    .Where(x => x.CategoryId == categoryId)
    .ToList();

if the first read record belonged to the category in question, the records loaded at the end would still have the un-altered value from the originally tracked instance. The database row for this entity will have been updated, but EF does not apply the changes to tracked instances in memory. If using an ExecuteUpdate from an injected DbContext instance then you may need to be wary of any tracked references. This could involve calling context.ChangeTracker.Clear() prior to any ExecuteUpdate() call, or ensuring any reads afterwards use AsNoTracking() to ensure the current data state comes from the database.