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.