Multithreaded queries with EF Core DbContext

Here are some quick tips on how to do efficient querying with Entity Framework Core and the DbContext.

The basics

This is a standard context implementation:

public class MyDbContext: DbContext
{
    public MyDbContext(DbContextOptions<MyDbContext> options)
            : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Order>().ToTable("Orders");
    }
}

The code above is your boiler plate pattern of mapping database tables to your entity framework context class. Obviously I’ve left out a bunch of stuff, but you get the picture.

Ordinary usage

Typically various dependencies in your .NET Core app are registered using a DI container. That also means that you resolve the same scoped instance in the code (that obviously depends on how you register it in your DI container).

Preparing for parallellism

What I typically do to clearly indicate in the code when you can parallellize is to define a factory method on the context like this:

public class MyDbContext: DbContext
{
    public MyDbContext(DbContextOptions<MyDbContext> options)
            : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Order>().ToTable("Orders");
    }

    // Factory method added
    public static MyDbContext Create(string connStr)
    {
        return new MyDbContext(new DbContextOptionsBuilder<MyDbContext>().UseSqlServer(connStr).Options);
    }
}

How to use it

A typical example would be something like this:

public Task<List<QuarterlyReport>> GetReports(int year)
{
    Task<QuarterlyReport> GetReport(int quarter)
    {
        // Using a local function here
        using (var context = MyDbContext.Create(connStr))
        {
            return await context.GetQuarterlyReport(quarter);
        }

        var reportTasks = Enumerable.Range(1,4).Select(q => GetReport(q));
        // Execute all 4 in parallel
        await Tasks.WhenAll(reportTasks);
        // And finally, generate the list.
        return reportTasks.Select(x => x.Result).ToList();
    }
}

The result

Now that there’s a factory method to use, I believe the intent is clear. This means you can simply call MyDbContext.Create() in every task/thread where you need to execute database queries.

Comments

Creating a factory method provides clear intent as I see it, which is important. Obviously you can modify how the context is registered in your DI container also so that it is registered as transient. Then all you need to do is to Resolve<T>() it several times to get a different instance. As always, there are several ways to do things, you choose what you’re most comfortable with.