How to choose an object relational mapping for .net

Question

(This must be a FAQ, so I expect it to be closed as duplicate, however I can not find a question that contains a good overview of the issues etc.)

What should someone consider when choosing an ORM for a .net system?

How are the above addressed by the ORM that are in common usage for .net systems.

(Witch stack overflow question should I link to in an answers when I says “consider using a ORM” for a .net question)

Does on one wish to provide a new answer and get the bounty?

Answer 1

This is the main question/answer I have been pointing people at in regards to choosing a .NET ORM:

NHibernate, Entity Framework, active records or linq2sql

I put a bunch of the "duplicate" type questions in my answer on that one. So there is plenty of reading you can do from a variety of different perspectives.

I'll also try to answer your specific questions:

What should someone consider when choosing an ORM for a .net system?

• You want to make sure it works with your database. If your database design was state of the art 15 years ago, many of the smaller and/or less mature ORMs won't work with your lack of keys, weird relationships, etc.

• You want to make sure it works with your database vendor. SQL Server support is pretty much always available, but other databases may or may not be supported by various ORMs (MYSQL seems to be the second most commonly supported database).

• If you want to generate your database based on your .NET classes, you need to make sure your ORM supports that.

• If you want performance, I guess you stay away from the bad performing ones, but those would never make a top 5 list, so as long as you go with an ORM that has some mainstream street cred, you are probably fine here.

• You need to make sure it has features you need supported: http://ayende.com/Blog/archive/2006/05/12/25ReasonsNotToWriteYourOwnObjectRelationalMapper.aspx

• Consider vendor lock-in and how easily you may or may not be able to escape it (for both the database vendor and the ORM vendor).

• Consider how much you want to spend.

• Consider what you want for code generation.

• Consider what you want for unit test support.

• Consider how much time you want to spend researching the various options.

• Consider the community that exists around the ORM.

There are plenty of things to keep in mind. I'm sure I could think of more. In the end, a lot of it is situational and you need to make your own decision what is best for you and your project.

How are the above addressed by the ORM that are in common usage for .net systems.

The only .NET ORM that I am aware of that meets pretty much all needs a .NET project could have (minus the more obscure database vendors) is NHibernate. It has been tested with a lot of "legacy" databases in a lot of different scenarios.

Answer 2

ORM Battle
A entire website dedicated to analysing and comparing various .NET ORM (Object Relational Mapping) products!

Bear in mind, though, that even the ORMBattle website only compares about 7 or 8 ORM's (although the ones it does compare, it seems to do so fairly thoroughly but see disclaimer below).

According to the SharpToolBox website, there are about 40 ORM's available! The list of ORM products, and their descriptions on the SharpToolBox website should give you a good start. See here: SharpToolBox Search Results for Object-Relational Mapping

Considering that pretty much every developer, and every application's data access needs can vary, finding a complete comparison between all of them will be difficult. This is probably the reason that over 40 different ORM products exist since there is no "one-size-fits-all" in the world of ORM.

Also, please bear in mind that any comparisons between ORM products may well be "flawed" since you can't always be sure that the various functionality of different ORM products is designed for the exact same purposes. A comment by AJ on this answer links to a blog post by Ayende Rahien who describes exactly this phenomenon when ORMBattle compared NHibernate to other ORMs.

I think the best approach is to look at the benchmarks, and read the descriptions of each ORM, but don't take those things literally, but rather interpret those figures based upon what your own application should be doing. For example, if your application primarily reads and writes database records one at a time, you'll probably use a completely different ORM product than an application that needs to process database records in batches.