Microsoft.NET O/R mapper: choose your own!
Only few years ago I was not such a big fan of Object-Relational mapping tools (ORM), but now I have to admit that when starting a new project I’m always thinking whether it makes sense to use it or not.
Compared to “traditional” coding techniques, Object-Relational mapping often reduces the amount of code that needs to be written, but I would add as well that a new kind of problems arise too, especially in some more complex scenarios. So particular attention should be taken on the cost/benefit side, like for anything else.
Repository pattern. Repository Pattern offers a great way of decoupling the data access from the application logic.
So, the golden rule is: do not mix the business logic with the data access code. [<-tweet this]
What is an Object-Relational Mapper
Let’s start with a simple definition.
Object-relational mapping (ORM, O/RM, and O/R mapping) in computer software is a programming technique for converting data between incompatibletype systems in object-oriented programming languages. This creates, in effect, a “virtual object database” that can be used from within the programming language.
List of things to consider
There are many things that one should consider before adopting a particular library. Here is perhaps a short list of questions to be answered before choosing:
- Is it free (and open source) or commercial
- What is the support given by the company or the community?
- Does it have an active community?
- Can it handle enterprise class ORM problems?
- Does it support LINQ?
- What databases does it support out of the box?
- A part direct table access, does it support querying of views, stored procedure or functions?
- Is there any caching mechanism?
- Does it support lazy-loading?
- Does it support batching mode (updating many items at once)?
ORM Mapping Costs
As mentioned earlier, there are some costs to consider when adopting an ORM. Please consider the following:
Usually the table structure and table relations are stored both in the DB and in the ORM mapping files. This can cause a maintenance problem as one need to make sure that both versions of the meta-data don’t get out of sync. So, every change on the database usually means recompiling the application.
Mapping it is not always a trivial task. Because of the “Object-relational impedance mismatch” there could be issues in transforming the database structure in the object oriented compatible way.
As the ORM tool is usually generating the SQL code to be executed on the database, this queries are often not fully optimized, and often could be rewritten in a much more optimized way if written manually. I mentioned often, as this is not always the case. Usually this can be solved by instead of querying the table directly, to query some specialized views that could optimize some code.
Microsoft.NET ORM tools
There is a quite big list of ORM tools, and here I am mentioning the ones I believe are among the most important ones.
I personally have experience with the Entity Framework and NHibernate, and I would leave the tough job of choosing one to you directly.
Microsoft’s Entity Framework
Entity Framework (EF) is an free and open source object-relational mapper that enables .NET developers to work with relational data using domain-specific objects. It eliminates the need for most of the data-access code that developers usually need to write.
Entity Framework allows you to create a model by writing code or using boxes and lines in the EF Designer. Both of these approaches can be used to target an existing database or create a new database.
Entity Framework is actively developed by the Entity Framework team which is assigned to the Microsoft Open Tech Hub and in collaboration with a community of open source developers.
Linq To SQL
LINQ to SQL is a free component of .NET Framework version 3.5 that provides a run-time infrastructure for managing relational data as objects.
In LINQ to SQL, the data model of a relational database is mapped to an object model expressed in the programming language of the developer. When the application runs, LINQ to SQL translates into SQL the language-integrated queries in the object model and sends them to the database for execution. When the database returns the results, LINQ to SQL translates them back to objects that you can work with in your own programming language.
I would personally not use Linq to Sql as it seems that not much effort is placed into enhancing the current technology.
NHibernate is a free object-relational mapping (ORM) solution for the Microsoft .NET platform: it provides a framework for mapping an object-oriented domain model to a traditional relational database. Its purpose is to relieve the developer from a significant portion of relational data persistence-related programming tasks. NHibernate is free as open source software that is distributed under the GNU Lesser General Public License. NHibernate is a port of the popular Java O/R mapper Hibernate to .NET.
DataObjects.Net is a commercial persistence and object-relational mapping framework for Microsoft.NET. It allows developers to define persistent objects as well as business logic directly in C#, Visual Basic or F#. The persistent objects can be retrieved by LINQ queries. Persistent data can be stored in SQL Servers. In contrast to many other ORM frameworks the database model is generated and maintained automatically.
It supports most of the mainstream databases.
Telerik’s OpenAccess ORM
OpenAccess ORM is Telerik’s free object-relational mapping tool that will generate the data access layer for your applications after just a few mouse clicks. All you have to do is to create the data model of your application and the tool will do the rest!
OpenAccess ORM supports: Database-First (Reverse) Mapping, Model-First (Forward) Mapping, Round-Trip (Mixed Mode) Mapping, Code Only Mapping (Fluent API), Mapping Stored Procedures, Views and Tables.
It supports most of the mainstream databases (Microsoft SQL Server, Oracle, Sybase, MySql, PostgreSQL, Firebird,…
Business Logic Toolkit is a set of components to simplify .NET application development. BLToolkit is provided as source code that you can use “as is” or customize for your applications. It is written in C# and compatible with .NET Frameworks 3.5 and 4.0, Silverlight 4, and Mono.
LBLGen Pro is a data-access solution.
You use the LLBLGen Pro Designer to create the entity/domain model, define the mappings and generate source-code for one of the four supported O/R mapping frameworks: Entity Framework, NHibernate, Linq to SQL or the LLBLGen Pro Runtime Framework.
You use the generated source-code combined with the O/R mapping framework of your choice to access your database and consume the data in that database.
When, for whatever reason, you have to change the O/R mapper for your project, you can simply switch the chosen target framework in the LLBLGen Pro designer, re-generate code and you’re done (give or take some minor changes if the newly chosen O/R mapper doesn’t support a feature the old one does). Of course your own code has to be migrated, but your entity model, mappings and the like all stay the same.
LinqConnect is a commecial, fast, lightweight, and easy to use LINQ to SQL-compatible ORM solution, supporting SQL Server, Oracle, MySQL, PostgreSQL, and SQLite. It allows you to use efficient and powerful data access for your .NET Framework, Metro, Silverlight, or Windows Phone applications supporting Code-First, Model-First, Database-First or mixed approaches.
nqConnect was developed closely to LINQ to SQL and retains full compatibility with it. Interface of the LinqConnect classes is compatible with LINQ to SQL ones. If you are a LINQ to SQL developer, you don’t need to learn much and can start developing with LinqConnect immediately.
Unlike LINQ to SQL, LinqConnect is an actively developed and supported product, and it offers a number of benefits over LINQ to SQL. It supports more database servers, more development platforms, more LINQ features, more mapping kinds, provides better performance, etc.
Wrap it Up
There is a large number of ORM implementations for Microsoft.NET and is not always easy to choose the right one. We have seen that even the fact of using an ORM tool has some additional costs when it comes to the application design and maintenance.
All in all, I see in general more positive than negative aspects, once the learning curve and familiarity with the tool is not the problem.
Please suggest or comment what is your experience with the ORM tools, and why would you would(n’t) use one?