EF6 Database First (DbContext) - Change Schema at runtime

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

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


版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

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

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

EF框架—Database-First

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

EF学习和使用(一)Database First

上篇博客中介绍了ORM思想,可以说ORM思想在数据交互方面给我们带来了一次变革。他能够自动实现Entity实体的属性与关系型数据库字段的映射,增删改查的sql脚本由ORM来自动生成,使我们编码时不用考...

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. ...

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...

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

最近项目在使用EF了,mvc使用EF确实方便,因为添加功能的时候可以使用vs自动生成用ef的增、删、查、改的模板,大的提高的工作效率。但是很多人都遇到过用EF开发的程序在第一次访问的时候会比用ADO纯...

.net EF DBcontext 简单demo

  • 2016-09-11 23:56
  • 10.37MB
  • 下载

EF异常‘在创建模型,此时不可使用上下文。如果在 OnModelCreating 方法内使用上下文或如果多个线程同时访问同一上下文实例,可能引发此异常。 请注意不保证 DbContext 的实例成员’

一、EF的线程安全问题 二、
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)