EF6 Database First (DbContext) - Change Schema at runtime

原创 2015年11月19日 20:22:49


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




Entity Framework 6 Code First 多个Dbcontext的迁移方法

Entity Framework code first migrations(迁移) 允许我们基于实体来创建和更新数据库. Entity Framework5 code first migration...

How to Change .NET Configuration Files at Runtime (including for WCF)

One of the most common issues people run into with WCF configuration, and .NET applications in gener...

EF学习和使用(一)Database First



ADO.NET Entity Framework 是微软以 ADO.NET 为基础所发展出来的对象关系对应 (O/R Mapping) 解决方案,现已经包含在 Visual Studio 2008 S...

Generating EF Code First model classes from an existing database

Entity Framework Code First is a lightweight way to "turn on" data access for a simple CLR class. ...

【EF 5】结合项目实战分析EF三大工作模式之—Database First

导读:所谓的EF的Databasefirst工作模式,是目前我们(不涉及社会领域)用的最广的一种模式,也是本次ITOO开发所采用的工作模式。本篇博客,就分析在项目中通过Database First模式...

MVC实用构架设计(三)——EF-Code First(6):数据更新最佳实践

前言   最近在整理EntityFramework数据更新的代码,颇有体会,觉得有分享的价值,于是记录下来,让需要的人少走些弯路也是好的。   为方便起见,先创建一个控制台工程,使用usi...
  • dz45693
  • dz45693
  • 2016年04月11日 13:25
  • 1146

[09] 使用 MVC 5 的 EF6 Code First 入门 系列:为ASP.NET MVC应用程序使用异步及存储过程

[渣译文] 使用 MVC 5 的 EF6 Code First 入门 系列:为ASP.NET MVC应用程序使用异步及存储过程 2014-05-13 16:32 by Bce, 1106 阅读, 2...
  • Litt_J
  • Litt_J
  • 2014年12月29日 14:36
  • 849

亲授MVC5中EF6 Code First启动慢及间隙变慢优化的实践经验


MVC5与EF6 Code First 第一个入门完整实例教程

1、创建一个mvc的项目 打开VS2013新建项目一个Web项目,框架选.NET Framewok4.5,项目名字为MiniProfilerDemo。如下图: 接下来在...
您举报文章:EF6 Database First (DbContext) - Change Schema at runtime