I’ve been using a few ORMs with a few projects, including NHibernate, SubSonic, Linq-to-SQL (L2S) and Entity Framework (EF) and even Strongly-Typed DataSets. Each provides an abstraction from the database, a few have Visual Studio integration, and they all simplify data storage for you, but I’ve found some are definitely better than others.
Most of the ORMs I’ve used required a lot of configuration in XML files. Probably the worst offender in this sense is NHibernate. L2S, EF and DataSets are bad too, but at least they have tooling in Visual Studio to make it easier to use and hide the XML from you. In this respect, the Fluent NHibernate project is a life-saver for anyone using NHibernate.
POCOs and the Repository Design Pattern
Another thing most ORMs provide you with is an ability to use simple POCOs or Plain Old CLR (or C#) Objects. Ordinary DataTables and Strongly-Typed DataSets come with a lot of extra “clutter” to them, this is especially visible when you’re interacting with them using IntelliSense inside Visual Studio. The clutter appears as all the baggage that comes along with DataTables, as well as the fact you have to use DataAdapters, and fetch row objects (i.e. UserTableRow).
It is really nice to just have a simple class that can do what you need it to within the “domain” (following the Domain Driven Design ideas). It simplifies your data layer model significantly, and of course reduces a lot of clutter.
NHibernate and SubSonic are the clear leaders here, NHibernate even more so, but SubSonic can regenerate mappings after you’ve made changes to your database.
Simple Database Schema Creation
Another thing I like to see in an ORM tool is the ability to take your data-layer mappings and generate a database schema from that. The primary reason is that database versioning during development is hard. Keeping everyone on the same page with databases is difficult and making each developer use the same database is hard to impossible. If your ORM can just create and/or update your database schema for you off the bat, keeping each developer up-to-date is a breeze. No more “why is my app crashing?”, “where is that SQL script?”, “who did this?” shouting over the cubicle walls, instead you just run the update scripts and you’re all set.
As far as I know there is only one clear winner here and that is NHibernate; L2S, EF and Strongly-Typed DataSets don’t have this functionality at all. I believe that the 3.x version of SubSonic does have this now, and version 2.x had a version of migrations (that may be obsolete in version 3).
My Pick for Best ORM
I’ve personally switched from the SubSonic camp to the NHibernate camp in the last year. With Fluent NHibernate and guidance from the S#arpArchitecture projects all real difficult barriers into NHibernate have fallen. SubSonic is an awesome ORM and does help you get up to speed quickly, but it does feel like it isn’t developer or maintained quite as much as NHibernate is, and the differences between 2.x and 3.x versions was so great as to almost be intimidating to me (almost)!
Entity Framework is good, but I’ve had problems with the tooling and updating the mapping schema in the projects I’ve used it in. To me, there is very little programmatic difference with Linq-to-SQL, in fact EF seems to have more functionality. According to what I’ve read, it will have Database Schema Generation in the .NET 4.0 version, but NHibernate 2.1 already has LINQ support (via another library) so EF is really still just playing catch-up.
Strongly-Typed DataSets are pretty much an obsolete technology as far as I’m concerned – from the .NET 1.0/1.1 days. They do provide some simplification over bare metal ADO.NET, but I don’t find them as easy to use as NHibernate or any of the other ORMs I’ve discussed so I’ve seen little benefit in modern projects.
While I know there are more options out there than just the ones I’ve pointed out, NHibernate is the tool that best represents what I’m looking for in an ORM.