ASP.NET MVC: Choosing Your Data Access Method

I've been asked a lot over the last few weeks this question:

Should I use SubSonic, LinqToSql,  or  NHibernate?

And I think it's probably a good idea to go over these things in a post - so here goes...

Which To Choose?
If you've decided to use ORM (Object-Relational Mapping), then you have some choices when it comes to toolsets. There are a lot out there - a few you can choose from are:

• LinqToSql - Microsoft's entry into the ORM arena
• SubSonic - my baby
• NHibernate - a well-respected ORM tool that has been around for a long, long time (in geek years).

There are many, many others and you may have your favorite that I don't have listed here. If it's not listed, rest assured that it's because 1) I'm biased and 2) I work for Microsoft  - but feel free to list yours in the comments! The more the merrier. This is a joke by the way - I've picked these based on price and acceptance (free and wide).

If you're not familiar with ORM and you don't have a tool you swear by, the selection can be pretty hard. Let's tackle that one first.

If You Don't Have a Favorite ORM Tool...
Let's start out by saying you should be very aware of the "pattern" the tool uses to work with your database. Lots of terms get thrown around, and you should be familiar with them:

Domain Objects, Entities: The class-based representation of your data. If you're using the Northwind Database, for example, a Product is a "Domain Object", also (blandly) referred to as an "Entity". What's a domain?

Domain: The context in which you are using your objects. People often refer to the structure of their objects with reference to their database as their "Domain Model"

Active Record: This is what SubSonic uses (and Rails and MonoRail) for it's core data access pattern. The idea here is that every object in your domain represents a table in your database, and every instance of that object is a row in one of those tables. The instance is responsible for "persisting" it's state to some medium (usually a database). Note that the domain model doesn't need to be literal - it's just a representation of your database in code.

Repository Pattern: The idea here is that there is an intermediary layer between your objects and your DB that handles Data Access - meaning you have "dumb objects" essentially. This is basically like a translator coming to your domain party so your database (who speaks Tagalog) can speak properly to your objects (which speak Swahili).

Data Broker: A data broker handles business logic and other things, and basically binds objects on an "as-needed" basis. It also persists the objects. This is slightly different than the Repository as it can contain some business logic.

This is also referred to as the "Mom" model, as in "Mom can I have a Product Object please? And can you put this Customer object back in the freezer for me? I can't reach..."

The main differences here are how "smart" your objects are. Some people dig the simplicity of Active Record, other people don't like the idea that a DB call can be made from any layer of your application (as it can avoid any logic you have that's supposed to happen during a call to the DB).

So Which One?
When people ask me about ORM tools, the first thing I ask them is "in what context?" Here's what I mean:

• Are you a contractor that is hired to work on projects with a great amount of autonomy?
• Do you work in a large company with more than 10 developers?
• Are you the boss?
• Do you prototype a lot?
• Are you in a position to educate?

The main thing to keep in mind here is that whatever tool you use, ultimately someone else will need to pick it up and use it and you're on the line for the choice. So let's start there.

Lifetime Considerations. Or How To Keep Your Job
One thing to keep in mind is this guideline (for better or worse - I didn't make it up, I've just witnessed it too many times):

Nobody ever got fired for using Microsoft

This is for many reasons, but in general if you select an Open Source tool, you need to be very keen on the lifetime of your project in relation to the tool your using. A lot of times this means that you should go with LinqToSql.

With regards to SubSonic - it's become quite popular, and it's "life expectancy" just grew in a nice way with me being able to work on it more with Microsoft.

Indeed this event has a lot of advantages, one of them being (among others) that I'm part of the scoping and spec discussions with respect to MVC (and other things), and as such I know very well how to fit SubSonic into the MVC picture. I also won't "plead broke" and let the project die. This is now my job, and I love it.

NHibernate has legs, and has been around for many, many years. It's pretty obvious it's not going anywhere, and a lot of people know the tool very, very well. You can almost be sure that someone on your team will be familiar with it and will have an opinion of it.

You're pretty safe with all of the tools here, however the safest bet is LinqToSql if you're worried about "project security". If not, the productivity curve might tilt away from LinqToSql, only because of learning the ins/outs of LINQ.

Vision and Approach. Or "Why They Made It"
Understanding why a tool was made (and the design philosophy behind it) can help a lot in terms of deciding to use it. It's important to use a tool that fits with your programming and architectural style.

SubSonic
With SubSonic, the focus was simplicity at every turn, with performance at the top of mind always. The vision we've always had in our mind (our "Mort" if you will) is you, saying "SWEET!" while you turn off your PC and go home with your work done an hour early.

To do this we rely on the concept of "Convention over Configuration" - what you do is more important than a setting in a file somewhere. This can be advantageous if you're able to set standards for your project that align with SubSonic's conventions.

LinqToSql
This toolset is not driven by convention, and instead allows for a reasonable amount of flexibility with respect to designing your domain.

Microsoft has never been in the position of "telling developers what to do" - so you won't find a lot of "prescriptive guidance" here. Instead what you will have is a very clean, easy designer, the ability to set a lot of names and override some key behaviors.

LinqToSql is built on top of LINQ, a set of extensions to C#. This makes querying fairly straightforward, however learning a new language feature can be a consideration for a team. On the other hand you'll have to learn it eventually :).

