This isn't a very easy task - we're biased. But being the fair, balanced, and wonderfully talented people we are, we'll do our best to help you out.
Which To Choose?== If youve 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 - Microsofts 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). If youre not familiar with ORM and you dont have a tool you swear by, the selection can be pretty hard. Lets tackle that one first. ==If You Dont Have a Favorite ORM Tool
Lets 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, DTOs: The class-based representation of your data which vary in ability and smarts. If youre using the Northwind Database, for example, a Product is a "Domain Object", also (blandly) referred to as an "Entity" if it's part of your Domain - or could be a simple, dumb object (DTO) used for moving data around.
Active Record: This is what SubSonic uses (and Rails and MonoRail) for its 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" its state to some medium (usually a database). Note that the domain model doesnt need to be literal - its 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 cant reach " The main differences here are how "smart" your objects are. Some people dig the simplicity of Active Record, other people dont like the idea that a DB call can be made from any layer of your application (as it can avoid any logic you have thats supposed to happen during a call to the DB).
So Which One?== The first thing to ask here is "in what context?" Heres what we 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? *What Database are you targeting SqlServer, SQLite, MySql, Oracle ? 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 youre on the line for the choice. So lets start there. ==Lifetime Considerations. Or How To Keep Your Job
One thing to keep in mind is this guideline (for better or worse - I didnt make it up, Ive 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 - its become quite popular, and its "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 Im 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 wont "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. Its pretty obvious its 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. Youre pretty safe with all of the tools here, however the safest bet is LinqToSql if youre 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. Its 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 weve 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 youre able to set standards for your project that align with SubSonics 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 wont 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 youll 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 youre 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. Ill update the post as necessary. Know that what Im offering, if it sounds biased, comes from lack of knowledge. And bias :). NHibernate is a port of the Hibernate Core project from Java. Given its history and maturity level, there is a certain amount of "weight" involved with it that youll need to get used to. This isnt a negative statement at all - its 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. Theres not much you cant do with NHibernate, and all in all its probably the most comprehensive ORM tool around.
Performance == It may be a feature to some, but its 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();
Note: you can also use Linq here with SubSonic 3.0 Setting up SubSonic is very straightforward: add a reference and setup the provider in your web.config, and youre all set as SubSonic will generate your code for you using a BuildProvider (and you never have to maintain the code - its magic). You can also use a command line to generate the classes for you.
LinqToSql has a bit of a learning curve in that you have to learn LINQ in order to use it. Its 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
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 dont 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:
We cant come right out and tell you to pick one over the other - its all up to you and what youre coding at the time. Besides, this post would be way too short if we just told you to use SubSonic :). Whatever you do - know that there isnt a "right way" or "Best Practice" with regards to your work - its whatever fits your project, budget, and tolerance. Or what your boss says.