How to change between databases with the same schema but different names using EF and dependency injection?

Question

I have a Web API web service that uses EF for database operations and Unity for dependency injection. I have multiple databases with different names but the same schema. There's one database per retail store. When the user logs in, depending on his privileges, he can select which store he wants to work with. This is a challenge using dependency injection because I have to change the database after a repository has been injected. I have something that works but am not sure if it's the best approach.

My specific questions are:

Is this a good approach for this problem? I've seen other questions that mention changing the connection string at run time, but I think I'd either have to have a connection string per store in my Web.Config or build the connection string dynamically somehow.
Do I need the Dispose logic in my factory? If I were injecting a repository directly I know I wouldn't need it. Since I'm generating the repo from an injected factory, can I trust Unity to dispose of the repo and close the db connections at some point? Should I use using statements around the generated repos instead?

Some questions I looked at while trying to solve this problem are this one, this one, and this one. However, none of them directly accomplish what I'm trying to do. Below is my current solution.

This is my repository and its interface. I've left out some of the methods for brevity:

IGenericRepository

public interface IGenericRepository<T> where T: class
{
    IQueryable<T> Get();
    void ChangeDatabase(string database);
    void Update(T entityToUpdate);
    void Save();
}

GenericRepository

public class GenericRepository<TDbSet, TDbContext> : 
    IGenericRepository<TDbSet> where TDbSet : class
    where TDbContext : DbContext, new()
{
    internal DbContext Context;
    internal DbSet<TDbSet> DbSet;
    public GenericRepository() : this(new TDbContext())
    {
    }

    public GenericRepository(TDbContext context)
    {
        Context = context;
        DbSet = Context.Set<TDbSet>();
    }

    public virtual IQueryable<TDbSet> Get()
    {
        return DbSet;
    }       

    public void ChangeDatabase(string database)
    {
        var dbConnection = Context.Database.Connection;

        if (database == null || dbConnection.Database == database)
            return;

        if (dbConnection.State == ConnectionState.Closed)
        {
            dbConnection.Open();
        }

        Context.Database.Connection.ChangeDatabase(database);
    }

    public virtual void Update(TDbSet entityToUpdate)
    {
        DbSet.Attach(entityToUpdate);
        Context.Entry(entityToUpdate).State = EntityState.Modified;
    }

    public virtual void Save()
    {
        Context.SaveChanges();
    }
}

In order to use dependency injection I'm injecting a repository factory to which I can pass a database name. The factory creates a repository with the connection string's default database, changes the database to the one specified, and returns the repository.

IRepositoryFactory

public interface IRepositoryFactory     
{
    IGenericRepository<TDbSet> GetRepository<TDbSet>(string dbName) where TDbSet : class;
}

StoreEntitiesFactory

public class StoreEntitiesFactory : IRepositoryFactory
{
    private bool _disposed;
    readonly StoreEntities _context;

    public StoreEntitiesFactory()
    {
        _context = new StoreEntities();
    }

    public IGenericRepository<TDbSet> GetRepository<TDbSet>(string dbName) where TDbSet : class
    {
        var repo = new GenericRepository<TDbSet, StoreEntities>(_context);

        repo.ChangeDatabase(dbName);

        return repo;
    }

    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(this);
    }

    protected virtual void Dispose(bool disposing)
    {
        if (_disposed)
            return;

        if (disposing)
        {
            _context.Dispose();
        }

        _disposed = true;
    }

    ~StoreEntitiesFactory()
    {
        Dispose(false);
    }
}

This is how I inject the repository factory in my WebApiConfig file:

WebApiConfig.cs

public static class WebApiConfig
{
    public static void Register(HttpConfiguration config)
    {
        // Web API configuration and services
        var container = new UnityContainer();       

        container.RegisterType<IRepositoryFactory, StoreEntitiesFactory>(new HierarchicalLifetimeManager());

        config.DependencyResolver = new UnityResolver(container);
    }
}

Finally, this is how I would use the factory in my controller:

StoreController

public class StoreController : ApiController
{
    private readonly IRepositoryFactory _storeEntitiesRepoFactory;

    public StoreController(IRepositoryFactory storeEntitiesRepoFactory)
    {
        _storeEntitiesRepoFactory = storeEntitiesRepoFactory;        
    }

    [HttpGet]
    public IHttpActionResult Get()
    {
        var dbName = getStoreDbName(storeNumberWeGotFromSomewhere);

        try
        {
            var employeeRepo = _storeEntitiesRepoFactory.GetRepository<Employee>(dbName);
            var inventoryRepo = _storeEntitiesRepoFactory.GetRepository<Inventory>(dbName);

            var employees = employeeRepo.Get().ToList();
            var inventory = inventoryRepo.Get().ToList();
        }
        catch (Exception ex)
        {
            return InternalServerError();
        }
    }
}

