Change Entity framework database schema at runtime

Question

In most asp.net applications you can change the database store by modifing the connectionstring at runtime. i.e I can change from using a test database to a production database by simply changing the value of the "database" field in the connectionstring

I'm trying to change the schema (but not necessarily the database itself) with entity framework but no luck.

The problem I'm seeing is the that the SSDL content in the edmx xml file is storing the schema for each entityset.

see below

<EntitySet 
    Name="task" 
    EntityType="hardModel.Store.task" 
    store:Type="Tables" 
    Schema="test"  />

Now I have changed the schema attribute value to "prod" from test and it works..

But this does not seem to be a good solution.

1. I need to update evert entity set as well as stored procedures ( I have +50 tables )
2. I can only do this an compile time?
3. If I then try to later update the Entity model-entityies that already exist are being read due to EF not recognizing that the table already exists in the edm.

Any thoughts?

Answer 1

I have this same issue and it's really rather annoying, because it's one of those cases where Microsoft really missed the boat. Half the reason to use EF is support for additional databases, but unless you go code first which doesn't really address the problem.

In MS SQL changing the schema makes very little sense, because the schema is part of the identity of the tables. For other types of databases, the schema is very much not part of the identity of the database and only determines the location of the database. Connect to Oracle and changing the database and changing the schema are essentially synonymous.