My Considerations
Until now I used some custom orm tool (written by me), it was working with stored procedures, because my idea is that the best optimization you may accomplish in sql code. But now as the project increased and its fonctionality has blow up, I realise that in the future must be used some more adapted orm for supporting Domain Model and Data Mapper, with wich can be used in a more simple fashion the concepts of Unit of Work, Repositories etc. I indeed fell the necessity to pass to this kind of aproach because Transaction Script approach that I used in conjuction with Data Mapping leads in hard maintenace problems when in enterprise application you have sophisticated workflows and transactions to be performed.
I spent some time searching through the existing ORM Mappers (commercial and not only), of course my idea was to find a free mapper, but if another commercial tool is more powerful in the sence that it offer more access in using the enterprise patterns and it's more flexible, than it can be used instead.
My decision was in favor of NHibernate. First of all it's ported from java environment and has proven its viability over the years and the multitude of serios projects based on it. Of course we can describe different pros and cons of using it, and other OPRMs such as SubSonic or LBLGEN. But in fact with some more or less effort the things are pretty well done with NHibernate. Second of all it's not as complicated as it seems at first glance, you'll discover that at first examples that you'll do with it. Third it offers very clean and separated approach to perform the necessary operations, I was excited of the granularity that it offers comparing to other similar tools.
I dislike the fact that more and more vendors try to make their ORMs more simple to use by not allowing the developper to dive into some subtle things that in the end lead to optimisations, that's crucial for serios applications. From my point of view the best approach are stored procedures, because you may fine tune their granularity as you wish, but from the maintenace point of view it's not the best solution. After this approach the best one will be this NHibernate. During surfing the web you can find different tests on the efficiency of this tool compared to other such as EntityFramework and LINQ. I can emphasize that in practically all cases NHibernate generates less sql code than those mentioned. NHibernate is the most closely to pure ADO.NET efficiency. That's why I recommend it.
Performance Tests
Next I'll show you the results of some tests:
The results of the tests several unusual. First, I ran the queries of each test in one set and looked at Query cost for each query, which is about the whole set.
In the first test, I got the following results:
Classic ADO.NET - 15%
LINQ 2 SQL = 48%
Entity Framework - 23%
Active Record (NHibernate) - 15%
The interesting thing here is that LINQ 2 SQL is indeed slowerthan the request performed by EntityFramework.
In the second test, I have a complex query with joins for LINQ 2 SQL:
Complex queries with joins - 24%
A set of simple queries - 76%
As you can see, one request has won, it's not surprising. However, in general, we can say that in terms of databases losses are not as lazy loading too long.
The third test was for EntityFramework:
Complex queries with joins - 19%
A set of simple queries - 71%
As you can see, the results are practically similar.
The forth test was for NHibernate:
Complex queries with joins - 8%
A set of simple queries - 92%
So now you can see by yourself and make some base ideas on how it is.
List of .NET ORM Tools
Next I'll give a list with good .NET ORM Tools, and you may compare them by yourself: