Using Multiple Databases in ASP.NET Core via Entity Framework Core

In this article, we are going to learn how to add multiple databases in the ASP.NET Core project using Entity Framework Core.

We are going to show you how our repository pattern implementation helps us in the process by using abstractions that will hide all the implementation details from the presentation layer. 

Since we already have the repository pattern explained in our ASP.NET Core Repository Pattern article, we strongly suggest reading it first to learn more about the repository pattern implementation in ASP.NET Core Web API. We will modify the source code from that project and show you the benefits of the implementation of that pattern when adding multiple databases in an ASP.NET Core project. 

Support Code Maze on Patreon to get rid of ads and get the best discounts on our products!

Let’s start.

VIDEO: Using Multiple Databases in ASP.NET Core With EF Core.

Using Multiple Databases to Support Migrations

If you open our project that you can download from our source code repository, you can execute initial migrations that will create the first database and populate the initial data:

PM> Update-Database -Context RepositoryContext

Now, let’s say we received a request for another external database that we have to use for the same project. Sometimes, that database would already contain the required data. But if we want to add some data to it, we need to enable migrations for that database as well.

To do that, we are going to start with the new model creation. So, let’s open the Entities project and then the Models folder, and add a new class:

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

We’ll assume that our new database will hold information about external clients. Of course, we are going to use a simple model for this example.

Now, in the Entities/Configuration folder, we are going to populate our client table – once we execute a new migration:

public class ClientConfiguration : IEntityTypeConfiguration<Client>
{
    public void Configure(EntityTypeBuilder<Client> builder)
    {
        builder.HasData
        (
            new Client
            {
                Id = new Guid("c1f33503-bb38-4fa1-98a0-6cfaf9986797"),
                Name = "External Client's Test Name"
            }
        );
    }
}

Next, in the same project, we are going to add a new context class:

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

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.ApplyConfiguration(new ClientConfiguration());
    }

    public DbSet<Client> Clients { get; set; }
}

If we use multiple contexts in our project, we have to provide the TContext parameter for the DbContextOptions class. In this case, the parameter is of ExternalClientContext type. But if you check the RepositoryContext class, you will see that the parameter is of RepositoryContext type.

Registering Contexts for Multiple Databases in ASP.NET Core

If we inspect the appconfig file from the main project, we are going to find multiple connection strings:

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "ConnectionStrings": {
    "sqlConnection": "server=.; database=CompanyEmployeeDb1; Integrated Security=true",
    "externalClientConnection": "server=.; database=CompanyEmployeeDb2; Integrated Security=true"
  },
  "AllowedHosts": "*"
}

After adding the connection string, we can register a new context in the ServiceExtensions class inside the Extensions folder:

public static void ConfigureExternalClientContext(this IServiceCollection services, IConfiguration configuration) =>
            services.AddDbContext<ExternalClientContext>(opts =>
                opts.UseSqlServer(configuration.GetConnectionString("externalClientConnection"), b => b.MigrationsAssembly("CompanyEmployees")));

Lastly, we have to call this method in the Starup class for .NET 5:

services.ConfigureLoggerService();
services.ConfigureSqlContext(Configuration);
services.ConfigureExternalClientContext(Configuration);
services.ConfigureRepositoryManager();

Or in .NET 6 in the Program class:

builder.Services.ConfigureExternalClientContext(Configuration);

That’s it.

We can create a new migration:

PM> Add-Migration ExternalClientData -Context ExternalClientContext

And execute it:

Update-Database -Context ExternalClientContext

Once that’s done, we will have both databases created:

Injecting Multiple Databases in the Repository Pattern

After successful migrations for multiple databases, we can start using both databases in our repository pattern to fetch the data from them.

In the Repository Pattern article, we have used only the RepositoryContext class in our RepositoryBase class (Repository project) thus limiting our repository to a single database:

public abstract class RepositoryBase<T> : IRepositoryBase<T> where T : class
{
    protected RepositoryContext RepositoryContext;

    public RepositoryBase(RepositoryContext repositoryContext) => RepositoryContext = repositoryContext;
...

Now, we can modify that.

So, let’s inspect a new RepositoryBase implementation:

public abstract class RepositoryBase<TContext, T> : IRepositoryBase<T> where T : class where TContext : DbContext
{
    protected TContext _context;

    public RepositoryBase(TContext context) => _context = context;
...

This time, we are using RepositoryBase with two generic parameters. The first one is for the context class, and the second one is for the entity. Additionally, we are restricting our TContext parameter to be only of DbContext type.

To continue, let’s create a new interface in the Contracts project:

public interface IClientRepository
{
    IEnumerable<Client> GetAllClients(bool trackChanges);
}

And, let’s create a repository class in the Repository project, that implements this interface:

public class ClientRepository : RepositoryBase<ExternalClientContext, Client>, IClientRepository
{
    public ClientRepository(ExternalClientContext clientContext)
        :base(clientContext)
    {
    }

