Entity Framework Schema Redirection

Article
08/30/2013

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:

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

Share via

Entity Framework Schema Redirection

Comments

Additional resources