public class SubSonic3: IEnjoyable

A couple of years ago, I heard about a data-layer code generation tool called SubSonic.  At the time, there was a fair bit of confusion about how it was to be categorized - it wasn't really an ORM, after all.  In fact, it was sort of a C# version of Ruby-On-Rails' ActiveRecord.  I played with it a bit, but I set it aside because I didn't have any projects at the time that called for something like this.

Fast forward a couple years, and some things have changed.  SubSonic's founder, Rob Conery, has been hired by Microsoft.  Somewhat surprisingly, this has not meant the end of SubSonic.  In fact, Rob's working on a new release, and it's shaping up to be really, really nice.  This is the first SubSonic release to come out with Linq support, and they look like they were made for one another.

When Rob announced the Subsonic 3 Preview 2 release, I grabbed it and took it for a quick spin.  If you're going to try this release, Rob's done a quick video that walks through a quick install and deployment, and I'd recommend watching it.  Installation couldn't be easier - the readme file lists four simple steps, and if you do everything right, you'll be up and running with scaffolding support for your database in less than five minutes (it takes less than one in the video).

What do you get for your effort?  SubSonic's scaffolding support builds out classes for each table in your database, as well as helpers for all of your stored procs.  The syntax shifts since SubSonic's earlier releases means that SubSonic 3 isn't a true ActiveRecord implementation, but the syntax is very natural and (IMO) productive -- it allows you to express database requests in a really compact form:  (example from Rob's post)

Northwind.DB db = new DB();
var result = from p in db.Products
where p.CategoryID == 5
select p;

foreach (var item in result)
{
Console.WriteLine(item.ProductName);
}

There's also support for LINQ queries, and you can batch queries so that multiple queries occur in one call to the database.  Even stored procs get an assist from SubSonic - procs are encapsulated into simple, type-safe function calls that return a reader for the results.  This is a big improvement over traditional data access layers where you'll see tons of repeated code.

Part of the magic comes from the SubSonic assembly, which supplies database-related code that could cut a bunch of repeated code out of your projects all by itself.  That's only part of the picture, though - there's a set of generated files that gives you strongly-typed objects for each of your tables.  The code generation uses T4 templates - see this screencast on the Patterns & Practices site for more on T4.

While simple applications might make use of the generated partial classes as-is, more complex apps are going to want to extend and/or encapsulate these classes, and expose more complete business classes to callers.  There are a number of ways you could do this:

• Edit the generated code.  This is probably not an approach you'd want to take for a production app, but for a quick prototype, it could work well.  The problem with this approach is that your customizations are subject to breakage as the generated code changes.  If you can limit your changes to extensions (via partial classes), you might stand a chance here; otherwise, you'll want to explore other options.
• Change the templates.  Since SubSonic is open-sourced, you have access to anything you might want to change.  Better yet, since the templates themselves are copied into your project, any changes you make there will be local to your project.  If your changes can be expressed in the T4 templates, this could be a really good option for you.
• Encapsulate the generated classes.  I've come to really like working with Rocky Lhotka's CSLA Framework, but the data access parts of these classes freqently end up feeling a little more ponderous than I'd like.  SubSonic is just the thing to fix this - its syntax is crisp and compact, leaving me to focus on writing business code in my CSLA classes.  Personally, I like this model a lot - it builds on the strengths of both projects.

I'd also recommend checking out Rob's webcast on using SubSonic as a REST handler.  There are some other interesting options if you're trying to build a REST interface to your database (Astoria, WCF REST starter kit), but it's a great demonstration of SubSonic's ease-of-use.

Bear in mind that this is a preview release (before beta), so there are some rough edges.  I saw some code generation snafus where I had fields named the same as their tables, and there's a problem with LINQ support that makes it tough to construct a LINQ query using the value of a variable rather than a reference to that variable.  You're going to want to wait for a slightly more mature release before you start doing "real" work with 3.0, but this preview will give you a good indication of what's to come.  There's a lot here to like.