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

I suggest checking out NHibernate.

https://www.hibernate.org/343.html

Answer 2

Given your requirements, I'd suggest checking out Mindscape LightSpeed. It supports about eight or nine different databases, and is convention driven (with options for configuration), so is very easy to set up. It has a LINQ provider. It allows you to extend the classes with your own properties and methods: in particular it allows you to decouple the persistent model (the fields) from the API (the properties and methods) without breaking the convention over configuration approach.

Answer 3

Just as a follow up to some of the answers here, there is NHibernate Linq spearheaded by the unbelievably prolific Oren Eini, AKA Ayende Rahien

http://ayende.com/Blog/archive/2009/07/26/nhibernate-linq-1.0-released.aspx

Haven't used it, but it looks very impressive. Seems like at some level it could even be a replacement for LINQ for SQL.

Answer 4

I've used Entity Framework for a couple of projects and really liked it. There admittedly were some kinks in the first version, particularly the way it dealt with foreign keys and stored procedures, but version 2, which is in beta and part of VS 2010 looks very promising.

Answer 5

Man..I'd go with the entity framework. It supports Linq. The entity framework 4.0 has major performance improvements over 3.5. It has everything that you require in your post. EF is more then a ORM it is a framework. I think nhibernate is a joke compared to the M$ entity framework. Nhibernate really dropped the ball for not including intellisense and making the setup easier.

Many enterprise organizations have embraced entity framework as well. Entity framework can support any database that can be ran on windows because it has a feature to allow any vendor to create a provider for it. Do yourself a favor and go with EF.

Answer 6

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.

Answer 7

You could also look at LLBLGen While it lacks a snappy name, it does have all the features you mentioned:

It is drivers for most database version, Oracle and SQL and others It supports Linq, in that you can use Linq to query the LLBLgen generated objects It allows you to extend the generated objects, they are all partial classes