ORM/Persistence layer Advice

Question

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.
For this project, I really don't care on how the data is persisted, as long as it can be queried and stored with a reasonable speed and most importantly with simple queries.
Concurrency should be handled seamlessly (the front-end will be on another tier and there'll be several simultaneous users, although not necessarily working on the same data) and the less I have to focus on the data layer (easy queries, automatic lazy loading etc) the better.
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.
• XPO: anything but intuitive, very slow, concurrency issues, not POCO
• 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

I've also looked at MongoDB and CouchDB but in those cases the catches with related objects looked like they required too much testing before getting things right. Besides none of them offers strongly typed queries.

Thanks in advance for your suggestions!

Answer 1

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

MyEntityCollection.DeleteAll( new MyEntity {Condition = value} ); 

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 be best for!). I used both in this company and hated both. The decision for new projects remained LINQ to SQL, and doing all we can to overcome it's limitations. This website StackOVerflow itself runs on top of it!!! 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 no longer 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 D 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 happened 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.