Entity Framework 4.1 : Is there a way to switch schema on runtime

Question

up to now (EFv1) we used this solution: http://efmodeladapter.codeplex.com/ after upgrading to .NET Framework 4.0 we are looking for built in way to change schema on runtime when using Entity Framework 4.0/4.1?

to be more specific we have two big schemas on DB (A and B) and only schema A added to the Model. the tables in A and B are identically same. i am looking for a solution to switch between A and B in runtime.

Answer 1

As Ladislav says, You could use two SSDL Files,

Here's how to do it.

• Right click on your .edmx file and choose properties
• change "Metadata artifact Processing" to Copy to Output Directory
• open .ssdl file from your output directory
• replace all Schema="dbo" to Schema="A"
• Save file as a.ssdl

• Initialize your DbContext using following connection string

  metadata=~/bin/Model1.csdl|~/bin/a.ssdl|~/bin/Model1.msl;provider=System.Data.SqlClient;provider connection string='data source=.\SQLEXPRESS;initial catalog=;integrated security=True;multipleactiveresultsets=True;App=EntityFramework';";

Answer 2

No. The schema is part of mapping file. So the options are either:

• Don't use mapping files as resources. Change the SSDL file at runtime.
• Use two different SSDL files and build connection string at runtime.

Answer 3

I can suggest you two models separately. And depending on the conditions use one or another. For example

void Foo(DbContext model) {
    if (model.GetType() == typeof(FooNamespace.MyFooModel)){
        var db = model as FooNamespace.MyFooModel;
        //Do stuff
    } else {
        var db = model as BarNamespace.MyBarModel;
        //Do stuff
    }
}

If your models have some similarities than you can use some interface. If your models share some commone attributes, i.e. both of them have Category objects, then having two different namespaces for models solves the problem.

Answer 4

This is my take on a similar scenario (very similar to @Greatran answer, but I could not find any Metadata artifact processing property item on my .EDMX file):

1. Build at least once your project containing the EDMX file
2. Grab a copy of generated .SSDL file, contained in a subpath of

   Your\Project\Folder\obj\Debug\edmxResourcesToEmbed\Your\EDMX\Namespace\

3. Copy and include that under your project, e.g. in the same directory as EDMX file: ...\Your\EDMX\Namespace\MyModel.ssdl
4. Rename it adding a suffix to match your deploy environment, so e.g. for Debug environment it would be MyModel.Debug.ssdl
5. From its file properties, set Build Action to Embedded Resource
6. Grab a copy of related context connection string from the web project Web.config file. That should be something like

connectionString="metadata=res://\*/XXXXX.MyModel.csdl|
res://*/XXXXX.MyModel.ssdl|
res://*/XXXXX.MyModel.msl; 
provider=ZZZZZ;provider connection string='AAAAA'" 

1. Transform that connection string in the web project config file for your deploy environment, say Web.Debug.config, in order to substitute the EDMX-generated SSDL file with your custom SSDL file:

connectionString="metadata=res://\*/XXXXX.MyModel.csdl|
res://*/YYYYY.MyModel.Debug.ssdl|
res://*/XXXXX.MyModel.msl;
provider=ZZZZZ;provider connection string='BBBBB'" 

The last point could get tricky, at least in my experience, as the namespace of EDMX-generated SSDL resource was different from the namespace of my custom SSDL resource. In order to get the exact namespace for my custom resource, I ran a debug session in VS and checked the output of this.GetType().Assembly.GetManifestResourceNames() in Immediate Window (thanks to stu432).

Also, I changed the connection string ='AAAAA' part in order to match the DB server in deploy environment.

Answer 5

The solution in c# Entity Framework EF 4.1 Change Schema and Database name at runtime works well. It basically changes the schema in the SSDL resource at runtime, so there is no need to generate a new SSDL for each schema used.