Click here to Skip to main content
12,893,816 members (53,412 online)
Click here to Skip to main content
Add your own
alternative version

Stats

6.4K views
154 downloads
15 bookmarked
Posted 7 Dec 2015

Entity Framework Multi DB Support

, 7 Dec 2015 CPOL
Rate this:
Please Sign up or sign in to vote.
An alternate method for multi db provider support for entity framework

Introduction

Databases are very important for some kind of applications. Back in old days, systems are strict to a specific database. Today, applications can run on multiple platforms but generally we build our applications to selected database. To support multiple providers, we have to do lots of coding.

First, let's look at problems.

  • MS SQL table and column names are case insensitive. Generally pascal case is used.
  • Oracle's default is all upper case. If you use any lower case, a quotatin (") is added to names. And it is very painful while writing queries.
  • PostgreSQL's default is all lower case. If you use any upper case, a quotatin (") is added to names. And it is very painful while writing queries too.

In OOP in C#, we generally use pascal case for objects. If you use Oracle or PostgreSQL a mapping is necessary. That's OK, but problem starts when you want multiple provider support. How to map a field for different providers?

There're many methods to support multiple providers. But, as a lazy developer, I wanted a simple solution. I hope you like it.

Special thanks to Mr. A. Burak Erbora for his contributions.

<!---------------------------------------------------------->

First things first

First things first. Let's create a test table. Very simple. An id and a test column.

MS SQL:

Oracle:

PostgreSQL:

Now, It's time to add NuGet packages.

<!---------------------------------------------------------->

Config things

Oracle modifies config file correctly but in PostgreSQL you may have problems. You can check config below:

<providers>
  <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
  <provider invariantName="Oracle.ManagedDataAccess.Client" type="Oracle.ManagedDataAccess.EntityFramework.EFOracleProviderServices, Oracle.ManagedDataAccess.EntityFramework, Version=6.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
  <provider invariantName="Npgsql" type="Npgsql.NpgsqlServices, EntityFramework6.Npgsql" />
</providers>
<DbProviderFactories>
  <remove invariant="Oracle.ManagedDataAccess.Client" />
  <add name="ODP.NET, Managed Driver" invariant="Oracle.ManagedDataAccess.Client" description="Oracle Data Provider for .NET, Managed Driver" type="Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.ManagedDataAccess, Version=4.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
  <remove invariant="Npgsql" />
  <add name="Npgsql - .Net Data Provider for PostgreSQL" invariant="Npgsql" description=".Net Data Provider for PostgreSQL" type="Npgsql.NpgsqlFactory, Npgsql" />
</DbProviderFactories>

And also our connection strings:

<connectionStrings>
  <add name="TestConnectionString" connectionString="Data Source=.\SQL2014;Initial Catalog=TestDb;Integrated Security=True;" providerName="System.Data.SqlClient" />
  <!--<add name="TestConnectionString" connectionString="DATA SOURCE=127.0.0.1:1521/XE;PASSWORD=1234;PERSIST SECURITY INFO=True;USER ID=TEST_USER" providerName="Oracle.ManagedDataAccess.Client" />-->
  <!--<add name="TestConnectionString" connectionString="Server=127.0.0.1;Database=test_db;UserId=test_user;Password=1234;" providerName="Npgsql" />-->
</connectionStrings>
<!---------------------------------------------------------->

Example entity

Let's create our entity for table.

Main thing is, to use single entity and use it in multiple providers. How to do it? If we have a structure below, coding will be very easy.

[DbTable(msSqlName: "Table1", oracleName: "TABLE1", postgreSqlName: "table1")]
class Table1
{
    [DbColumn(msSqlName: "Id", oracleName: "ID", postgreSqlName: "id")]
    [Key]
    public int Id { get; set; }

    [DbColumn(msSqlName: "TestCol", oracleName: "TEST_COL", postgreSqlName: "test_col")]
    [Required]
    public string TestCol { get; set; }
}

You'll see two new attributes: DbTable and DbColumn. These are the key points.

While defining the class and properties, we'll give three different names for three different providers.

<!---------------------------------------------------------->

Using the code

First, start with DbColumn.

class DbColumnAttribute : ColumnAttribute
{
    public DbColumnAttribute(string msSqlName, string oracleName, string postgreSqlName)
        : base(Tools.DbProvider == DbProviders.MsSql
            ? msSqlName
            : Tools.DbProvider == DbProviders.Oracle
                ? oracleName
                : Tools.DbProvider == DbProviders.PostgreSql
                    ? postgreSqlName
                    : null) {}
}

As you see, its base class is ColumnAttribute. While calling base() method we'll set given name by provider defined in the connection string (Tools.DbProvider is a helper method).

