Change Entity Framework storage DB schema in runtime

August 22, 2008

When you build a EF schema, it automatically plant the name of the imported tables schema into the EDMX file. But alas, what happens when you work in different environments (such as Dev, Test, Prod…) and in each database, the schema of the tables is different?

Basically you either reset all your databases to use the same schema or worse – change the EDMX and recompile the assembly before updating the wanted environment.

But there is another solution, not a clear one, probably not a supported one, but still, it works.

Every EF model is accompanied by metadata, accessible from the MetadataWorkspace property of the model. This metadata class holds the collections of the conceptual, store and mapping items, and is loaded in sections: first of all the conceptual model is loaded, and before the first query is executed, the store and mapping model loads.

The idea is to interrupt to load process and change the schema name before any query is executed.

Look at this code:

   1: Employees e = new Employees();
   2:  
   3: XmlReader[] sReaders = new XmlReader[]{ XmlReader.Create(
   4:     Assembly.GetExecutingAssembly().GetManifestResourceStream("Employees.ssdl"))};
   5:  
   6: XmlReader[] mReaders = new XmlReader[]{ XmlReader.Create(
   7:     Assembly.GetExecutingAssembly().GetManifestResourceStream("Employees.msl"))};
   8:  
   9: StoreItemCollection sCollection = new StoreItemCollection(sReaders);
  10: EdmItemCollection cCollection = e.MetadataWorkspace.GetItemCollection(
  11:     DataSpace.CSpace) as EdmItemCollection;
  12:  
  13: StorageMappingItemCollection csCollection = 
  14:     new StorageMappingItemCollection(cCollection, sCollection, mReaders);
  15:  
  16: e.MetadataWorkspace.RegisterItemCollection(sCollection);
  17: e.MetadataWorkspace.RegisterItemCollection(csCollection);
  18:  
  19: EntityContainer container = 
  20:     e.MetadataWorkspace.GetItem<EntityContainer>(
  21:         "TestModelStoreContainer", 
  22:         DataSpace.SSpace);            
  23:                     
  24: EntitySetBase set = container.BaseEntitySets["Person"];
  25:  
  26: typeof(EntitySetBase).GetField(
  27:     "_schema",
  28:     BindingFlags.NonPublic | BindingFlags.Instance).SetValue(set, "dbo");
  29:           
  30: var q = from p in e.Person
  31:         select p;
  32:  
  33: Console.WriteLine(e.Person.ToTraceString());
  34: Console.WriteLine(q.First().Age);

Let’s inspect the code:

Lines 3-7: We load the conceptual and storage xml from the resources of the containing assembly (if the code is run in another assembly, change the code to return the assembly that holds the model).

Lines 16-17: We must load both new parts to the MetadataWorkspace, otherwise we’ll get an exception when trying to execute a query.

Lines 20-24: Extract an entity set definition from the store collection (if all tables are needed to be changed, you may want to use a foreach statement on the BaseEntitySets collection).

Lines 26-29: Here’s the catch – in order to change the schema, we need to reset the value of a private field (the access property is an inline one). So we use wonderful reflection that allows us to do it (full trust required).

From there on, every time a query is built, it will use the wanted schema.

Note:

  1. Using this approach you can also change other stuff, like table names, defining queries and so on.
  2. There is a simpler way to load the store model (instead of writing rows 3-22), explained by Julie Lerman here, but it will work for examining the collection, not for changing it – the use of ToTraceString on an entity will cause the query to be cached, so future queries on the same entity will use the old schema name instead of the new. Loading the store collection first and then creating a query will use the new schema name.

 

 

Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

6 comments

  1. Ido FlatowAugust 24, 2008 ב 3:01 am

    If you’re going to change the schema after some queries have been loaded, you should also set the field “_cachedProviderSql” to null after setting the new schema value (after line 28).
    Setting this will cause already prepared queries and new queries on items which where queried before to use the new schema without the need to build a new model instance

    Reply
  2. SamMarch 4, 2009 ב 1:28 pm

    You say it would be possible to change the table name, using this approach – do you maybe know which fields need to be changed to achieve this?

    Namely I want to add the database name to the table name, and I’m not sure what value I’d need to change to achieve this.

    Thanks!
    Sam

    Reply
  3. Nir PApril 18, 2010 ב 8:19 pm

    Hi,
    I can’t thank you enough for this post, it helped me a lot.
    This is working, however, only for queries manipulation but not for modification (update\insert\delete).
    The schema stays the same, in the modification commans.

    Any idea why?
    Thanks!
    Nir.

    Reply
  4. LMOctober 17, 2011 ב 9:10 am

    @Nir
    I am facing the same issue. Any solutions so far?

    LM

    Reply
  5. FranciscoJanuary 26, 2012 ב 11:09 pm

    Same issue, Please can you let us know if this has been looked at. I would love to use this approach for updates/deletes and insterts.

    Reply
  6. http://www.exlair.comJuly 25, 2013 ב 5:03 am

    clearly like your website however you have to verify the spelling on fairly a few of one’s posts. Many of them are rife with spelling issues and I to discover it extremely troublesome to inform the truth on the other hand I’ll surely come back again.

    Reply