Data Access Technologies - Should I use ADO, LINQ, nHibernate or the Entity Framework?

I came across this blog post today that explains the differences between these data access technologies.  However, it does not give a definitive answer on when to use which technology.  That's because it depends on so many factors.  My advice is to keep it simple. 

Let me expand on the different technologies and provide some guidelines on when to use them:

Classic ADO.Net - In my opinion ADO.Net is a dying breed.  As a developer you need to know it for maintenance purposes, but any new features or products should be designed using LINQ or EF\nHibernate.  Some people might not agree with me here, but again this is my opinion.  The only caveat is to make sure the target environment supports the 3.5 Framework.  If the systems people will not let you install 3.5 on the server or end user's machine then LINQ and EF will not work. 

LINQ (LINQ to SQL) - As the blog post on this states, LINQ currently only supports SQL Server.  There are tons of open source projects that do LINQ to other things, but the only MS supported database backend is SQL Server.  For many of us, that is just fine.  If you know you need to support other DB platforms you can move on to EF\nHibernate now.  If you're pretty certain the only DB backend is going to be SQL Server then the decision between LINQ to SQL or EF\NHibernate comes down to architecture.  EF\nHibernate are full blown ORM tools, meaning they include many additional features (Inheritance, complex types, multiple data sources, etc) over and beyond what LINQ to SQL provides.  So, if you are using SQL Server and the application design is not overly advanced or complex.  I'd use LINQ to SQL.

EF\nHibernate - I lumped these two tools together because they are both ORM tools.  They provide support for many advanced and complex concepts in software design.  Using these advanced features is great if you are building a large enterprise, public facing or consumer use application.  However, for many applications these tools can be overkill and end up taking too much time to design.  That being said there is one thing that often gets overlooked, the fact that you can use these tools to do one to one mapping similar to the way LINQ to SQL does.  So, if you need to support mulitple DB backends, but you don't want to use the advanced features you can do so.

In summary, if you are building a simple application that is going to use SQL Server as the DB backend, use LINQ to SQL.  If you are building a simple application that needs to support a DB backend other than SQL Server, use EF or nHibernate and do one to one mapping between your entities and your DB tables.  If you are building a large application that requires some complex architecture, use EF or nHibernate.  Don't use ADO.Net for any new projects unless the target environment does not support the .Net 3.5 Framework.  However, I don't think it is necessary to go back and rip out all your ADO.Net code and replace it with something else.  Just be patient and use the new stuff with new projects.

11 comments:

  1. Raj Says:

    The blog is good, explains briefly about the differences between ADO.net, LINQ, and EF. It would have good if provided some code snippets as well

  2. electroharp Says:

    May lead to believe that you need 3.5 framework for nHibernate.. actually you don't

  3. mchean Says: This comment has been removed by the author.
  4. mchean Says: This comment has been removed by the author.
  5. mchean Says:

    Sorry about that last one. Thanks for the clear rundown. What do you think of Object DB like DB4o if you have that option available to you?

    Mike

  6. Stseng Says:

    Have you compare data access wrt to Data services via CAM Content Assembly Mechanism (CAM) from
    OASIS http://www.oasis-open.org/committees/tc_home.php?wg_abbrev=cam
    http://wiki.oasis-open.org/cam

    presentation http://dama-ncr.org/Programs/ScheduleOfPast.htm#May%2013,%202008:

    article http://www.soamag.com/I9/0707-1.asp

  7. Jiangning Tang Says: This comment has been removed by the author.
  8. Jiangning Tang Says:

    Why do you think ADO.NET is dying? Just because it's not as sexy and fancy as LINQ? What problem does ADO.NET has? Performance? Learning curve? Scalability?

    The only thing good about LINQ is its uniform programming model across querying DB, XML, Collections. If you are working with SQL Server or Database alone, it's not as efficient and performing as ADO.NET (working with stored procedure)

  9. Ben H Says:

    Let me repond to a couple of the comments here. First off, I have not played with many of the ORM tools out there. I know there are many tools and many different schools of thought. The purpose of this post was to express my opinion based on the tools I've used.

    I still believe ADO.NET 2.0 is on the way out. There are many reasons. Check out my latest post (http://www.benhblog.com/2008/07/are-adonet-datasets-dead.html) for a few of my reasons.

  10. Microsoft CRM 4.0 Implementation Says:

    Lots of frameworks are coming day by day... it has become difficult to judge...

  11. mohamed Says:

    Thx for ur article, but plz if u can specify the advs of EF and NH, and according to which criteria i use either EF or NH, like performance, validation, row based security and .....so on.