Change the database schema of Entity Framework during runtime. c# entity-framework


The connection string may be changed at runtime in the majority of ASP.NET apps to change the database store. I.e., by just altering the value of the "database" column in the connectionstring, I may go from utilizing a test database to a production database.

With entity framework, I'm attempting to modify the schema but not necessarily the database itself, but I'm having no success.

The issue I'm running into is that each entityset's schema is being stored in the edmx xml file's SSDL content.

see below

    Schema="test"  />

Now that I've updated the schema attribute value from "test" to "prod," it works.

However, it doesn't appear like this is a suitable option.

  1. I have more than 50 tables, thus I need to update every entity set as well as the stored procedures.
  2. Only at compilation time can I do this?
  3. Entities that already exist are read if I subsequently attempt to change the Entity model because EF does not recognize that the table already existing in the edm.

Any suggestions?

12/2/2009 6:47:59 PM

Accepted Answer

Update You obviously want to update the referenced schema for each DB, not the database, based on your suggestions. This was clarified in the question's revision, which also brought back the example EDMX you gave that had been obscured by the initial formatting.

Here, I'll reiterate what I said below:

If the schemata are in the same DB, you can't switch these at runtime (except with EF 4 code-only). This is because two identically-named and structured tables in two different schemata are considered entirely different tables.

I would revisit the design of placing test and production data—or, more specifically, "anything and production data"—in the same database, as JMarsch said above. It seems like a tragedy waiting to happen.

Old response is below.

Are you certain that you are modifying the right connection string? The connection string that defines the location of CSDL/SSDL/etc. is included in the connection string that the EF uses, which is inside. A "regular" connection string is often used by another component of your program (e.g., ASP.NET membership). In this situation, you must change both of your connection strings whenever you switch DBs.

Similar to how you must use particular tools for this, which is distinct from the common connection string constructor and understands the EF connection string format, if you edit the connection string at runtime. See the linked example. Also see providing EF connection string assignment guidance.

12/2/2009 6:51:54 PM

Popular Answer

Because it's one of those situations when Microsoft truly missed the boat, I too have this problem, which is actually very aggravating. Support for other databases makes up half of the justification for using EF, however unless you go code first, it doesn't actually solve the issue.

Because the schema is a component of the identity of the tables in MS SQL, modifying the schema makes very little sense. For other kinds of databases, the schema primarily specifies the database's location rather than contributing to its identity. Changes to the database and the schema may both be made by connecting to Oracle.

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow