Introduction

Within .NET core, when using Entity Framework, we usually register a DbContext as a service and let the instance itself create and manage its database connection.

When an operation requires two or more contexts, each of them owns and manages its own connection, even if these connections use the same connection string to connect to the same database.

Now imagine an operation to register an order. It uses an OrderContext to create and store a new order. But it may also have to use a StockContext to update (lower) the stock of the ordered products. Both actions are part of a single transaction: they should both succeed or fail.

But as said before, each context manages its own connection. And therefore, we can create a transaction on each of the connections, but we cannot create a single transaction spanning over both contexts (unless we use distributed transactions, which we won’t).

Ultimately, there will always be a possibility that one transaction gets committed and the other doesn’t (deadlock, connection loss, bad code, concurrence issue,...).

We will not discuss the quality of the design having two contexts involved in an operation. Let’s rather find a practical solution to the problem.

The Testcase

First, let's create a test case: we will create two tables containing counts: OrderCounter and StockCounter. Both tables have an Id and Value column and a row with Id 1. Using two separate contexts, whenever we increase the value of one counter (in OrderCounter), we’ll decrease the value of the other counter (in StockCounter).

Here is the code to create the database in SQL Server:

CREATE DATABASE [TransDemo]
GO

USE [TransDemo]
GO

CREATE SCHEMA [order]
GO

CREATE TABLE [order].[OrderCounter]
(
    [Id] int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Value] INT
)
GO

INSERT INTO [order].[OrderCounter] ([Value]) VALUES (0)
GO

CREATE SCHEMA [stock]
GO

CREATE TABLE [stock].[StockCounter]
(
    [Id] int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Value] INT
)
GO

INSERT INTO [stock].[StockCounter] ([Value]) VALUES (100)
GO

During testing, we can always restore the initial situation with the following script:

UPDATE [order].[OrderCounter] SET [Value] = 0 WHERE [Id] = 1

UPDATE [stock].[StockCounter] SET [Value] = 100 WHERE [Id] = 1

So far for the database.

For our test case, we will create a .NET 6 "ASP.NET Core Web API" project. Make sure “Enable OpenAPI support” is checked. This will give us a Swagger UI which we can use to test our API.

We have now two contexts to create. One for managing the order counter:

using Microsoft.EntityFrameworkCore;
using System.ComponentModel.DataAnnotations.Schema;

namespace MyWebApi
{
    public class OrderContext : DbContext
    {
        public OrderContext(DbContextOptions<OrderContext> options)
            : base(options)
        { }

        public DbSet<OrderCounter> OrderCounters { get; set; }
    }

    [Table(nameof(OrderCounter), Schema = "order")]
    public class OrderCounter
    {
        public int Id { get; set; }

        public int Value { get; set; }
    }
}

And one, identical apart from the namings, to manage the stock counter:

using Microsoft.EntityFrameworkCore;
using System.ComponentModel.DataAnnotations.Schema;

namespace MyWebApi
{
    public class StockContext : DbContext
    {
        public StockContext(DbContextOptions<StockContext> options)
            : base(options)
        { }

        public DbSet<StockCounter> StockCounters { get; set; }
    }

    [Table(nameof(StockCounter), Schema = "stock")]
    public class StockCounter
    {
        public int Id { get; set; }

        public int Value { get; set; }
    }
}

Since we have created the database from SQL scripts, we do not need to create migrations.

We do however still have to register or contexts as services and define the connection string. In appsettings.json, we’ll define a “DefaultConnection” connection string:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=(local);Database=TransDemo;
     Trusted_Connection=True;MultipleActiveResultSets=true;Encrypt=False;"
  },
  "AllowedHosts": "*"
}

Next, we register our context services in Program.cs (Startup.cs if we use the pre .NET 6 project setup). We add the following code before the call to builder.Build():

var connectionString = builder.Configuration
    .GetConnectionString("DefaultConnection");

builder.Services.AddDbContext<OrderContext>(
    options => options.UseSqlServer(connectionString));

builder.Services.AddDbContext<StockContext>(
    options => options.UseSqlServer(connectionString));

Rest us one more thing: create the operation to increase the order counter and decrease the stock counter. Since we are in a Web API project, we will create a Web API controller with a Register action:

using Microsoft.AspNetCore.Mvc;

namespace MyWebApi.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class OrderingController : ControllerBase
    {
        private readonly OrderContext orderCtx;
        private readonly StockContext stockCtx;

        public OrderingController(OrderContext orderCtx, StockContext stockCtx)
        {
            this.orderCtx = orderCtx;
            this.stockCtx = stockCtx;
        }

        [HttpGet(Name = "Register")]
        public int[] Get(int quantity = 1)
        {
            try
            {
                orderCtx.OrderCounters.Find(1)!.Value += quantity;
                stockCtx.StockCounters.Find(1)!.Value -= quantity;

                orderCtx.SaveChanges();
                if (quantity >= 10)
                    throw new ApplicationException("Something went wrong.");
                stockCtx.SaveChanges();
            }
            catch (Exception)
            { }

            return new int[] {
                orderCtx.OrderCounters.Find(1)!.Value,
                stockCtx.StockCounters.Find(1)!.Value
            };
        }
    }
}

Notice how the controller gets both database contexts injected.

In the Register action, we’ll increase the order counter and decrease the stock counter with the same amount.

We then save the changes of the order context and of the stock context. But in between both saves, for testing, we make the code fail if the quantity is 10 or more.

This failure is further ignored and we return the new value of both counters.

When we run it, we get a Swagger UI interface we can use to call our Web API. Expand “/api/Ordering”, press the “Try it out” button and then the “Execute” button.