And DbTable attribute. It has additional code for supporting schemas.

Databases have schemas, in MS SQL it is "dbo" but we generally omit it, if you're not familiar with Oracle, schema is also the user name, which is very important.

class DbTableAttribute : TableAttribute
{
    public DbTableAttribute(string msSqlName, string oracleName, string postgreSqlName, string msSqlSchema = null, string oracleSchema = null, string postgreSqlSchema = null)
        : base(Tools.DbProvider == DbProviders.MsSql
            ? msSqlName
            : Tools.DbProvider == DbProviders.Oracle
                ? oracleName
                : Tools.DbProvider == DbProviders.PostgreSql
                    ? postgreSqlName
                    : null)
    {
        switch (Tools.DbProvider)
        {
            case DbProviders.MsSql:
                Schema = (msSqlSchema ?? ConfigurationManager.AppSettings["DefaultDbSchema"]) ?? "dbo";
                break;
            case DbProviders.Oracle:
                Schema = (oracleSchema ?? ConfigurationManager.AppSettings["DefaultDbSchema"]);
                break;
            case DbProviders.PostgreSql:
                Schema = (postgreSqlSchema ?? ConfigurationManager.AppSettings["DefaultDbSchema"]);
                break;
        }
    }
}

Generally, in a project we have a single schema. to not to write it every time, and also schemas can be different in development and production environment, it's a good idea to put schema name in config file.

And the DbContext:

class TestDataContext : DbContext
{
    public TestDataContext() : base("name=TestConnectionString")
    {
        // Disable auto migrations
        Database.SetInitializer<TestDataContext>(null);

        Configuration.AutoDetectChangesEnabled = true;
        Configuration.LazyLoadingEnabled = true;
        Configuration.ProxyCreationEnabled = true;
    }

    public virtual DbSet<Table1> Table1 { get; set; }
}

Nothing special here. :-)

<!---------------------------------------------------------->

Little testing

MS SQL:

<appSettings>
  <add key="DefaultDbSchema" value="dbo" />
</appSettings>
<connectionStrings>
  <add name="TestConnectionString" connectionString="Data Source=.\SQL2014;Initial Catalog=TestDb;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>

Oracle:

<appSettings>
  <add key="DefaultDbSchema" value="TEST_USER" />
</appSettings>
<connectionStrings>
  <add name="TestConnectionString" connectionString="DATA SOURCE=127.0.0.1:1521/XE;PASSWORD=1234;PERSIST SECURITY INFO=True;USER ID=TEST_USER" providerName="Oracle.ManagedDataAccess.Client" />
</connectionStrings>

PostgreSQL:

<appSettings>
  <add key="DefaultDbSchema" value="public" />
</appSettings>
<connectionStrings>
  <add name="TestConnectionString" connectionString="Server=127.0.0.1;Database=test_db;UserId=test_user;Password=1234;" providerName="Npgsql" />
</connectionStrings>

<!---------------------------------------------------------->

Q&A

  • Q: So, this seems that, you're not using a designer, does it code first?
  • A: Yes. These attributes (DbTable and DbColumn) are custom attributes and they're not supported by EF designer. So to use this method, we have to create our tables manually. But this does not mean it is code first, create database first and disable code first migrations.
  • Q: Can we automatize it?
  • A: Well, some ideas came to my mind, such as, to create a T4 template which will connect both three providers, get table and columns and create attributes with them, but it's just an idea right now...
  • Q: Is MySQL supported?
  • A: Not in this specific project, but it can. Just add provider to project and modify attributes.
  • Q: Which providers can we use?
  • A: Any provider that can Entity Framework support.
  • Q: I have code first now and lots of configuration on it. Can I use this?
  • A: Sure. These attributes just changes table and column name mapping, not other configurations.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Efe Erdoğru
Software Developer (Senior)
Turkey Turkey
No Biography provided

You may also be interested in...

Pro

Comments and Discussions

 
QuestionNeed for Db first approach Pin
Member 1180012013-Feb-17 3:31
memberMember 1180012013-Feb-17 3:31 
GeneralMy vote of 5 Pin
Cahit SEÇMEN3-May-16 10:31
memberCahit SEÇMEN3-May-16 10:31 
Questionquestions Pin
Uluç Bilecen4-Mar-16 9:08
memberUluç Bilecen4-Mar-16 9:08 
AnswerRe: questions Pin
Efe Erdogru12-Mar-16 0:39
memberEfe Erdogru12-Mar-16 0:39 
QuestionGood article Pin
Member 116823358-Dec-15 4:24
memberMember 116823358-Dec-15 4:24 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170424.1 | Last Updated 7 Dec 2015
Article Copyright 2015 by Efe Erdoğru
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid