Changing schema name on runtime - Entity Framework

I need to change the storage schema of the entities on runtime. I've followed a wonderful post, available here: http://blogs.microsoft.co.il/blogs/idof/archive/2008/08/22/change-entity-framework-storage-db-schema-in-runtime.aspx?CommentPosted=true#commentmessage

This works perfectly, but only for queries, not for modifications.

Any idea why?

Well, I was looking for this piece of code all around the Internet. In the end I had to do it myself. It's based on Brandon Haynes adapter, but this function is all you need to change the schema on runtime - and you don't need to replace the autogenerated context constructors.

public static EntityConnection Create(
    string schema, string connString, string model)
{
    XmlReader[] conceptualReader = new XmlReader[]
    {
        XmlReader.Create(
            Assembly
                .GetExecutingAssembly()
                .GetManifestResourceStream(model + ".csdl")
        )
    };
    XmlReader[] mappingReader = new XmlReader[]
    {
        XmlReader.Create(
            Assembly
                .GetExecutingAssembly()
                .GetManifestResourceStream(model + ".msl")
        )
    };

    var storageReader = XmlReader.Create(
        Assembly
            .GetExecutingAssembly()
            .GetManifestResourceStream(model + ".ssdl")
    );
    XNamespace storageNS = "http://schemas.microsoft.com/ado/2009/02/edm/ssdl";

    var storageXml = XElement.Load(storageReader);

    foreach (var entitySet in storageXml.Descendants(storageNS + "EntitySet"))
    {   
        var schemaAttribute = entitySet.Attributes("Schema").FirstOrDefault();
        if (schemaAttribute != null)
        {
            schemaAttribute.SetValue(schema);
        }
    }
    storageXml.CreateReader();

    StoreItemCollection storageCollection =
        new StoreItemCollection(
            new XmlReader[] { storageXml.CreateReader() }
        );
    EdmItemCollection conceptualCollection = new EdmItemCollection(conceptualReader);
    StorageMappingItemCollection mappingCollection =
        new StorageMappingItemCollection(
            conceptualCollection, storageCollection, mappingReader
        );

    var workspace = new MetadataWorkspace();
    workspace.RegisterItemCollection(conceptualCollection);
    workspace.RegisterItemCollection(storageCollection);
    workspace.RegisterItemCollection(mappingCollection);

    var connectionData = new EntityConnectionStringBuilder(connString);
    var connection = DbProviderFactories
        .GetFactory(connectionData.Provider)
        .CreateConnection();
    connection.ConnectionString = connectionData.ProviderConnectionString;

    return new EntityConnection(workspace, connection);
}

The resulting EntityConnection should be passed as a parameter when instantiating the context. You can modify it, so all ssdl models are modified by this function, not only the specified one.

I've managed to resolve this issue by using a brilliant library, located in CodePlex (courtesy of Brandon Haynes), named "Entity Framework Runtime Model Adapter", available here: http://efmodeladapter.codeplex.com/

I've tweaked it a bit, to fit our needs and without the need of replacing the designer code at all.

So, I'm good.

Thanks anyways, and especially to Brandon, amazing job!

I need import data from postgres database. It by default use schema "public". So I use Entity Framework CTP 4 "Code first". It by default use schema "dbo". To change it in runtime I use:

public class PublicSchemaContext : DbContext
{
    protected override void OnModelCreating(System.Data.Entity.ModelConfiguration.ModelBuilder builder)
    {
        builder.Entity<series_categories>().MapSingleType().ToTable("[public].[series_categories]");
    }

    public DbSet<series_categories> series_categories { get; set; }
}

It work for select, insert, update and delete data. So next test in pass:

[Test]
        public void AccessToPublicSchema()
        {
            // Select
            var db = new PublicSchemaContext();
            var list = db.series_categories.ToList();
            Assert.Greater(list.Count, 0);
            Assert.IsNotNull(list.First().series_category);

            // Delete
            foreach (var item in db.series_categories.Where(c => c.series_category == "Test"))
                db.series_categories.Remove(item);
            db.SaveChanges();

            // Insert
            db.series_categories.Add(new series_categories { series_category = "Test", series_metacategory_id = 1 });
            db.SaveChanges();

            // Update
            var test = db.series_categories.Single(c => c.series_category == "Test");
            test.series_category = "Test2";
            db.SaveChanges();

            // Delete
            foreach (var item in db.series_categories.Where(c => c.series_category == "Test2"))
                db.series_categories.Remove(item);
            db.SaveChanges();
        }