Connect multiple Databases to .NET core project via Entity Framework Core

Question

I am trying to create a .NET core application that connects to multiple databases, but using one generic repository that contains the logic for all CRUD operations. What is the best way to achieve this?

    public Repository(ApplicationDbContext dbContext)
    {
        _dbContext = dbContext;
        _set = _dbContext.Set<T>();
    }

Above is the constructor of my repository. Here, I inject the ApplicationDbContext. I am looking for a way to make this ApplicationDbContext generic, so I only have to need only one repository, in which I can inject different contexts for accessing multiple databases. Essentially I am looking for something like this:

public class Repository_1<T> where T:EntityBase
{
    public Repository_1(IDbContext dbContext)
    {

    }
}

Where I can swap out the dbContext and replace it with another context that connects to another Database.

Solution 1

You could set two parameters for your repository and add constrains on them, asusume that your dbContext is inherited from DbContext

public class TestRepository<TContext, T> : ITestRepository<TContext,T> 
    where TContext : DbContext
    where T : BaseEntity
{

    private readonly TContext _context;
    private DbSet<T> entities;


    public TestRepository(TContext context)
    {
        _context = context;
        entities = context.Set<T>();
    }



    public List<T> GetAll()
    {

        return entities.AsNoTracking().ToList();
    }
}

ITestReposiroty:

public interface ITestRepository<TContext,T>
    where TContext : DbContext
    where T : BaseEntity
{       
    List<T> GetAll();
}

Startup.cs

services.AddScoped(typeof(ITestRepository<,>), typeof(TestRepository<,>));

Controller:

public class ProductsController : ControllerBase
{
    private readonly ITestRepository<ApplicationDbContext, Product> _repository;

    public TestRepoController(ITestRepository<ApplicationDbContext, Product> repository)
    {
        _repository = repository;
    }
    // GET api/products

    [HttpGet]
    public List<Product> Get()
    {
        return _repository.GetAll();
    }
}

Solution 2

Create base context and including all settings into this, DBSET:

public abstract class BaseContext : DbContext
{
    public BaseContext(DbContext options)
    : base(options)
    { }
    public DbSet<object> FirstDbSet { get; set; }
    ...
}

inherit from BaseContext for both DBs(Databases):

public class NavaContext : BaseContext
{
    public NavaContext (DbContext<NavaContext> options) : base(options)
    {

    }
}

public class StackContext : BaseContext
{
    public StackContext(DbContext<StackContext> options) : base(options)
    {

    }
}

and register both in Startup.cs:

public IServiceProvider ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<NavaContext>(options => options.UseSqlServer(Configuration.GetConnectionString("LATAMConnectionString")));
    services.AddDbContext<StackContext>(options => options.UseSqlServer(Configuration.GetConnectionString("EUConnectionString")));

    // Autofac
    var builder = new ContainerBuilder();

    // needed only if you plan to inject ICollection<BaseContext>
    builder.RegisterType<NavaContext>().As<BaseContext>();
    builder.RegisterType<StackContext>().As<BaseContext>();

    builder.Populate(services);


    return new AutofacServiceProvider(builder.Build());
}

add connection strings in appsettings.json:

"ConnectionStrings": {
  "NavaConnectionString": "Server=(localdb)\\mssqllocaldb;Database=ContosoUniversity1;Trusted_Connection=True;MultipleActiveResultSets=true",
  "StackConnectionString": "Server=(localdb)\\mssqllocaldb;Database=ContosoUniversity1;Trusted_Connection=True;MultipleActiveResultSets=true"
}

and now you can inject both contexts:

public class ReportRepository : IReportRepository
{
    private readonly NavaContext latamDbContext;
    private readonly StackContext euDbContext;

    public ReportRepository(NavaContext latamDbContext, StackContext euDbContext)
    {
        this.latamDbContext = latamDbContext;
        this.euDbContext = euDbContext;
    }
}

or if you plan to inject collection of contexts:

public class ReportRepository : IReportRepository
{
    private readonly ICollection<BaseContext> dbContexts;

    public ReportRepository(ICollection<BaseContext> dbContexts)
    {
        this.dbContexts = dbContexts;
    }
}

to access specific context:

var _stackContext= dbContexts.FirstOrDefault(x => x is StackContext) as StackContext;
var _navaContext= dbContexts.FirstOrDefault(x => x is NavaContext) as NavaContext;

Comments

[MichaelM]

So in your scenario you have different databases with identical schemas? Or is each schema unique?

[wserr]

No, each schema is unique.

[MichaelM]

So you essentially want a repo container to hold all your contexts?

[Iuri Farenzena]

You mean something like this: ibb.co/2t9sPqH

[Mojtaba Nava]

Could I help you?

[wserr]

Almost - I actually want to have one repository per type, e.g. a user repository, that I need to connect to the right database (the one with the table User in it). I don't want a collection of contexts in my repository, only one. Is this possible?

[Mojtaba Nava]

Yes of course. Choose an optimal repository method and you can define two databases.

[wserr]

What do you mean by that?