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" />
</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")
{
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.