After a first run with quantity 1, you should get the array [1, 99] returned. Both add up to 100 and so our operation kept state consistent.

Now try again, but with quantity = 10. You get [11, 89] which also looks consistent. But query the database and you will see this is not correct:

SELECT * FROM [order].[OrderCounter] WHERE [Id]=1
SELECT * FROM [stock].[StockCounter] WHERE [Id]=1

The database shows 11 and 99 for both counters respectively. .NET got fooled by the fact that one SaveChanges was not executed while basically, in memory, the counter was updated. But when you do a next invocation of the service, with quantity = 0 for instance, you will see that database is right.

So it is possible to update one counter without the other, and that is a problem, the problem we want to solve...

The Solution

The solution is simple: let both contexts share a same database connection and let a transaction surround the whole operation. But how to do that?

Sharing a Connection

Well first of all, database contexts should not manage their own connections anymore. And therefore, there should be a kind of a shared connections manager. I’ve called it an SqlConnectionSource. And here is the code:

using Microsoft.Extensions.Configuration;
using System.Data.SqlClient;

namespace MyWabApi
{
    public class SqlConnectionSource : IDisposable
    {
        private readonly IConfiguration configuration;
        private readonly Dictionary<string, SqlConnection> connections = new();

        public SqlConnectionSource(IConfiguration configuration)
        {
            this.configuration = configuration;
        }

        public SqlConnection this[string name]
        {
            get
            {
                if (!connections.TryGetValue(name, out SqlConnection? conn))
                {
                    var cs = this.configuration.GetConnectionString(name);
                    return connections[name] = new SqlConnection(cs);
                }
                else
                {
                    return conn;
                }
            }
        }

        public virtual void Dispose()
        {
            GC.SuppressFinalize(this);
            foreach (var connection in connections.Values)
                connection.Dispose();
        }
    }
}

It contains a dictionary of SqlConnections and whenever a connection is requested to it, it either returns an existing one from its dictionary, or creates a new one, making sure there is always only one connection per connection string name.

When disposed, all connections of the dictionary are disposed as well.

This makes this class usable as a scoped service within our Web API or web applications.

With the SqlConnectionSource, we can ensure that only one connection for the connection string named “DefaultConnection” exists. And we can do that within the scope of a Web API call.

Nice! But how do we use that SqlConnectionSource ?

Well, let’s create an extension method to register a DbContext that will use the SqlConnectionSource:

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using System;

namespace MyApp
{
    public static class SqlExtensions
    {
        public static void AddScopedSqlDbContext<TDbContext>
        (this IServiceCollection services, 
         string connectionName = "DefaultConnection", 
         Action<DbContextOptionsBuilder<TDbContext>> optionsAction = null)
            where TDbContext : DbContext
        {
            services.AddScoped<DbContextOptions<TDbContext>>(serviceProvider =>
            {
                var builder = new DbContextOptionsBuilder<TDbContext>();
                var source = serviceProvider.GetRequiredService<SqlConnectionSource>();
                var connection = source[connectionName];
                builder.UseSqlServer(connection);
                optionsAction?.Invoke(builder);

                return builder
                    .Options;
            });

            services.AddScoped<TDbContext>();
        }
    }
}

It is rather complex code, but it comes down to this: when the AddScopedSqlDbContext extension method is called, it registers the given DbContext class as a scoped service (last line of the code).

A DbContext expects a DbContextOptions<T> to be injected in its constructor. So the other code is to configure and also register such DbContextOptions within the same scope, and such that it uses the SqlConnectionSource service to get the SqlConnection to use.

We can now replace the registration of our database contexts in the Program.cs file by the following code:

builder.Services.AddScoped<SqlConnectionSource>();
builder.Services.AddScopedSqlDbContext<OrderContext>("DefaultConnection");
builder.Services.AddScopedSqlDbContext<StockContext>("DefaultConnection");

This registers SqlConnectionSource as a scoped service. And also registers the OrderContext and StockContext to use the connection string named “DefaultConnection” (you can actually leave out the connection name parameter as “DefaultConnection” is the default value).

Don’t forget to remove the old database context registration code that uses the AddDbContext extension method.

We can now run our test case again. The result should however be the same. And that is normal. We miss one more step: creating a shared transaction!

Sharing a Transaction

It is less straightforward than it may seem, but this is the rewritten Register action with shared transaction:

        [HttpGet(Name = "Register")]
        public int[] Get(int quantity = 1)
        {
            var transaction = orderCtx.Database.BeginTransaction();
            stockCtx.Database.UseTransaction(transaction.GetDbTransaction());

            try
            {
                orderCtx.OrderCounters.Find(1)!.Value += quantity;
                stockCtx.StockCounters.Find(1)!.Value -= quantity;

                orderCtx.SaveChanges();
                if (quantity >= 10)
                    throw new ApplicationException("Something went wrong.");
                stockCtx.SaveChanges();

                transaction.Commit();
            }
            catch (Exception)
            {
                transaction.Rollback();
            }

            return new int[] {
                orderCtx.OrderCounters.Find(1)!.Value,
                stockCtx.StockCounters.Find(1)!.Value
            };
        }

First, we begin a transaction on the order context.

Then, we’ll tell the stock context to use that same transaction.

And the end of the try block, we commit the transaction, while in the catch block, we rollback the transaction.

On failure, the action still reports wrong values in the returned array, but that is because the transaction only operates on the database and not in the application memory.

But on database: either the operation succeeds (the transaction is committed) and both counters are consistently updated, or the operation fails (the transaction is rolled back) and no counter is updated.

Here you are!

History

26^th November, 2022: Initial version

EF Core Contexts Sharing Transaction