Entity Framework Schema Translations
3 min read
3 min read
You should have different setup environments, right. Normally something like dev, test (or staging) and production. This implies to have different DBs as well. At work we have Oracle databases and there we don't really have different DB "instances" but different schemas, depending on the environment we deploy to. This post presents an approach of allowing to dynamically "rewrite" the schema defined in the EF mappings in an easy to use way.
<EntitySet Name="ADDRESSES"Note that this is the xml file generated by the DevArt Entity Developer as we use that one since it has some nice features. The problem here is the hard-coded schema definition in line 4. Why? Because in the connection string you have to specify the schema name as the
<connectionStrings>So normally, when we deploy to a different environment, a web.config transformation adapts the
connectionString="metadata=res...connection string='User Id=MYSCHEMA;Password=...'"
User Idfor that specific environment like changing
MYSCHEMA_TEST. Unfortunately that doesn't work because - remember - the EF configuration file has still
MYSCHEMAhardcoded in the configuration file.
ALTER SESSION SET CURRENT_SCHEMA=MYSCHEMA_TEST;The drawback: you execute one additional command on each connection opening. Not probably a big overhead, but still. DevArt supports this starting from version 7 of their dotConnect driver. In there, they added an
Initialization Commandproperty defined on the connection string and which lets you specify exactly this alter session command. We currently have v 6.something ... Although we're planning to upgrade probably, I continued to search.
DbContextand finally, it adapted an approach of substituting all schema definitions which is not necessarily always desired.
Schema Translations=MYSCHEMA->MYSCHEMA_TEST,ANOTHERSCHEMA->ANOTHERSCHEMA_STAGINGAdvantages of this approach:
I've just uploaded the project on GitHub: https://github.com/juristr/dbcontext-utils
Feel free to submit any comments or pull requests :)