Oracle DB EDMX: Switch Between Prod/Test/Stage Schema Names Dynamically using Entity Framework DB First Approach

<b>Dipon<font color="orange">Roy</font></b>

5.00/5 (1 vote)

Jun 5, 2020

CPOL

3 min read

9869

Use same EDMX file with different Oracle database schemas

Background

It was an ASP.NET .NET Framework 4.5 project using EntityFramework 6.0.0 generated EDMX from an Oracle database schema. Things were working great with the production schema, but unexpectedly, it wasn't connecting/working with the newly created/production-replicated test schema.

Db Context Class

The Db context was very basic as shown below:

Production Connection String

Production schema name: IPWAVE

  <connectionStrings>
    <!--Prod-->
    <add name="IPDB" 
     connectionString="metadata=res://*/EntityDataModels.IPAddressPlannaing.csdl|
     res://*/EntityDataModels.IPAddressPlannaing.ssdl|res://*/
     EntityDataModels.IPAddressPlannaing.msl;provider=Oracle.ManagedDataAccess.Client;
     provider connection string=&quot;DATA SOURCE=xx.xx.xx.xx:xxxx/yyyyyy;
     PASSWORD=SecretThing;USER ID=IPWAVE&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>

Change Connection String

At Web.config, we changed database connection string from production to test, new test schema name: IPWAVETEST.

  <connectionStrings>
    <!--Test-->
    <add name="IPDB" connectionString="metadata=res://*/EntityDataModels.
     IPAddressPlannaing.csdl|res://*/EntityDataModels.IPAddressPlannaing.ssdl|
     res://*/EntityDataModels.IPAddressPlannaing.msl;provider=Oracle.ManagedDataAccess.Client;
     provider connection string=&quot;DATA SOURCE=xx.xx.xx.xx:xxxx/yyyyyy;
     PASSWORD=SecretThing;USER ID=IPWAVETEST&quot;" providerName="System.Data.EntityClient"/>
  </connectionStrings>

As we can see, the schema names were different.

Production schema name: IPWAVE
New test schema name: IPWAVETEST

After changing the connection string, we ran the application but it was throwing exceptions.

Why Wasn't It Working?

Entity Framework Generated SQL Not Working With New Schema

After debugging, we found the connection established without any issue but Linq or Lambda Expression not working with the DbContext due to entity framework generated SQL. The SQL was including old schema name like:

SELECT * FROM "IPWAVE"."IPAPM_CLOCK"

Where the expected query was:

SELECT * FROM "IPWAVETEST"."IPAPM_CLOCK"

EDMX Hardcoded Schema Name

The SQL generation module of an Entity Framework provider translates a given query command tree into a single SQL depending on the configuration. In our case, EDMX file DbModelContext.edmx was previously auto-generated from Db, contained all the mapping configurations including hardcoded schema name IPWAVE inside it.

Here is the screenshot of the schema binding section of the EDMX file.

Possible Solutions

Change schema name sections of the EDMX file manually each time, while switching from one schema to another
Manage things dynamically, depending on connection string provided schema name

Manage Things Dynamically

To manage things dynamically, we need to:

Update existing EntityFramework
Create a custom Db Command Interceptor, which will replace the old schema name with a new one for SQL query
Use the custom Db Command Interceptor with current DbContext

Update EntityFramework

We have to upgrade EntityFramework 6.0.0 to EntityFramework 6.2.0 or later to use Db Command Interceptor option. This upgrade may add new things to the current web.config file.

<?xml version="1.0" encoding="utf-8"?>
<packages>
  <package id="EntityFramework" version="6.2.0" targetFramework="net45" />
</packages>

Db Command Interceptor

This class will replace oldSchema name with the newSchema name, before the query reaches the database.

using System.Data.Entity.Infrastructure.Interception;

namespace IPAPM.EntityDataModels
{
    class ReplaceSchemaInterceptor : IDbCommandInterceptor
    {
        private readonly string _oldSchema;

        private readonly string _newSchema;

        public ReplaceSchemaInterceptor(string oldSchema, string newSchema)
        {
            _oldSchema = Schema(oldSchema);
            _newSchema = Schema(newSchema);
        }