score 2 · Answer 1 · answered Jun 07 '19 at 18:15

I'd recommend you to use a design pattern called a Strategy Pattern to solve this problem.

This pattern allows you to change between two or more strategies on runtime. Reference: https://en.wikipedia.org/wiki/Strategy_pattern

For the injection, I'd suggest you register two concrete classes on the Unity, one for each DB Connection and call the Resolve method for the one you need passing the string to instantiate the DB.

IUnityContainer container = new UnityContainer();
container.RegisterType<ICar, BMW>();
container.RegisterType<ICar, Audi>("LuxuryCar");

ICar bmw = container.Resolve<ICar>();  // returns the BMW object
ICar audi = container.Resolve<ICar>("LuxuryCar"); // returns the Audi object

Reference: https://www.tutorialsteacher.com/ioc/register-and-resolve-in-unity-container

About the Dispose, you can config all of these concrete classes to the DBs as Singletons, and let all the connections opened, but you will need to verify if this is possible to your application.

Could you elaborate on how the Strategy Pattern would help me? As I understand it, the Strategy Pattern is meant to select between multiple algorithms. What algorithms would I be selecting between here? — Bruno, Jun 07 '19 at 19:07

score 2 · Accepted Answer · answered Jun 07 '19 at 18:15

I think you probably want your IRepositoryFactory implementations to return the same repository for the same dbName. As it is written now, calling StoreEntitesFactory.GetRepository with two different dbName parameters will cause problems since it gives the same instance of StoreEntites to every repository.

To illustrate...

public class DemonstrationController
{
    private readonly IRepositoryFactory _storeEntitiesRepoFactory;

    public DemonstrationController(IRepositoryFactory storeEntitiesRepoFactory)
    {
        _storeEntitiesRepoFactory = storeEntitiesRepoFactory;
    }

    [HttpGet]
    public IHttpActionResult Get()
    {
        var empRepo1 = _storeEntitiesRepoFactory.GetRepository("DB1");
        var empRepo2 = _storeEntitiesRepoFactory.GetRepository("DB2");

        // After the second line, empRepo1 is connected to "DB2" since both repositories are referencing the same
        // instance of StoreEntities
    }
}

If you changed StoreEntitiesFactory to return the same repository based on the given parameter, this would solve that problem.

public class StoreEntitiesFactory : IRepositoryFactory
{
    private bool _disposed;
    private Dictionary<string, StoreEntities> _contextLookup;

    public StoreEntitiesFactory()
    {
        _contextLookup = new Dictionary<string, StoreEntities>();
    }

    public IGenericRepository<TDbSet> GetRepository<TDbSet>(string dbName) where TDbSet : class
    {
        if (!_contextLookup.TryGetValue(dbName, out StoreEntities context))
        {
            context = new StoreEntities();
            // You would set up the database here instead of in the Repository, and you could eliminate
            // the ChangeDatabase function.

            _contextLookup.Add(dbName, context);
        }
        return new GenericRepository<TDbSet, StoreEntities>(context);
    }

    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize();
    }

    protected virtual void Dispose(bool disposing)
    {
        if (!_disposed)
        {
            if (disposing)
            {
                foreach (var context in _contextLookup.Values)
                {
                    context.Dispose();
                }
            }
            _disposed = true;
        }
    }
}

As for the second question, you would need the dispose logic in the factory since it owns the instances of StoreEntities being created. No need to use using statements around the repositories it creates, just let Unity dispose of the factory.

I like the idea of having the factory keep a collection of the repositories a lot. One thing that's not clear to me is in the `StoreEntitiesFactory` when you say "You would set up the database here instead..." , are you saying I should move the code from the `ChangeDatabase` method to the spot you indicated? I would still have to change the db somehow since `new StoreEntities()` would use the default db indicated in the connection string. — Bruno, Jun 07 '19 at 19:02
Yeah, move the code in your `ChangeDatabase` method to where that comment is, then remove the `ChangeDatabase` method from the `IGenericRepository`. I think having that method on the repository itself is risky, because a developer could ask for a repository for "DB1" from the factory, then call `ChangeDatabase` on the repository. At that point, all the repositories that were created from that factory for "DB1" are pointing to the new database. — Joshua Robinson, Jun 07 '19 at 19:35

How to change between databases with the same schema but different names using EF and dependency injection?

2 Answers2