Implementing the Repository Pattern with Multiple Databases in C# and .NET

Using multiple databases along with the repository pattern, can greatly enhance the modularity and maintainability of your codebase, especially when dealing with multiple databases or complex domains. We won’t go into details as how to configure databases and run migrations. This will be more of an architecture guide.

Let’s say we have Books and Movies that we want to store in different databases. Firstly we will create our simple models.

public class Book
{
    public int Id { get; set; }

    public string Name { get; set; }
}

public class Movie
{
    public int Id { get; set; }

    public string Name { get; set; }
}

Then we will create separete DbContexts for each of them

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

    public DbSet<Book> Books { get; set; }
}

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

    }

    public DbSet<Movie> Movies { get; set; }
}

Now as we have our DbContexts we will add connection strings for each of them in appsettings.json file

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "FirstConnectionString": "Data Source=(LocalDB)\\MSSQLLocalDB;Initial Catalog=FirstDb;Integrated Security=True",
    "SecondConnectionString": "Data Source=(LocalDB)\\MSSQLLocalDB;Initial Catalog=SecondDb;Integrated Security=True"
  }
}

Now we’re going to need one more component for taking the responsibility for selecting the specific DbContext. We will call it DbContextProvider and here is how it will look like.

public interface IDbContextProvider
{
    T Get<T>()
        where T : DbContext;
}

public class DbContextProvider : IDbContextProvider
{
    protected IServiceProvider ServiceProvider { get; }

    public DbContextProvider(IServiceProvider serviceProvider)
    {
        ServiceProvider = serviceProvider;
    }

    public T Get<T>() 
        where T : DbContext
    {
        return ServiceProvider.GetRequiredService<T>();
    }
}

Now we’re going to need the implementation of repository pattern

public interface IRepository<TEntity, TContext>
        where TEntity : class
        where TContext : DbContext
    {
        TEntity? GetById(int id);
        IEnumerable<TEntity> GetAll();
        void Add(TEntity entity);
        void Update(TEntity entity);
        void Delete(TEntity entity);

        Task<int> SaveChangesAsync(CancellationToken cancellationToken = default);
    }

public class Repository<TEntity, TContext> : IRepository<TEntity, TContext>
        where TEntity : class
        where TContext : DbContext
    {
        protected TContext Context { get; private set; }

        public Repository(IDbContextProvider dbContextProvider)
        {
            Context = dbContextProvider.Get<TContext>() 
                ?? throw new InvalidOperationException($"DbContext of type {typeof(TEntity).Name} is not registered");
        }

        public TEntity? GetById(int id)
        {
            return Set().Find(id);
        }

        public IEnumerable<TEntity> GetAll()
        {
            return Set().ToList();
        }

        public void Add(TEntity entity)
        {
            Set().Add(entity);
        }

        public void Update(TEntity entity)
        {
            Context.Entry(entity).State = EntityState.Modified;
        }

        public Task<int> SaveChangesAsync(CancellationToken cancellationToken = default)
        {
            return Context.SaveChangesAsync(cancellationToken);
        }

        public void Delete(TEntity entity)
        {
            Set().Remove(entity);
        }

        protected DbSet<TEntity> Set()
        {
            return Context.Set<TEntity>();
        }
    }

After we need to register all the components in our DI container

public class Program
{
    public static void Main(string[] args)
    {
        var builder = WebApplication.CreateBuilder(args);

        builder.Services.AddDbContext<FirstDbContext>(options =>
            options.UseSqlServer(builder.Configuration.GetConnectionString("FirstConnectionString")), ServiceLifetime.Transient);

        builder.Services.AddDbContext<SecondDbContext>(options =>
            options.UseSqlServer(builder.Configuration.GetConnectionString("SecondConnectionString")), ServiceLifetime.Transient);

        builder.Services.AddScoped<IDbContextProvider, DbContextProvider>();
        builder.Services.AddScoped(typeof(IRepository<,>), typeof(Repository<,>));

        ...
    }
}

Now that we have everything ready here is a simple example of how we can use it in a controller

[ApiController]
[Route("[controller]/[action]")]
public class SampleController : ControllerBase
{
    protected IRepository<Book, FirstDbContext> BookRepository { get; }

    protected IRepository<Movie, SecondDbContext> MovieRepository { get; }

    public SampleController(IRepository<Book, FirstDbContext> bookRepository, IRepository<Movie, SecondDbContext> movieRepository)
    {
        BookRepository = bookRepository;
        MovieRepository = movieRepository;
    }

    [HttpGet(Name = "get-books")]
    public async Task<IEnumerable<Book>> GetBooks()
    {
        var book = new Book
        {
            Name = "Book " + Guid.NewGuid()
        };

        BookRepository.Add(book);

        await BookRepository.SaveChangesAsync();

        return BookRepository.GetAll();
    }

    [HttpGet(Name = "get-movies")]
    public async Task<IEnumerable<Movie>> GetMovies()
    {
        var movie = new Movie
        {
            Name = "Movie " + Guid.NewGuid()
        };

        MovieRepository.Add(movie);

        await MovieRepository.SaveChangesAsync();

        return MovieRepository.GetAll();
    }
}

Hope this post will help you to implement your multiple DbContext architecture. Here is the link to project in github.