Tutorial :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?


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


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


Solution:3

I suggest checking out NHibernate.

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


Solution:4

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.


Solution:5

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.


Solution:6

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.


Solution:7

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.


Solution:8

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


Solution:9

I know this question is almost 8 years old, but at least if there is a straggler to this question, they will get a more complete picture of the ORMs available.

We've been using Dapper for database access. It's very lightweight. It's extremely fast. I still get to write SQL (which is my preference). It automatically maps returned data into objects, even a dynamic object. You can add extensions that will insert, update, etc without the need for SQL. So you can build it up to more closely emulate a full ORM.

Once you start using it, you'll be shocked how you lived without it. It should ship with the .NET framework.

It's database agnostic, BUT the SQL you write may not be. So if you are writing your own SQL, you'd have to ensure it works on your target databases.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »