James Zhou's blog


EF6 Database First (DbContext) - Change Schema at runtime


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


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", "");

            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(new ObjectItemCollection());
            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




个人分类: .NET EF
上一篇ASP.NET MVC - Display UTC time from server as local time on client side
下一篇ASP.NET MVC - loop model data in javascript
想对作者说点什么? 我来说一句