Entity Framework MVC and multiple databases

Question

I've put together an MVC application using a repository pattern with Entity Framework and everything is going smoothly - but I've run into a stopping block and I'm not sure how to proceed.

I have a few dozen databases with the same schema, and I want to be able to choose one or many at runtime. For example, let's say I start with a database of users (not made yet). That user has connection string information associated with them (possibly more than one). Once the user has "logged in", I want the Enumerables I feed to my Views to contain matching data from all of the databases that user has access to.

Here's an example of what I have right now:

Entity:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.ComponentModel.DataAnnotations.Schema;

namespace Dashboard.Domain.Entities
{
    public class Flight
    {
        public Guid Id { get; set; }
        public string CarrierCode { get; set; }
        public string FlightNo { get; set; }
        public string MarketingCarrierCode { get; set; }
        public string MarketingFlightNo { get; set; }
        public string Type { get; set; }
        public string TailNo { get; set; }
        public string OriginIATA { get; set; }
        ...

    }
}

DB Context:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.Entity;
using Dashboard.Domain.Entities;

namespace Dashboard.Domain.Concrete
{
    public class EFDbContext : DbContext
    {
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Passenger>().ToTable("PAX");
        }
        public DbSet<Flight> Flights { get; set; }
        public DbSet<Passenger> PAX { get; set; }
        public DbSet<Airport> Airports { get; set; }
    }
}

Flight repository interface:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dashboard.Domain.Entities;

namespace Dashboard.Domain.Abstract
{
    public interface IFlightRepository
    {
        IQueryable<Flight> Flights { get; }
    }
}

EF Flight Repository:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dashboard.Domain.Abstract;
using Dashboard.Domain.Entities;

namespace Dashboard.Domain.Concrete
{
    public class EFFlightRepository : IFlightRepository
    {
        private EFDbContext context = new EFDbContext();

        public IQueryable<Flight> Flights
        {
            get { return context.Flights; }
        }
    }
}

Controller:

 public class FlightController : Controller
    {
        private IFlightRepository fRepository;
        private IPaxRepository pRepository;
        private IAirportRepository aRepository;
        public int PageSize = 10;

        public FlightController(IFlightRepository flightRepository, IPaxRepository paxRepository, IAirportRepository airportRepository)
        {
            this.fRepository = flightRepository;
            this.pRepository = paxRepository;
            this.aRepository = airportRepository;
        }

        public ViewResult List(byte status = 1, int page = 1)
        { ...

I want those repositories to contain all of the data from all of the connection strings specified, but I have no idea where to start. EF is getting my connection string from the web.config, but I need to be able to set it dynamically somehow and I need to put more than one database's data into the repository.

Is this possible? I should mention that the site is READ ONLY, so I won't need to write changes back to the DBs.

UPDATE:

I've changed the code so I can pass a connection string to the constructor of my EF Repository, but when I try to merge the IQueryables from two different contexts, as below:

public class EFFlightRepository : IFlightRepository
{
    private EFDbContext context1 = new EFDbContext(connectionstring1);
    private EFDbContext context2 = new EFDbContext(connectionstring2);
    private IQueryable<Flight> context;

    public EFFlightRepository()
    {
        context = (IQueryable<Flight>)context1.Flights.Union(context2.Flights);
    }
    public IQueryable<Flight> Flights
    {
        get { return context;}
    }
}

I get this exception:

The specified LINQ expression contains references to queries that are associated with different contexts.

How can I combine them so I can run my LINQ queries just like it's ONE set of data?

Answer 1

It is difficult to come up with a detailed solution because it really depends on your software design choices, but I think a possible solution consists of the following things:

1) A method / class that creates a collection of DbContext objects using the DbContext constructor with connection string or connection string name (is the same constructor) as Willian Werlang mentioned:

new DbContext("DB1");

2) Your repositories should be able to accept the list of DbContext's rather than a single one. It could e.g. be injected with the constructor of it.

3) The retrieval methods should iterate over the repositories and load (eager load when detaching) the relevant objects.

4) The retrieved objects could be detached from their DbContext using the following code:

dbContext.Entry(entity).State = EntityState.Detached;

This isn't required but might be a consideration since you would return a mix of different data sources.

5) The retrieved/detached objects should be added to a returned List<> or you could yield return the results one by one with IEnumerable<> is return type.

Returning an IQueryable isn't possible in this case but an IEnumerable will do as result.

An example of a simple retrieval method for a flight repository could be something like:

public IEnumerable<Flight> GetFlights() {
    // dbContexts is an IEnumerable<DbContext> that was injected in the constructor
    foreach (var ctx in dbContexts) {
        foreach (var flight in ctx.Flights) {
            yield return flight;
        }
    }
}

Answer 2

You can set multiples databases on your web.config, but with different names, so your DbContext's can receive the name of the database you want as parameter, like:

new DbContext("DB1");

This way you can choose from which database you'll get the data but I don't think you can get data from multiples bases at the same time with only onde dbContext;

Answer 3

My solution was to change my Repository classes to take a connection string parameter, like this:

namespace Dashboard.Domain.Concrete
{
    public class EFFlightRepository : IFlightRepository
    {
        private EFDbContext context;

        public IQueryable<Flight> Flights
        {
            get { return context.Flights;}
        }

        public EFFlightRepository(string connectionString)
        {
            context = new EFDbContext(connectionString);
        }
    }
}

Then create a factory class (using Ninject.Extensions.Factory) to pass the parameter when the repository is being created (How to pass parameters down the dependency chain using Ninject):

namespace Dashboard.Domain.Factories
{
    public interface IFlightRepoFactory
    {
        IFlightRepository CreateRepo(string connectionString);
    }
}

I have another Factory class that produces a list of Repositories based on a list of strings (connection strings to feed to the individual repository classes).

namespace Dashboard.Domain.Factories
{
    public interface IRepoCollectionFactory
    {
        IRepositoryCollection CreateCollection(List<string> connectionStrings);
    }
}

Then, in my controller class, I iterate through the Collection generated by the Collection Factory, running whatever query needs to be run on each set of repositories, and combine the results.

This ultimately gives me a list that contains all of the data from each query on each repository.

public FlightController(IRepoCollectionFactory repoCollectionFactory)
{
    this.repoCollectionFactory = repoCollectionFactory;
    this.collection = repoCollectionFactory.CreateCollection(new List<string> { 
                // each connection string for each database here
    });
}

Bindings in Ninject class:

    private void AddBindings()
    {
        ninjectKernel.Bind<IFlightRepoFactory>().ToFactory();
        ninjectKernel.Bind<IAirportRepoFactory>().ToFactory();
        ninjectKernel.Bind<IPaxRepoFactory>().ToFactory();
        ninjectKernel.Bind<IRepoFactory>().ToFactory();
        ninjectKernel.Bind<IRepoCollectionFactory>().ToFactory();

        ninjectKernel.Bind<IRepositories>().To<EFRepositories>();
        ninjectKernel.Bind<IRepositoryCollection>().To<EFRepositoryCollection>();
        ninjectKernel.Bind<IFlightRepository>().To<EFFlightRepository>();
        ninjectKernel.Bind<IPaxRepository>().To<EFPaxRepository>();
        ninjectKernel.Bind<IAirportRepository>().To<EFAirportRepository>();
    }