首页 > 代码库 > EF6 Database First (DbContext) - Change Schema at runtime

EF6 Database First (DbContext) - Change Schema at runtime

Problem:
There are two SQL databases (dev and live) with on Azure which has identical table structures but different table schema name. We need a way to change the schema name at runtime thus we can maintain one code base for the two database

Solution:

public class DBHelper    {        public static MyDBEntities Connect()         {            if (ConfigurationManager.ConnectionStrings["DBConnection"] == null)            {                throw new ApplicationException("connectionStrings in .config file is missing a connection named ‘connectionStrings‘");            }            string connectionString = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;            //parse schema name from database name            SqlConnection conn = new SqlConnection(connectionString);            string schema = conn.Database.Replace("_db", "");            return Connect<MyDBEntities>(connectionString, schema);        }        private static T Connect<T>(string connectionString, string schema) where T : DbContext        {            var assembly = typeof(T).Assembly;            var type = typeof(T);            var resourceNames = assembly.GetManifestResourceNames();            string contextName = null;            // code used to avoid of getting the wrong csdl, used when you have more than one             // DbContext on your project            foreach (var csdl in resourceNames.Where(r => r.EndsWith(".csdl")))            {                var csdlDocument = XDocument.Load(assembly.GetManifestResourceStream(csdl));                XNamespace csdlNamespace = "http://schemas.microsoft.com/ado/2009/11/edm";                var name = csdlDocument.Root.Elements(csdlNamespace + "EntityContainer").First().Attribute("Name").Value;                if (type.Name.Equals(name))                {                    contextName = csdl.Replace(".csdl", "");                    break;                }            }            string csdlName = resourceNames.Single(r => r.Equals(contextName + ".csdl"));            string ssdlName = resourceNames.Single(r => r.Equals(contextName + ".ssdl"));            string mslName = resourceNames.Single(r => r.Equals(contextName + ".msl"));            var ssdlDocument = XDocument.Load(assembly.GetManifestResourceStream(ssdlName));            XNamespace ssdlNamespace = "http://schemas.microsoft.com/ado/2009/11/edm/ssdl";            var functions = ssdlDocument.Root.Elements(ssdlNamespace + "Function").ToList();            foreach (var f in functions)                f.SetAttributeValue("Schema", schema);            var entitySets = ssdlDocument.Root.Elements(ssdlNamespace + "EntityContainer").ToList().Elements(ssdlNamespace + "EntitySet").ToList();            foreach (var es in entitySets)                if (es.Attribute("Schema") != null) es.SetAttributeValue("Schema", schema);            Func<string, XmlReader[]> getFromResource = (name) =>            {                using (var s = assembly.GetManifestResourceStream(name))                {                    return new XmlReader[] { XDocument.Load(s).CreateReader() };                }            };            var storeItems = new StoreItemCollection(new XmlReader[] { ssdlDocument.CreateReader() });            var edmItems = new EdmItemCollection(getFromResource(csdlName));            var mappingItems = new StorageMappingItemCollection(edmItems, storeItems, getFromResource(mslName));            var workspace = new MetadataWorkspace();            workspace.RegisterItemCollection(storeItems);            workspace.RegisterItemCollection(edmItems);            workspace.RegisterItemCollection(mappingItems);            workspace.RegisterItemCollection(new ObjectItemCollection());            workspace.LoadFromAssembly(assembly);                        var storeConn = new SqlConnection(connectionString);            ConstructorInfo contextConstructor = typeof(T).GetConstructor(new Type[] { typeof(DbConnection)}); //require a partial class of the EF model to accept one parameter of type DbConnection            var entityConn = new EntityConnection(workspace, storeConn);            return (T)contextConstructor.Invoke(new Object[] { entityConn });        }    }    public partial class MyDBEntities    {        public MyDBEntities(DbConnection existingConnection):base(existingConnection,true)        {        }    }


then can use the entity like below with the DBHelper

using (var db = DBHelper.Connect()){  //you code here to use db}



Note: if you use different EF version, you may need to change the csdl or ssdl namespace in above code
Reference:
http://blogs.msdn.com/b/dbrowne/archive/2013/08/30/entity-framework-schema-redirection.aspx http://pastebin.com/UpqUzezq

EF6 Database First (DbContext) - Change Schema at runtime