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 = "http://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 = "http://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 (5)

  1. René says:

    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 });

           }

  2. KC says:

    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

  3. Jonatas W. Gonçalves says:

    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!

  4. Jonatas Wesley Gonçalves says:

    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!

  5. zbethem says:

    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?