        public string Schema(string schema) 
        {
            string value = string.Format(@"""{0}"".", schema);
            return value;
        }

        public void NonQueryExecuted(System.Data.Common.DbCommand command, 
                                     DbCommandInterceptionContext<int> interceptionContext)
        {
        }

        public void NonQueryExecuting(System.Data.Common.DbCommand command, 
                                      DbCommandInterceptionContext<int> interceptionContext)
        {
            command.CommandText = command.CommandText.Replace(_oldSchema, _newSchema);
        }

        public void ReaderExecuted(System.Data.Common.DbCommand command, 
        DbCommandInterceptionContext<System.Data.Common.DbDataReader> interceptionContext)
        {
        }

        public void ReaderExecuting(System.Data.Common.DbCommand command, 
        DbCommandInterceptionContext<System.Data.Common.DbDataReader> interceptionContext)
        {
            command.CommandText = command.CommandText.Replace(_oldSchema, _newSchema);
        }

        public void ScalarExecuted(System.Data.Common.DbCommand command, 
                    DbCommandInterceptionContext<object> interceptionContext)
        {
        }

        public void ScalarExecuting(System.Data.Common.DbCommand command, 
                    DbCommandInterceptionContext<object> interceptionContext)
        {
            command.CommandText = command.CommandText.Replace(_oldSchema, _newSchema);
        }
    }
}

Things may need to change depending on needs, so please check string Schema(string schema) method.

Use Db Command Interceptor With DbContext

The schema name switching is going to take place inside the constructor.

private const string _edmxDefaultSchema = "IPWAVE"; EDMX hardcoded schema name
string schemaValue Expected schema name from the connection string
public readonly string Schema; The actual schema that will be used to generate SQL

namespace IPAPM.EntityDataModels
{
    using Oracle.ManagedDataAccess.Client;
    using System;
    using System.Configuration;
    using System.Data.Entity;
    using System.Data.Entity.Core.EntityClient;
    using System.Data.Entity.Infrastructure;
    using System.Data.Entity.Infrastructure.Interception;
    using System.Data.SqlClient;

    public partial class IPDB : DbContext
    {
        private const string _edmxDefaultSchema = "IPWAVE";
        public readonly string Schema;
        public IPDB()
            : base("name=IPDB")
        {
            string conString = ConfigurationManager.ConnectionStrings["IPDB"].ToString();
            EntityConnectionStringBuilder entityConnectionStringBuilder = 
                                    new EntityConnectionStringBuilder(conString);
            OracleConnectionStringBuilder details = 
                            new OracleConnectionStringBuilder
                           (entityConnectionStringBuilder.ProviderConnectionString);
            string schemaValue = details.UserID;
            if (!schemaValue.Equals(_edmxDefaultSchema, 
                                    StringComparison.InvariantCultureIgnoreCase))
            {
                Schema = schemaValue;
                DbInterception.Add(new ReplaceSchemaInterceptor(_edmxDefaultSchema, Schema));
            }
            else
            {
                Schema = _edmxDefaultSchema;
            }
        }
    }
}

Using the DbContext

Linq or Lambda Expression

Linq or Lambda expression usages will be same as regular:

IPDB Db = new IPDB();
IPAPM_USER user = (from b in Db.IPAPM_USER
                   where b.USER_NAME == userName
                   where b.STATUS == "Active"
                   select b).FirstOrDefault();

Raw SQL Query

While using any raw SQL query, we are including the schema name in our query. At the new DB context class, we have a schema property (public readonly string Schema).

IPDB Db = new IPDB();
List<string> values = Db.Database.SqlQuery<string>
(String.Format("SELECT ColumnName FROM {0}.TableOrViewName", Db.Schema)).ToList();

References

Limitations

In our target DB, all objects belonged to the same schema (USER ID=IPWAVE)
Have tested things with:
- Entity add/update/delete (DB row insert/update/delete)
- Execute raw SQL query with EDMX/Entity Framework
Haven't worked with:
- Migrations: EDMX changes to Db
- EDMX refresh: Db changes to EDMX

History

4^th June, 2020: Initial version