LinqToSql works in a very literal "Domain Context" - meaning that to work with your objects, you need to have your domain "instanced" in the form of a DataContext. I talk about this in more detail here.

The summary of this post is that the DataContext is a nice, transactional way to work with your database, but there is a strong conceptual difference between what you're used to with "detached data" and the web. The upside here is that you have concurrency options built in. The downside is that this presents some limitations if you want to use objects as data-transport mechanisms. In other words if you want to instance a Product, work with it, and pass it to another layer in your application to save it, there are some hurdles to jump.

NHibernate
Note: I have never used NHibernate, so if I get some of this wrong please leave me a comment and clarify. I'll update the post as necessary. Know that what I'm offering, if it sounds biased, comes from lack of knowledge. And bias :).

NHibernate is a port of the Hibernate Core project from Java. Given it's history and maturity level, there is a certain amount of "weight" involved with it that you'll need to get used to. This isn't a negative statement at all - it's just been around for a while and has a lot of features to it.

Getting started with NHibernate can be a bit intimidating at first, but once you ramp up you will no doubt have twice the feature set of SubSonic and LinqToSql. Features mean learning and reading - and if you like diving into an API, this is a good choice.

NHibernate also has a very large following, and members of the core team are prolific bloggers who discuss how to do things with the toolset quite often.

There's not much you can't do with NHibernate, and all in all it's probably the most comprehensive ORM tool around.

Performance
It may be a feature to some, but it's lifeblood to others who find themselves at the helm of a high-traffic site.

Each of these tools is very performant, but NHibernate has spent a little more time with the concepts of data-caching and lazy/eager loading than either SubSonic or LinqToSql.

Caching is a big issue, and can pretty much be handled by your application as well as your data layer - this is an architectural decision you need to make.

Ease Of Use
All three of these tools are fairly easy to use in terms of Entity use and Querying in general.

SubSonic
SubSonic leans heavily the concept of the "Fluent Interface" - which means when you write a query, you write it out as you would a sentence rather than a structural object instantiation:

IDataReader rdr=new Query("Products").ExecuteReader(); 
IDataReader rdr=new Query("Products").WHERE("CategoryID",Comparison.GreaterThan,5).ExecuteReader(); 

You can also work with strongly-typed collections in much the same way:

ProductCollection products=new ProductCollection().Load(); 
ProductCollection products=new ProductCollection().Where("CategoryID",Comparison.GreaterThan,5).Load(); 

Setting up SubSonic is very straightforward: add a reference and setup the provider in your web.config, and you're all set as SubSonic will generate your code for you using a BuildProvider (and you never have to maintain the code - it's magic). You can also use a command line to generate the classes for you.

LinqToSql
LinqToSql has a bit of a learning curve in that you have to learn LINQ in order to use it. It's not terribly difficult, but it is a consideration. The query looks like this:

NorthwindDataContext db=new NorthwindDataContext ();

var result = from p in db.Products 
             select p;

This query returns an IQueryable<T> object, which you can enumerate over like so:

foreach(var p in result){
     //do something...
}

You can also work with the data in List<> form:

List<Northwind.Product> list=p.ToList<Northwind.Product>();

LinqToSql starts to bog down a bit when things get more complex (but do keep in mind you can always use a View or SP). You can indeed write some complex queries, but they usually take hours to figure out and test unless you know the API very well. Here's one in particular that grabs a customer purchase by month rollup:

            var customerOrderGroups = 
                from c in customers
                select
                    new {c.CompanyName, 
                         YearGroups =
                             from o in c.Orders
                             group o by o.OrderDate.Year into yg
                             select
                                 new {Year = yg.Key,
                                      MonthGroups = 
                                          from o in yg
                                          group o by o.OrderDate.Month into mg
                                          select new { Month = mg.Key, Orders = mg }
                                     }
                        };

Again, if you know LINQ this this isn't much of an issue (when I look at this query all I see is "blond... brunette... redhead..."). The main consideration here is readability - LINQ can be a challenge when it comes to this. This is mitigated, however, in that in the next 3 years or so we'll all know LINQ very well :).

NHibernate
Again - not very familiar so if I write something silly, do let me know.

Setting up NHibernate is probably the biggest downside of using the tool. A mapping has to be made between your database and the toolset, and in that map you can describe all sorts of ways that your domain should take shape. This is a one-time consideration (usually) so don't be dissuaded by this alone. The fact that you have this level of control over your domain map can be very very helpful later (with regards to naming, etc).

Using NHibernate is pretty straightforward. To run a query, you simply:

ICollection<Customer> customersInLondon = Repository<Customer>.FindAll(
   Where.Customer.City.Eq("London") ); 

There multitudes of ways to get data from your db using NHibernate - even the most complex queries with nested loops and joins.

There really is no comparison here between what SubSonic can do (we always say "use a View or SP for complex queries") and NHibernate. NHibernate blows SubSonic away in query deparment.

Summary
I hope you found this helpful, and as you probably imagined I can't come right out and tell you to pick one over the other - it's all up to you and what you're coding at the time. Besides, this post would be way too short if I just told you to use SubSonic :).

Whatever you do - know that there isn't a "right way" or "Best Practice" with regards to MVC - it's whatever fits your project, budget, and tolerance.