Mar 062013
 
FacebookGoogle+StumbleUponLinkedInflattrPinterestReddittumblrTwitter

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.

ORMMapping

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.

Whatever approach is taken, please create different application layers, and especially for the data access, use the 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.
–Wikipedia

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:

  1. Is it free (and open source) or commercial
  2. What is the support given by the company or the community?
  3. Does it have an active community?
  4. Can it handle enterprise class ORM problems?
  5. Does it support LINQ?
  6. What databases does it support out of the box?
  7. A part direct table access, does it support querying of views, stored procedure or functions?
  8. Is there any caching mechanism?
  9. Does it support lazy-loading?
  10. 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:

Needless Repetition

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 itself

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.

Performance problems

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

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

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,…

BLToolkit

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

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

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?

zoran maksimovic
About the author:
My name is Zoran Maksimovic. I'm a passionate programmer and interested in everything about Software Development, Object-Oriented Design and Software Architecture. Feel free to contact me or to know more about me in the about section.
FacebookGoogle+StumbleUponLinkedInflattrPinterestReddittumblrTwitter

  7 Responses to “Microsoft.NET O/R mapper: choose your own!”

  1. Here is an ORM that works with Microsoft Access
    https://www.kellermansoftware.com/p-47-net-data-access-layer.aspx

  2. Zoran Maksimovic

    Here is an ORM that works with Firebird
    https://www.kellermansoftware.com/p-47-net-data-access-layer.aspx

  3. Hy zoran
    Been there, done that… I must say that when you compare NH against EF, EF is still years behind in terms of capabilities and OCP. But MS is catching up. If I need to go down the relational route today I wouldn’t choose a fullblown ORM anymore, often they introduce too much shitload like mapping and more. I personally prefer TinyORMs like SimpleData, massive, dapper or petapoco. They are closer to the baremetal and are easy to test. Although having to much dynamic magic has its downsides also. But if I’m not restricted by some weird enterprise policy (like you have to use XYZ DB) I would go for documents models and choose sn appropriate storing engine.

    • Hi Daniel,
      Fully agree with what you said.
      My feeling though is that EF is less invasive than NHibernate, and perhaps “simpler” to use. Microsoft is progressing quite fast and adding new features. NHIbernate is powerful but is becoming bloated. Some of the features, although powerful, you wouldn’t really use, or use in some very niche scenarios. But, again, knowing both of them is definitively an advantage, whatever technology one chooses. My bad is that I never had the time to really try the other listed libraries, and I am sure that other libraries would have their own advantage too!

      TinyORM’s are the “next thing”. I used PetaPoco, but wasn’t too much impressed by it. You have to work by convention, which still mean keeping the code in sync, but probably that is not avoidable. As you said, it has it’s downsides.

      Perhaps in one of the next posts, it would be interesting to compare what one would do by using EF vs SimpleData or others. Just for the sake of seeing what is the pro/cons.

      Cheers
      Zoran

    • As you allude to when you mention document models, I think many developers are still skipping the question which needs to be asked before thinking about an ORM. That is, “Do I need to use a relational database?”.

Leave a Reply