Which ORM? LINQ To SQL, Entity Framework? LLBLGen? NHibernate?…?

While I was planning to write about the same topic and have the draft ready in my Windows Live Writer waiting to complete, I found an interesting question in StackOVerflow and couldn’t just resist to answer:

ORM/Persistence layer AdviceORM

The question starts with:

I’m starting a new project and I’m looking around for either a very good ORM or for a non-SQL-based persistence layer.

Then follows up with a REALLY GOOD summary of what he believes about each known ORM he knew out of his own findings and search. I advice you to go read it.

However, all this investigation didn’t get him to a single choice answer. And I can’t blame him. This is one fo the questions that will remain for so long without a single answer, or maybe having the popular “It depends” answer.

I have had a LONG research in this topic as well. I have read for so long (and watched videos/casts) to make sure of the best usage of many ORMs and then used them sometimes in test projects sometimes in production, and I wanted to share my thoughts based on this. I posted a long answer there on the question in StackOverflow, and I want to share this answer with you here. I may also have a second part of this post based on my existing Windows Live Writer draft, but, based on my previous times, I think I won’t!

Let me first quote some parts from the question itself:

I also want to avoid at all cost having to mess with string-based queries so tools supporting LINQ or otherwise intuitive and possibly strongly typed queries get a big bonus.
Finally working with POCO objects is another thing I’d really want to do
Here’s a list of products I’ve evaluated and why they don’t fit, just so that I don’t see any advice about using those:

  • NHibernate: crazy xml stuff, too much set up, high maintenance complexity and cost for model changes, session factories are messy and don’t fit well with my needs
  • Castle ActiveRecord: NHibernate based, little documentation plus some problems related to NHibernate still apply. Furthermore, to get decent models it takes so many attributes that one is better off creating the schema manually, and the way relations are handled is a shame.
  • Linq To SQL: missing POCO objects and according to MS it won’t improve much overtime (EF is what they’re committed to)
  • Entity Framweork: although in v4 POCO objects are possible, they’re still pretty hacky and force you into doing too much manual work to set things up. Besides, v4 is just a beta
  • LLBLGen Pro: good, especially with SelfServicing adapters, but not POCO. Also, the LINQ provider isn’t perfect yet. Finally, deleting a group of objects is not possible via LINQ which results in mixing APIs (one of which is far from intuitive) and that I don’t like.
  • SubSonic SimpleRepository: for a few minutes I thought I was dreaming. The deam came to an end as I figured out how the thing didn’t handle relationships
  • Here was my answer:

    If you can afford LLBLGen license, go for it.

    I seriously don’t like LINQ query-syntax the more I work with it (although I LOVE the language features related to it like Extension Methods and Expression Trees).

    I loved it at first like everybody else, but being uncertain whether [[ where employee.Name.StartsWith(“John Smit”) ]] in that XYZ LINQ provider will be done in SQL statement or in LINQ to Objects (after the SQL returns all results), and whether [[ user.Roles.Contains(role) ]] will at all work or not is a big step behind.

    LLBLGen can make deleting all items without loading them as easy as

    This is pretty simple and I like it. You get lazy loading and you set eager/deep loading by default and/or per query using Prefetch API. You can compose and construct dynamically (and easily) any filter/sort/loading at infinite levels. It’s very nice.

    There are only two problems about LLBLGen: first, it’s price not all companies would love to pay especially given the hype Microsoft alternatives have. Second, the naming convention although standard in RDBMS theories) like PredicateFactory instead of “where” or “filter” and Prefetch instead of deep loading and even SortExpression instead of orderby, those all are a little scary to a developer working with it for the first times, but soon you learn to love them, given the power and ease they give. There are talks about POCO support in LLBLGen 3.0. I cannot tell about it because I don’t know.

    Now given I no longer work in a company that uses LLBLGen, the company uses LINQ to SQL mainly because it’s “proven” in so many projects without big failures (unlike EF 1, which is lacking even LINQ features in LINQ to SQL and has very bad performance and can be quite limiting in advanced mapping – which it should have been best for!). This website StackOVerflow itself runs on top of it!!! I used both in this company (EF after L2S) and hated both. The decision for new projects remained LINQ to SQL, and doing all we can to overcome it’s limitations. You can work around it to do SEMI-POCO (you still need to use some L2S related types when it comes to associations).

    I also do some small projects at home. Since I nolonger have LLBLGen license, I decided to learn NHibernate and use it along with Fluent NHibernate and LINQ To NHibernate. I have learned through this that NHibernate is VERY strong. It changed how I work by some features like updating DB schema automatically (I never touched the DB almost when using it). LINQ provider (in NHibernate Contrib project) is quite lacking sometimes but there is the unreleased source code of NHibernate itself contains a better LINQ provider (haven’t tried it yet). The “Session” in NHibernate has problems when you are doing web development similar to those related to DataContext in L2S or ObjectContext in EF (LLBLGen doesn’t suffer from those thanks to self tracking entities).

    The biggest problems I had with NHibernate though was ability to find information. Too many pieces that should be put together in certain way and not much guidance can include advanced information for both mapping and querying. If not I had a friend (Tuna Toksoz , @tehlike on twitter) who happended to be a committer in NHibernate project source code, I’d really be in serious trouble.

    The moral I learned was: If you want something that just works and a bit basic use Linq To Sql or SubSonic, if you want something in the middle and your production environment can afford BETA .NET version (given golive exists) use Entity Framework 4.0, if you want something very powerful and can afford the hard learning process go to NHibernate, AND, BEST OF ALL, if you can afford LLBLGen, USE IT.

    Let me know your own thoughts on the topic.

    Share With Friends:

    P.S. Please help me out by checking this offer, then look below for a small Thank You.

    How did I learn that?

    As a bonus for coming here, I'm giving away a free newsletter for web developers that you can sign up for from here.
    It's not an anything-and-everything link list. It's thoughtfully collected picks of articles and tools, that focus on Angular 2, ASP.NET 5, and other fullstack developer goodies.

    • I am a sole developer but I bought the licence for LLGLGen a few years ago back in version 1. I upgraded to v2 then 2.6 and I will pay again for v3 when it comes out. It pays for itself in every project I do, and the support is probably the best I have ever seen.
      I do recommend it often to other developers. LLBLGen and Resharper are the two tools I cannot live without.

    • Like Richard and Mohamed I love working with LLBLGen. It’s not perfect, but it it’s getting closer with each release. It provides an extreme productivity boost, the support forums are responsive, and the support team cares about their users. Find a bug and you’ll usually get a fix within days. Check it out, you’ll be happy you did.

    • Completely agree with Richard.. I cannot live without Resharper and LLBLGen.

    • Interesting! I always keep surprised with the size of LLBLGen users community compared with what I used to think!

      Actually, I keep wishing Microsoft bought the company instead of making Entity Framework :S. That would have caused a lot of us much headache. I know, the typical “baby .NET developer waiting for Mom Microsoft to to feed his career”…

    • http://

      Have you looked at Lightspeed by Mindscape?


    • LLBLGen Pro FTW. You can’t beat the support and the flexibility of the product. By the time EF is ready for prime time, LLBLGen Pro will be light years ahead. I’ve used for small projects and large, high availability projects without any problems whatsoever.

      I bought a license for my personal projects and think it’s worth every penny.

    • http://

      I think that NHibernate is a great long term investment. I took the time to learn it and I never looked back.

      It is very powerful and has so many features that it can be confusing for a new user. In that case, I recommend getting the book NHibernate in Action.

      You can have a painless start using attributes for mapping (no xml needed) and LINQ (no new Query API to learn).

      Once, you are confortable with it, you will welcome its advanced features (great support for legacy databases, contributions related to sharding, lucene full text search, GIS integration, etc.)

    • Based on various comments I saw on the web NHibernate seems to have Linq issues. I am told that writing a linq provider is a large undertaking which could account for these issues.

    • For LINQ to NHibernate, it’s still quite early.
      Read the answers from NHibernate group to my question about it here:

      Linq To LLBLGen Pro is a huge investment though (although as mentioned above that I like LLBLGen Pro API more). Frans Bouma (the man behind LLBLGen Pro) blogged about it in over 14 detailed parts here:

      But still, NHibernate is way better than Microsoft LINQ Providers (L2S, EF). but still, LLBLGen is better – if your company can invest in it, and I know it’s not the case for some companies.

    • http://

      Just wanted to give my 2 cents. I have used NHibernate, LLBLgen and i was the original creator of some very popular code smith templates for data access…

      LLBLGen wins hands down. The learning curve is actually pretty low. For instance, i decided to write a complex query in Nhibernate, it took me 40 minutes. I decided to try the same one in LLBLGen, took me 5 minutes…i had no idea how to write query in either ORM.

      That was 2 years ago, and i have never looked back!

    • http://

      NetTiers is a CodeSmith template-based ORM layer which is absolutely EXCELLENT. I’ve tried LLBLGEN and Subsonic, and prefer NetTiers ten times over, especially when it comes to the number of features it provides and how intuitive it is to use.

    • I used .NET Tiers also (although long time ago). I loved especially its generated ASP.NET forms.

      However, in an N Tier design when you have complex queries it doesn’t always fit my needs.

    • http://

      I have used Subsonic, NHibernate, LinqToSql, DAAB, EF, and NetTiers (and a couple other ORMs in Java), and LLBLGen is by far my favorite. I like the template editor as well (almost as good as CodeSmith in my opinion), and the designer is absolutely fantastic. LLBLGen is a great investment and makes development against various databases extremely productive (Oracle and SQL are the primary databases I’ve worked against with LLBLGen). The other ORMs are good as well, and I’m not slamming them at all, but LLBLGen makes refactoring code really easy. I also really like the LLBLGen SelfServicing model, although the Adapter model seems to be the most widely used.

    • http://

      If your database can only be accessed through stored procedures then all ORM’s which depend on dynamic SQL are out. As a policy, many companies do not allow dynamic SQL as a security measure.

      While some developers like LLBLGen, some companies frown on buying several licenses unless there’s a general consensus between the developers and there’s a real ROI from using it.

      There are other factors too.