ORM/Persistence layer Advice

Question

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

Have you thought about using an object oriented database such as db4o. Although I have never used it, I found it quite interesting when I discovered it:

It supports LINQ querying, uses POCOs, and if I understood correctly, the data is simply stored in a file (no installation of a database is required).

Some links: tutorial, forum post

Answer 2

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 Tres).

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

Answer 3

LLBLGen.

If you think your going to find something that ticks all your boxes then you could be waiting for a long time.

Answer 4

Take a look at EntitySpaces. I can't recommend from my own personal experience, but it works great for one of my colleagues (who is not into sof...)

Answer 5

With all respect, I tend to absolutely disagree with your assessment of NHibernate weaknesses.

I think XML mappings are extremely simple and intuitive to create. Adding reference to NHibernate.dll and creating SessionFactory is a piece of cake too. Maintenance and change management is significantly simplified. Session Factories and Sessions are very easy to understand and deal with.

Overall I think you're being emotional and not rational with your assessment.

Answer 6

Take a look at Aspectize here

Answer 7

For me, the answer has turned out to be LLBLGen Pro. In addition to it's capabilities, you gain a dedicated support team who cares about helping you get your project working and should you discover a bug, in most cases, you'll get a fix in hours or days. This can't be minimized, as it allows you to continue your work rather than waiting for a release (months?) or fixing the bug yourself.

Answer 8

Take a look at DataObjects.Net:

Advantages:

• Easy to design business model using simple classes and attributes
• Database schema is automatically generated in runtime - so you don't have to care on how data is persisted
• Automatic lazy loading, transparent persistence, etc...
• Really good LINQ implementation
• High performance

Disadvantages:

• Not POCO

Answer 9

Write your own ORM

It might sound crazy but you can write your own ORM. Sometime back in a project the client did not want to use 3rd party tools or libraries and for that reason, we ended up writing our own ORM which served specific objectives for us. You can decorate your objects' classes and properties with Attributes to map them with Database tables and fields. Have base class for all the business objects and do database operations on the objects using reflection.

This way you can build your own tiny ORM to suit the particular objectives you want.For your reference It may somewhat look like this.

[TableMapping("Users", "User", "Users")]
public class User : EntityBase
{
    #region Constructor(s)
    public User()
    {
    }
    #endregion

    #region Properties

    #region Default Properties - Direct Field Mapping using DataFieldMappingAttribute

    private System.Int32 _UserId;

    private System.String _UserName;

    [DataFieldMapping("UserID")]
    [DataObjectFieldAttribute(true, true, false)]
    [NotNullOrEmpty(Message = "UserID Is Required.")]
    public override int Id
    {
        get
        {
            return _UserId;
        }
        set
        {
            _UserId = value;
        }
    }

    [DataFieldMapping("UserName")]
    [NotNullOrEmpty(Message = "Username Is Required.")]
    public string UserName
    {
        get
        {
            return _UserName;
        }
        set
        {
            _UserName = value;
        }
    }

    #endregion

    #region One-To-Many Mappings
    #endregion

    #region Derived Properties
    #endregion

    #endregion
}

Answer 10

You might take a look at Telerik ORM. I haven't used it in production but it was based on a Java ORM\ODB called POET that worked very well for me several years ago. Since it uses assembly enhancement it offers a much more transparent experience than some of the products you rejected above.

In terms of a pure ODB FastObjects by Versant is probably worth a look.

Good luck - let us know what you decide to go with.

Answer 11

I've used LLBLGenPro, NHibernate and a few Object Databases.

My first recommendation would be for NHibernate with FluentNhibernate to handle the mappings.

I found 90% of the difficulty related to using NHibernate was directly tied to the XML mappings. When I switched over the FluentNhibernate, the pain went away.

The other 10% of the difficulty is simply ignorance; I didn't understand it. And that's not NHibernate's fault.

LLBLGenPro: I have no idea what the Linq support is like now, but before Linq was supported it was a PITA to write queries. I read the docs and I got it, but my coworkers did not and complained endlessly about the complexities of prefetch paths and the like. Plus, like you said - not POCO. Add on the cost and I'd say it's way more trouble than it's worth.

If it's not a client-server architecture, I'd suggest db40. I used it on a small, private project and loved it. Easy to use. Great little object database.