ADO.NET Native to EF6 Migration

A story written with experience of migrating .Net codebase from SQL Server to Multiple Databases support.

Why Migration?

For .Net based projects, SQL Server may not the only choice when it comes to enterprise. Considering Microsoft licensing change from processor based to core based in 2012, its not the pocket friendly option any more and demand for other databases has increased.

That forces many of the enterprise applications to support various other databases which offer great security features and licensing options.

Due to increased demand, one has to migrate to Object-Relational Mapping(ORM) framework or equivalent implementation based on needs.

ORM or Native Drivers:

Native Driver based implementation expects dynamic switching/multiple code bases. So, new database support may require changes to code base.

We have evaluated two ORM frameworks nHibernate and Entity Framework (EF6). We happened to choose EF6 considering better long term support for enterprise.

In my case, I have chosen EF6 with Code First approach. Code First approach enables us avoid database specific configuration files.

Performance Results (Native SQL Provider vs EF6 ORM):

• Performance tests are run on basic identical servers.
• ORM initialization is performed before running performance test.

25k iterations Test Result

50k iterations Test Result

Select with Pagination Test Result

Advantages:

• EF Providers from respective DB owners is a huge advantage
• Compiled queries with LINQ (Less run-time surprises)
• No manual SQL statements
• Automatic Query generation, which is SQL Injection Safe

Disadvantages:

• Performance cost is upto 2X of ADO.NET without ORM. This can be covered with few optimizations, but it does not match with native performance.
• No control over generation of query. This is acceptable, as long as support from providers is good. So far, Oracle, PostgreSQL and SQL Server seems very active and stable with latest releases.

Even though performance results are not encouraging, its features for multiple databases and fact that we can stick to one code base encouraged us to choose EF.

Things to be taken care during migration:

1. Unlike SQL server, most other databases are case-sensitive for table names and column names. In order to overcome these issues, we choose to create tables and columns by forcing case sensitivity same as Model.
2. In case your application uses Forms Authentication or similar which demands native providers. This part needs to be rewritten with a custom provider.
3. Free Style SQL queries has to be converted to LINQ. No more string based queries
4. Say No to most of the Stored procedures, as databases like PostgreSQL does not support them. By adding certain blocks, will force your app to stick to fewer databases.
5. For .Net applications, its common to use SSRS based Reports(Written in RDL). If that is the case, one has to convert all RDL based reports to RDLC reports.

Summary:

Essentially, EF6 has performance overhead compared to native drivers. But, it provides a great way to code using LINQ and achieve database portability. Learning curve for EF6 is minimal for a person who is familiar with LINQ.

Happy Coding!