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

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

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

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启动慢及间隙变慢优化的实践经验

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

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

1、创建一个mvc的项目 打开VS2013新建项目一个Web项目,框架选.NET Framewok4.5,项目名字为MiniProfilerDemo。如下图: 接下来在...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:EF6 Database First (DbContext) - Change Schema at runtime
举报原因:
原因补充:

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