Entity Framework Schema Redirection

If your runtime schema differs from your design-time schema, you need to update your Entity Framework metadata to hit your new schema.

Here's a simple solution for doing it at runtime for Entity Framework 4.  I need to update it for EF 5 and 6.

 // Copyright (c) Microsoft Corporation.  All rights reserved
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;
using System.Xml.Linq;
using System.Xml;
using System.Data.Metadata.Edm;
using System.Data.SqlClient;
using System.Reflection;
using System.Data.EntityClient;
using System.Data.Mapping;
 
namespace EfSchemaRedirection
{
 
  class Program
  {
    static T Connect<T>(string connectionString, string schema) where T : ObjectContext
    {
      var assembly = typeof(T).Assembly;
 
      var rn = assembly.GetManifestResourceNames();
      var ssdl = rn.Single(r => r.EndsWith(".ssdl"));
      var csdl = rn.Single(r => r.EndsWith(".csdl"));
      var msl = rn.Single(r => r.EndsWith(".msl"));
 
      var doc = XDocument.Load(assembly.GetManifestResourceStream(ssdl));
 
      XNamespace ns = "https://schemas.microsoft.com/ado/2009/02/edm/ssdl";
 
      var entitySets = doc.Root
                          .Elements(ns + "EntityContainer").ToList()
                          .Elements(ns + "EntitySet").ToList();
      foreach (var es in entitySets)
      {
        var dq = es.Descendants(ns + "DefiningQuery").FirstOrDefault();
        if (dq != null)  //warning hacking any defining queries is likely to be fragile and require customization.
        {
          XNamespace store = "https://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator";
          var designTimeSchema = es.Attribute(store + "Schema").Value;
          dq.Value = dq.Value.Replace("FROM [" + designTimeSchema + "].", "FROM [" + schema + "].")
                             .Replace("JOIN [" + designTimeSchema + "].", "JOIN [" + schema + "].");
        }
        else
        {
          es.SetAttributeValue("Schema", schema);
        }
        
      }
 
      Func<string, XmlReader[]> getFromResource = (name) =>
      {
        using (var s = assembly.GetManifestResourceStream(name))
        {
          return new XmlReader[] { XDocument.Load(s).CreateReader() };
        }
      };
 
      var workspace = new System.Data.Metadata.Edm.MetadataWorkspace();
      var storeItems = new StoreItemCollection(new XmlReader[] { doc.CreateReader() });
      var edmItems = new EdmItemCollection(getFromResource(csdl));
      var mappingItems = new StorageMappingItemCollection(edmItems, storeItems, getFromResource(msl));
 
      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(EntityConnection) });
      var entityConn = new EntityConnection(workspace, storeConn);
      return (T)contextConstructor.Invoke(new Object[] { entityConn });
 
    }
    static void Main(string[] args)
    {
      using (var db = Connect<SchemaTestEntities>(@"server=.;database=SchemaTest;Integrated Security=true","B"))
      {
        var t = db.T.First();
        var vt = db.vts.First();
 
        Console.WriteLine(t.source_schema);
        Console.WriteLine(vt.source_schema);
 
        var sql = string.Format("select * from {0}.[vt]","A");
 
        var vt2 = db.ExecuteStoreQuery<vt>(sql).First();
 
        Console.WriteLine(vt2.source_schema);
      }
 
      
    }
  }
}

Comments

• Anonymous
  December 06, 2013
  Thanks Dave!!! This works with EF6:        public static T Connect<T>(string connectionString, string schema) where T : DbContext        {            var assembly = typeof(T).Assembly;            var resourceNames = assembly.GetManifestResourceNames();            var ssdlName = resourceNames.Single(r => r.EndsWith(".ssdl"));            var csdlName = resourceNames.Single(r => r.EndsWith(".csdl"));            var mslName = resourceNames.Single(r => r.EndsWith(".msl"));            var ssdlDocument = XDocument.Load(assembly.GetManifestResourceStream(ssdlName));            XNamespace ssdlNamespace = "schemas.microsoft.com/.../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)            {                es.SetAttributeValue("Schema", schema);            }            Debug.WriteLine(ssdlDocument.ToString(System.Xml.Linq.SaveOptions.DisableFormatting));            Func<string, XmlReader[]> getFromResource = (resourceName) =>            {                using (var s = assembly.GetManifestResourceStream(resourceName))                {                    return new XmlReader[] { XDocument.Load(s).CreateReader() };                }            };            var edmItems = new EdmItemCollection(getFromResource(csdlName));            var storeItems = new StoreItemCollection(new XmlReader[] { ssdlDocument.CreateReader() });            var storageMappingItems = new StorageMappingItemCollection(edmItems, storeItems, getFromResource(mslName));            var workspace = new MetadataWorkspace(                () => { return edmItems; },                () => { return storeItems; },                () => { return storageMappingItems; }                );            workspace.LoadFromAssembly(assembly);            var storeConn = new SqlConnection(connectionString);            ConstructorInfo contextConstructor = typeof(T).GetConstructor(new Type[] { typeof(ObjectContext) });            var entityConn = new EntityConnection(workspace, storeConn);            var context = new ObjectContext(entityConn);            return (T)contextConstructor.Invoke(new Object[] { context });        }

• Anonymous
  January 17, 2014
  Hi Dave, This works fine in my project. But the issue is var dbContext=Model<testContext>(); after returning if i use dbContext its still pointing to old schema but not schema. While the method which you is working perfectly without any error. Can you help me on this. Thanks . KC

• Anonymous
  March 06, 2014
  Thanks Dave! I´m using EF5, i did it work using part of your code and part of René code. I did some changes because  i´m using Oracle, and add some code to resolve the problem that occurs when you have more than one DbContext in your project. I posted in Pastbin http://pastebin.com/UpqUzezq because of the comment size limitations. Sorry, my poor english!

• Anonymous
  March 06, 2014
  Hi, it´s me again. I just wanna leave a comment with my login. Thanks Dave! I´m using EF5, i did it work using part of your code and part of René code. I did some changes because  i´m using Oracle, and add some code to resolve the problem that occurs when you have more than one DbContext in your project. I posted in Pastbin http://pastebin.com/UpqUzezq because of the comment size limitations. Sorry, my poor english!

• Anonymous
  November 18, 2014
  Thanks for the write-up and help from the comments. This works great with exception. When I don't use the redirection, I see Oracle queries against a 1-row table taking 0msecs. Note that there is an initial hit on the first query, but after that they all clock in at 0msecs. Using the redirection, I see a cost of 46msecs to 100msecs. I also see that the total Oracle query time is consistently +40msecs to +70msecs on top of the redirection cost. This seems significant to me. I'm guessing I have found two negatives:

1. Linq-SQL query statements aren't cached (explanation for the 0msecs query response times versus consistent >0msecs)
2. Reflection/manipulation takes time Anyone find a trick to cache or speed it up?