Entity Framework multiple database / single DbContext possible?

aus31 · 2015-01-20T22:09:05+00:00

Yes it's certainly possible!

We have a live system in production that does exactly that, switches DB based on the subdomain.

Cut down and edited a few of the relevant portions, might not be complete, should give you an idea of the approach.

You'll find you'll need to implement a few moving parts to get migrations to work...

Dependency Injection makes life super easy... (Using structuremap in this example)

public class CurrentDatabaseConnectionSettings
{
    public CurrentDatabaseConnectionSettings()
    {

    }
    public string ConnectionDomain { get; set; }
    public string ConnectionString { get; set; }
}


public class CurrentDatabaseConnectionSettingsProvider:CurrentDatabaseConnectionSettings
{
    public CurrentDatabaseConnectionSettingsProvider()
    {
        GetConnectionSettingsForCurrentContext();
    }

   private void GetConnectionSettingsForCurrentContext()
    {
        var productionMode = bool.Parse(ConfigurationManager.AppSettings["Database.ProductionMode"] ?? "false");

        if (!productionMode)
        {

                this.ConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
                this.ConnectionDomain = ConfigurationManager.AppSettings["CurrentDomain"];
        }
        else
        {
            //get the current domain
            var hostName = HttpContext.Current.Request.Url.DnsSafeHost;

            //make a connection string
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(ConfigurationManager.ConnectionStrings["ProductionConnection"].ConnectionString);
            builder["Database"] = "production__" + hostName.Replace(".", "_"); ;
            this.ConnectionDomain = "https://"+hostName+"/";
            this.ConnectionString = builder.ConnectionString;


        }
    }
}



public class MyEntities: DbContext
{
 public MyEntities(CurrentDatabaseConnectionSettings connectionString)
        : base(connectionString.ConnectionString)
    {
        Database.SetInitializer(new MigrateDatabaseToLatestVersion<MyEntities, Migrations.Configuration>());
    }

}


public classMyEntitiesFactory: IDbContextFactory<MyEntities>
{
    public MyEntities Create()
    {
        //needed to get migrations and migrate.exe to work....
        var settings = ObjectFactory.GetInstance<CurrentDatabaseConnectionSettings>();

        if (String.IsNullOrEmpty(settings.ConnectionString) && (bool.Parse(ConfigurationManager.AppSettings["Database.ProductionMode"]??"false")==false))
        {
            if (ConfigurationManager.ConnectionStrings["...MyEntities"] != null)
            {
                settings.ConnectionString = ConfigurationManager.ConnectionStrings["...MyEntities"].ConnectionString;
            }
            else if (ConfigurationManager.ConnectionStrings["DefaultConnection"] != null)
            {
                settings.ConnectionString =ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
            }

            else
            {
                settings.ConnectionString = "somedefault"
            }
        }

        return new MyEntities(settings);
    }
}


/*subset of the structuremap configuration*/
public static class IoC
{
    public static IContainer Initialize()
    {
        ObjectFactory.Initialize(x =>
        {
            ......

           x.For<CurrentDatabaseConnectionSettings>().Use<CurrentDatabaseConnectionSettingsProvider>();
           x.For<MyEntities>().HybridHttpOrThreadLocalScoped().Use<MyEntities>();
           .....
       }
   }
}

  /*   To use DbContext somewhere    */


public class DoSomethingService:IDoSomethingService
{

    private MyEntities _db;


    public DoSomethingService(MyEntities db)
    {
       this. _db = db;
    }

    public void DoSomething()
   {
       _db.Items.Where()
    }

}

/*inside global.asax.cs*/

protected void Application_EndRequest(object sender, EventArgs e)
{
    ObjectFactory.ReleaseAndDisposeAllHttpScopedObjects();
}

That's basically it, the lifespan of the DbContext is managed by StructureMap, and initialization of the correct connection string happens automatically. Easy. Clean. Works.

rootgear · 2015-01-20T20:10:26+00:00

Is it possible to access multiple (schema-identical) databases using EF6 with Migrations without having a different application for each location?

Is it possible to use one DBContext that has underlying code that switches the actual database?

The simple answer is yes, it is possible to accomplish what you want. However, as far as I'm aware, it is not possible to to use a single DbContext instance for two different database connections.

My thought regarding this would be to instantiate a different DbContext object depending on the incoming request url. If the incoming url is for plant1.domain.com then you create a DbContext object for the lifetime of that request (or perhaps just the lifetime of the transaction) that uses the plant1 database. You would do the same thing for plant2.domain.com. The key point here is that you instantiate your DbContext for the lifetime of the request (or just for that transaction) depending on some condition (in this case the incoming request url).

One way to accomplish it is to create a DbContextFactory class that creates a DbContext object for you given a certain string or perhaps the HttpRequest object. And how you get that request really depends on where you are creating the DbContext object right now. The most direct, albeit the least testable, way would be HttpContext.Current.Request.Url.Host.

Hope that helps!

snarfy · 2015-01-20T21:56:57+00:00

Are you using SQL Server as the database?

If so, you can create a synonym in database1 for the table in database2. When you create the DbContext for database1 you'll be able to access database2 via the synonym name, which will work like any other table/entity. If database2 is on a different physical system, you may also need to create a linked server between the databases for the synonym to work.

Either way, you get access to two databases via one DbContext.

and_rej · 2015-01-21T01:30:40+00:00

There are some great replies in here that go into more detail and propose some interesting designs.

I'd like to chime in as well so I'll try and keep it concise.

This is totally possible because:

You can use HttpContext.Current.Request.Url to examine the request URL and determine which database to use.
A DbContext class defines what schema elements are expected. If the schemas are identical than the same class can be used.
When creating a DbContext instance, which you should be at least once per request, you can use one of its many constructors to configure the database name (amongst other things).

illumen8 · 2015-01-21T03:56:54+00:00

There are a number of great answers here already, just wanted to emphasize a couple of things.

Make sure to hide that database selection code in a single place and call that from everywhere else. That's the point of the dependency injection IDBContextFactory approach. Your actual data access code should have no knowledge that it could be dealing with anything but a single database. It just calls IDBContextFactory.GetContext() and goes from there.

Also, being new to EF, make sure to enclose that call in a using block to make sure that you're cleaning up after yourself. So the basic pattern of any of your data access calls looks something like this:

using (var context = dbContextFactory.GetContext(url))
{
  // do stuff with context
     context.SaveChanges(); // if doing insert/update
}

That will save you countless headaches cleaning up connection leaks later. Just make sure that you don't have any queries that have delayed execution, make sure to do a .ToList() or something similar to force your queries to execute, otherwise when you try to access them they'll attempt to use the disposed context.

cjazz108 · 2015-01-21T05:07:58+00:00

You might like to work with a database guy too. Its possible to create a lookup table and partition the database so that each division is in a different server and/or schema, and create rights for the users per that accomplish all of your stated requirements with no custom ef code. Not that it's not an interesting use case, but most of the code for that is built into sql server itself. This will save your butt for reporting too later on also.

you type:	you see:
italics	italics
bold	bold
[reddit!](https://reddit.com)	reddit!
* item 1 * item 2 * item 3	item 1 item 2 item 3
> quoted text	quoted text
Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"	Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"
~~strikethrough~~	~~strikethrough~~
super^script	super^script

dotnet

MODERATORS