What ORM for .net should I use?

Question

I'm relatively new to .NET and have being using Linq2Sql for a almost a year, but it lacks some of the features I'm looking for now.

I'm going to start a new project in which I want to use an ORM with the following characteristics:

• It has to be very productive, I don't want to be dealing with the access layer to save or retrieve objects from or to the database, but it should allows me to easily tweak any object before actually commit it to the database; also it should allows me to work easily with a changing database schema
• It should allows me to extend the objects mapped from the database, for example to add virtual attributes to them (virtual columns to a table)
• It has to be (at least almost) database agnostic, it should allows me to work with different databases in a transparent way
• It has to have not so much configuration or must be based on conventions to make it work
• It should allows me to work with Linq

So, do you know any ORM that I could use? Thank you for your help.

EDIT I know that an option is to use NHibernate. This appears as the facto standard for enterprise level applications, but also it seems that is not very productive because its deep learning curve. In other way, I have read in some other post here in SO that it doesn't integrate well with Linq. Is all of that true?

Answer 1

Perhaps your best bet is using NHibernate. It's arguably the best "industry standard" when it comes to both commercial and open source ORMs. It has been around a long while to become really stable, is used in many enterprise companies, is based on the even better known Hibernate (java), but has fully been rewritten to make the best use of .NET features.

NHibernate drawbacks

This sounds like I'm an advocate of NHibernate. Perhaps I am. But NHibernate has a drawback: it has a steep learning curve and getting used to the many possibilities and choosing the right or "best" practice for your situation can be daunting, even for experienced developers. But that's the prize to pay for an enterprise-level ORM that's capable of virtually anything.

NHibernate with FluentNHibernate rocks

Many of these drawbacks and setup problems vaporize the minute you start using Fluent Nhibernate, personally, I hardly do without it anymore as it removes all the tediousness of NHibernate at once (almost).

It makes working with NHibernate a breeze: just write your entities as POCOs and load them fully automatically to create your database, the associations etc (or don't create the schema if it's there already). Configure your database using the Fluent syntax. A very simple setup can look as basic as this:

// part of a default abstract setup class I use
public ISessionFactory CreateSessionFactory()
{
    return Fluently.Configure()
        .Database(
            MsSqlConfiguration.MsSql2008
                .ConnectionString(c =>
                    c.Server(this.ServerName)
                    .Database(this.DatabaseName)
                    .Username(this.Username)
                    .Password(this.Password)
                    )
        )
        .Mappings(m =>
            m.AutoMappings.Add(AutoMap.AssemblyOf<User>()   // loads all POCOse
                .Where(t => t.Namespace == this.Namespace))
                // here go the associations and constraints,
                // (or you can annotate them, or add them later)
            )
        .ExposeConfiguration(CreateOrUpdateSchema)
        .BuildSessionFactory();
}


// example of an entity
// It _can_ be as simple as this, which generates the schema, the mappings ets
// but you still have the flexibility to expand and to map using more complex
// scenarios. It is not limited to just tables, you can map views, stored procedures
// create triggers, associations, unique keys, constraints etc.
// The Fluent docs help you step by step
public class User
{
    public virtual int Id { get; private set; }   // autogens PK
    public virtual string Name { get; set; }      // augogens Name col
    public virtual byte[] Picture { get; set; }   // autogens Picture BLOB col
    public virtual List<UserSettings> Settings { get; set; }  // autogens to many-to-one
}

public class UserSettings
{
    public virtual int Id { get; private set: }   // PK again
    public virtual int UserId { get; set; }       // autogens FK
    public virtual User { get; set; }             // autogens OO-mapping to User table
}

which takes all POCO entities and automatically maps them, creates the configuration for the ORM and builds the schema in the database, provided the user has sufficient rights. One very powerful ability of Fluent (and NH to a lesser extend) is to update a database schema when you make any changes.

Other aids to NHibernate

Also on the upside: many auto generation tools exist (including the open source MyGeneration) that can take your DB schema(s) from a simple ODBC or other connection and turn them into the correct entity classes, associations and HBM configuration files. Many of these tools are (partially) graphical design aids.

Use S#arp for enforcing MVC + NH + NUnit best practices

Make sure to read NHibernate best practices. It brings generics and DAO to the next level. You can also skip to the chase and dive deep with S#arp (download), which is a framework that imposes all these best practices and adds NUnit to the mixture.

Before I start using a new technology I usually want it well covered. NHibernate and Hibernate don't come short here. Many books explain (N)Hibernate from starter to professional, white papers are abundant and tool documentation is meanwhile rather excellent.

About LINQ and NH

LINQ and NHibernate have always gone well together through all types of ICollection<> which are used in the many-to-X mappings and other associations, but requires the data to be retrieved first which requires a good design (the cache helps here), otherwise it'll perform badly. This has been considered a sore point of NH ever since LINQ came about.

Luckily, there's now a new kid in town: NHibernate-LINQ, which maps LINQ queries to ICriteria queries prior to submitting. ICriteria queries are well cached and this combination with LINQ is both very powerful and very performant. NH-LINQ is now part of the standard distribution.

Disclaimer

I've used NHibernate for a almost decade (first Java, later .NET). I've flirted with other ORM's both commercial and open source, but in the end always returned to NH (unless company policy demanded different, but that was rare). This story may sound a bit biased but the space here is too short to go into excruciating detail about how NHibernate compares to other technologies.

It's very well possible that other ORM's better fit your need, especially if you never plan to use it in complex multi-database, multi-db-server or hard-to-map-to-OO legacy situations. For me, NH shines because it doesn't limit me in any which way and supports full roundtrip engineering, but your choice might turn out different if features of lighter ORM's that are discussed here weigh heavier for you.

Update: added code sample
Update: expanded code sample, fixed typos and wording
Update: little chapters, added LINQ part, added Disclaimer part

Answer 2

Why not look at subsonic? I like it over the others because it's lightweight maps transparently to the database scheme (uses ActiveRecord) and fulfills all your requirements.

It has to be very productive.

I think this is the job of every ORM? With subsonic you can use the Controller (for databinding) or just execute the Save method on any ORM object.

It should allows me to extend the objects

Extending the generated classes is easy, they are all defined as partials. And you can even edit the templates. (They are T4 templates you include in your project, so you have complete control over how and what is generated)

It has to be (at least allmost) database agnostic

I think this is kinda basic for any ORM. Subsonic supports a lot of database of which the well knowns are: Oracle, mySql, MsSql, SqlLite, SqlCE. You can look at the database support list here.

It has to have not so much configuration or must be based on conventions

Yes, it is absolutely convention over configuraion or opinionated as they call it. For a summary of the conventions look here.

It should allows me to work with Linq

Absolutely, since version 3.0 Linq is supported.

For a comparisson between nhibernate, LinqToSql and subsonic read this It's actually a fair and up to date comparison and explicitly outlines the differences in the visions of the different ORM's.

Things I miss in subsonic:

• UnitOfWork support (you could solve this by using the support for transactions.)

• IdentityMap support (your objects get cached in some scope (appdomain, threat, web request context, page lifetime, ...) Although you good argue if this is supposed to be part of the ORM, or of some caching layer.

I heard hibernate supported both.