    public IEnumerable<Client> GetAllClients(bool trackChanges) => 
        FindAll(trackChanges)
        .ToList();
}

We can see that following the same pattern from the previous implementation, our class inherits from the RepositoryBase class and our new interface. But this time, our RepositoryBase class has two parameters.

Additionally, we can inspect another repository class that uses a different context class:

public class CompanyRepository : RepositoryBase<RepositoryContext, Company>, ICompanyRepository
{
    public CompanyRepository(RepositoryContext repositoryContext)
        : base(repositoryContext)
    {
    }

    public IEnumerable<Company> GetAllCompanies(bool trackChanges) =>
        FindAll(trackChanges)
        .OrderBy(c => c.Name)
        .ToList();
}

Here we use a different context class and of course a different entity.

Supporting Multiple Databases in RepositoryManager (UoW)

Now, we have to modify the IRepositoryManager interface to provide our presentation or service layer (if you have both) with the possibility to call new repository classes:

public interface IRepositoryManager
{
    ICompanyRepository Company { get; }
    IEmployeeRepository Employee { get; }

    IClientRepository Client { get; }

    void Save();
}

And we have to modify the RepositoryManager class:

public class RepositoryManager : IRepositoryManager
{
    private RepositoryContext _repositoryContext;
    private ExternalClientContext _externalClientContext;

    private ICompanyRepository _companyRepository;
    private IEmployeeRepository _employeeRepository;

    private IClientRepository _clientRepository;

    public RepositoryManager(RepositoryContext repositoryContext, ExternalClientContext externalClientContext)
    {
        _repositoryContext = repositoryContext;
        _externalClientContext = externalClientContext;
    }

    ...

    public IClientRepository Client
    {
        get
        {
            if (_clientRepository is null)
                _clientRepository = new ClientRepository(_externalClientContext);

            return _clientRepository;
        }
    }

    public void Save() => _repositoryContext.SaveChanges();
}

Since we don’t have any modification methods in our project, we didn’t modify the Save method to include additional context. But you can always do that and use ChangeTracker.HasChanges() to determine which context has changed:

public void Save()
{
  if (_repositoryContext.ChangeTracker.HasChanges())
    _repositoryContext.SaveChanges();
  else
    _externalClientContext.SaveChanges();
}

Or you can add just both contexts in the Save method and let EF Core do the check for you. It’s up to you.

From the architectural point of view, if you have to modify both databases at the same time and the data from both databases need to be in sync, then it is probably a better solution to use just a single database. But if your second database is not connected to the main one, and modifications are not related to each other, which should be the case most of the time, feel free to continue using multiple databases in the project.

Now we can use this in a controller or a service class – depends on your project. For the sake of simplicity, we’ll create another endpoint in the existing CompaniesController:

[Route("api/companies")]
[ApiController]
public class CompaniesController : ControllerBase
{
    private readonly IRepositoryManager _repository;
    private readonly ILoggerManager _logger;
    private readonly IMapper _mapper;

    public CompaniesController(IRepositoryManager repository, ILoggerManager logger, IMapper mapper)
    {
        _repository = repository;
        _logger = logger;
        _mapper = mapper;
    }

    [HttpGet]
    public IActionResult GetCompanies()
    {
        var companies = _repository.Company.GetAllCompanies(trackChanges: false);

        var companiesDto = _mapper.Map<IEnumerable<CompanyDto>>(companies);

        return Ok(companiesDto);
    }

    [HttpGet("clients")]
    public IActionResult GetTests()
    {
        var tests = _repository.Client.GetAllClients(trackChanges: false);

        return Ok(tests);
    }
}

You can see that we don’t have to modify anything in the presentation layer – we just add a new endpoint and use our repository manager as if nothing has changed in the repository implementation.

Testing Data Fetching From Multiple Databases in ASP.NET Core

To test this, we are going to start our application and send a Postman request for the first database:

Right after that request, we are going to send another one that uses the second database:

We can see that our project works with multiple databases without a single problem.

Conclusion

That’s it.

We’ve seen how to use multiple databases in ASP.NET Core with Entity Framework core. Additionally, we’ve shown the advantage of using our repository pattern implementation. We can hide all this logic inside the repository layer and keep using it in our presentation/service layers as if nothing has changed in the project.

Until the next article,

All the best.

Liked it? Take a second to support Code Maze on Patreon and get the ad free reading experience!