How to change an EF connection string, based on subdomain of MVC web site?

Question

I have an EF5 ASP.NET MVC 3 (Razor) web site, running under IIS7. Now I want to be able to change the connection string to the MSSQL database depending on the subdomain of the URL, e.g. foo.mydomain.com should connect to my "Foo" database, and bar.mydomain.com should connect to the "Bar" database.

Obviously the DNS records are set up so that they all point to the same web site.

What's the most efficient way of achieving this?

Answer 1

why don't you start passing your own SqlConnection to your YourDbContext?

var partialConString = ConfigurationManager.ConnectionStrings["DBConnectionStringName"].ConnectionString;
var connection = new SqlConnection("Initial Catalog=" + Request.Url.Host + ";" + partialConString);
var context = new MyDbContext(connection, true);

You can also change database in the DBContext:

context.Database.Connection.ChangeDatabase("newDbname");

Answer 2

It's not very easy... You should change the constructor of object context to dynamically change the connection string. Take the subdomain name using System.Web.HttpContext.Current.Request.Url.Host. Then use it to compute the proper connection string.

You should do this in the designer generated code. Of course this is not a good place.. to make it work use the T4 templating. Open your model and right click on the blank designer surface, then select "Add code generation item" -> Ado.net entity object generation. This will create a .tt file. Open it and look for the constructor syntax. Add your logic there.

Good luck!

Answer 3

I've come up with what I feel is a better solution than all those proposed to date. I'm using the default EntityModelCodeGenerator, so perhaps there are other, better, solutions for other templates - but this works for me:

1. Create the other half of the partial class MyEntities.
2. Override OnContextCreated(), which is called from within the class constructor.
3. Change the store connection string using a regex.

This comes out as follows:

partial void OnContextCreated()
{
  // change connection string, depending on subdomain
  if (HttpContext.Current == null) return;
  var host = HttpContext.Current.Request.Url.Host;
  var subdomain = host.Split('.')[0];
  switch (subdomain)
  {
    case "foo":
      ChangeDB("Foo");
      break;
    case "bar":
      ChangeDB("Bar");
      break;
  }
}

private void ChangeDB(string dbName)
{
  var ec = Connection as EntityConnection;
  if (ec == null) return;
  var match = Regex.Match(ec.StoreConnection.ConnectionString, @"Initial Catalog\s*=.*?;", RegexOptions.IgnoreCase);
  if (!match.Success) return;
  var newDbString = "initial catalog={0};".Fmt(dbName);
  ec.StoreConnection.ConnectionString = ec.StoreConnection.ConnectionString.Replace(match.Value, newDbString);
}