SubSonic – Another Look at Code Generation

Last month, I wrote Manual Code Generation, an article which shared, in my view, the pros and the cons of code generators.  In this same article, I took sides and expressed that “I am not a fan of code generation.”  My stance came as no surprise to some — particularly my buddy (read archenemy) Jon Galloway.  We’ve gone back and forth on this topic for a few years now so, of course, Jon replied to my post with arguments in favor of code generation.  Along with arguments, he also offered a few suggestions; the best of which was to check out SubSonic.  Actually, Jon simply asked, “Have you checked out  SubSonic?”  but I know this was just his polite way of asking me to please check it out before I continued to publicly embarrass myself by making bold statements about things which I have fairly little exposure simply because I effectively dismissed them years ago.  

I have now checked out SubSonic.  In an effort to get in the last word, here’s my review:

First Impressions

The SubSonic project is hosted on CodePlex.  If you are not familiar, CodePlex is Microsoft’s open source project hosting web site. You can use CodePlex to create new projects to share with the world, join others who have already started their own projects, or use the applications on the site and provide feedback. Through CodePlex your project team may manage news feeds, releases, discussions, issues, source code and licensing.  I know this is a review of SubSonic, but CodePlex is worth talking about (especially since it helped make a good first impression.)

Nearly Everything You Need To Know

The project’s main page has nearly everything one needs to get started:

Product Overview 

The overview provides a very quick description of the product’s core feature list.  To be honest, I wish more detailed information was provided here, but I think I got the gist.  At its essence, SubSonic includes:

  1. A Data Access Layer (DAL) Builder which generates CRUD-enabled objects (which, in turn, represent selected database tables) and complementary strongly-typed collections.
  2. A complete utility toolset complete with Rails-like scaffolding which effortlessly builds admin-like web pages, migrations or DB versioning and code generators.
  3. A dynamic query tool that lets one use SQL Server, mySQL and/or Enterprise Library without having to know the underlying SQL syntax.
  4. An object-relational (OR) mapper which extends to stored procedures and views.

My one recommendation would be to elaborate on the feature set and tell a little more about the product in the overview.  Answer questions like “Why was this effort undertaken?” and “What problem does SubSonic solve?”  That sort of thing.  I will get into this a bit more in the Webcast section, but I think the “Welcome to the ActionPack for .NET!” content found on the sample website default page would be a nice addition to the project main page. 

Source Code Download 

SubSonic 1.0.5 was recently released on 10/24/2006.  It’s a 1.3 MB .zip file download which includes the complete SubSonic Actionpack, Sample Website and Dependencies.  A double-click on the unzipped solution file, a quick update to the Northwind connection string and I was up and running.  You can’t beat that.

Starter Site Download 

Latest version of the starter site project was released on 10/24/2006 as well.  It came packaged up as a .vsi (Visual Studio Content Installer) file which, unfortunately, failed when I attempted to run it  [1].  As I mentioned previously, the Sample Website source code was provided which I’m guessing is what would come along with the Starter Site so I was still in business.


This demo was a treat.  I am so pleased I spent the time to watch it.  Not only did the webcast walk through highlighted functionality, but, in my opinion, it gave the product a “story.”  You see, when I am at a gallery or museum, I tend to spend more time reading about the art/artist then actually admiring the work itself.  I have a hard time appreciating a painting until I understand its background.  I really like to know what the piece represents and what drove the artist to create it.  That’s what the 20-minute webcast gave me.  It gave SubSonic a personality.  It also more deeply shared the philosophy behind the product which is Rails.  Though the written overview touched upon elements of Rails (scaffolding), the demo touched upon one of its guiding principles (Convention Over Configuration) and base architecture (MVC, model-view-controller) in the words of the author.  If you are interested in trying out the SubSonic ActionPack you should absolutely watch the webcast.


Now that SubSonic was running on my machine, it was time to start playing around.  I started with the basics which was basically everything which was discussed in the demo. 

BuildProvider Class

If it hasn’t been mentioned already, SubSonic takes advantage of  a new feature in .NET 2.0, the System.Web.Compilation.BuildProvider Class, to generate the DAL at compile time.  There’s good reason why I’m placing a lot of  emphasis on the use of the BuildProvider: First, you are generating and working your code within the same tool — Visual Studio. Also, your code is generated automatically when you build your application.  This means your application code and generated code will never be out of synch again. Finally, I wasn’t previously familiar with the BuildProvider class, thus it made SubSonic a great learning tool.

I should mention that the generator is wired up really nicely and you don’t actually need to know a thing about the BuildProvider.  All you need to know is classes/collections will be generated for tables listed in the App_Code/builder.abp file.  You can even keep the default (not touch a line of code) and generate classes for all tables.

Additional Documentation

I built the project and clicked through the Sample Website.  Again, I really like the content on the default page so be sure to spend some time here.  I then breezed through the short tutorials found in the “Setup” and “CRUD” sections since the WebCast had already provided me with the basics.

Data Access Layer

My main interest was working with the generated DAL.  Therefore, I spent the majority of my time in the “Query the DB” section.  I did encounter an issue with the “Use a Collection” example, but otherwise the samples provided for a great testing grounds [2].

At first glance, I personally thought the provided examples were dumbed down a little too much.  Sure, they were enough to get one started, but they didn’t go beyond the bare essentials. 

For example, what if one wishes to filter their result set by more than one criterion.  How is this done?  After fiddling around a bit, I discovered this is as easy as making more than one call to the Query object’s AddWhere() function, but I wouldn’t have known without some experimentation.

Actually this is a nice segue. 

Knowing SubSonic

I’ve commented in the past that generated code is difficult to write and maintain unless you have a firm, working knowledge of the code generation tool itself.  What I mean by this is the generated code (in this case the exposed functions and methods available in the SubSonic DAL) uses custom syntax which one can only learn through using the tool and generated objects. 

The Query object’s AddWhere() function acts a good example.  Though it’s easy to query on multi-parameters, you have to learn SubSonic’s language before knowing how to do so.  This makes for an interesting point.  The documentation states that SubSonic “lets you use SQL Server and the Enterprise Library without having to know SQL.”  Though this is true, instead of learning SQL, you need to learn SubSonic’s language.  

Sticking to Conventions

To the creator’s credit, it appears that time was dedicated to making the tool and, in turn, the DAL consistent and intuitive.  SubSonic is a working example of  “Convention Over Configuration”  which makes learning how to use the product a lot easier.  There are numerous examples of this throughout the code, but most glaring is the decision to use singular, rather than plural, forms of object names.  Even if the underlying database table is named Products (plural), SubSonic is configured to generate a ProductCollection rather than a ProductsCollection.  Once you start understanding SubSonic’s conventions and patterns, you can start to navigate around the codebase with more confidence. 

(Of course, I had to look at the PluralToSingular utility function.  It will work in most cases, but the ever-present Octopi table is going to cause problems.)

Writing SubSonic

One big code generation con, in my opinion, is that someone has to spend time writing the code.  Though my point holds water, Jon argued that someone has already written SubSonic so this shouldn’t be a problem for me.  Well, maybe I’m too picky, but I’ve found that most software doesn’t do everything that I want or need and I wind up at least tweaking the generated classes/collections. 

For example, I like to be able to access collection items by key as well as by index.  The CodeSmith implementation, which I mentioned in my earlier write-up, didn’t provide this functionality.  Additionally, functions like Exists() weren’t available either.   If I recall, these custom collections were inheriting from System.Collections.CollectionBase which isn’t fall that full-featured.  SubSonic’s generated collections are a bit more robust (they inherit from System.Collections.Generic.List ) but I still can’t access, add or remove an item from the collection by key.  In the end, even though someone has already written the code, I will also need to spend time writing code if I would like this functionality. 

Code Regeneration

With this said, I wouldn’t necessarily have to modify the code generator itself.  Since SubSonic takes advantage of partial classes, I could easily maintain app-specific collection code with no concern that it would be over-written with the code generated by the next compile.  Along these lines, as Jon pointed out, SubSonic does support handcrafted code very well.  Subsonic also supports database changes as the DAL is safely regenerated when the project built.

DAL Focused

If you use the provided scaffolding model you will find it can quickly generate admin pages based on a database table and the associated DAL objects.  In the example project, scaffolding is in place to administer the Products table. What one gets out-of-the-box with this example is a list of all products along with a link add or edit. On the edit page, one may modify any of the field values (with the exception of an identity column.  Item deletion is done on the edit page as well.

Though scaffolding is in place to quickly generate admin pages, the presentation layer is not the ActionPack’s focus.  SubSonic’s bread and butter truly is the DAL which I consider a very wise approach to code generation.  In fact, in the demo, it even suggests that CRUD scaffolding should be considered for developer use only as it is presumably non-performant. I have to give SubSonic’s team a lot of credit for making scaffolding available yet sharing the limitations openly and not pushing this feature down the throats of end users.  I also really respect the decision to focus heavily in one area (the DAL), making it really solid, and not get distracted by other not-as-important features.

Keep It Simple, Stupid 

SubSonic’s DAL architecture follows the KISS principle which I admire.  For example, though the Northwind EmployeeTerritories table establishes an obvious relationship between Employees and Territories through foreign key constraints, the DAL doesn’t.  One might assume the Employee object would own an internal Territories Collection, but it doesn’t.  SubSonic generates separate objects for each database table and they are managed completely separate in the DAL.  Period. 

I may be wrong, but I believe this means one would have to do the following in order to display the names of all Territories for Employee with the last name Griswold.

  1. Fill an EmployeeCollection where last name = Griswold
  2. Fill a EmployeeTerritoryCollection reference for each Employee.EmployeeID in the EmployeeCollection.
  3. Fill a TerritoryCollection for each EmployeeTerritory.TerritoryID in the EmployeeTerritoryCollection.

You may be thinking, “That’s not exactly simple.”  Well, you are right and you’re wrong.  The object model is simple and for 90% of what needs to be coded in a given application this is more than adequate.  Again, SubSonic keeps it simple and it’s objects are generated to meet the most-likely conditions — not the exception. 

That being said, one might consider using SubSonic’s Query Object to build the necessary SQL to populate the TerritoryCollection right off the bat, I’m not sure this is possible since the query would require joining at least two tables. 

On the surface this appears that we’re ina quite the predicament about 10% of the time, but SubSonic has an answer.   Which bring us to potentially my favorite feature… 

Using Stored Procedures

In the cases where you aren’t able to use Query Object and you aren’t able to leverage the Fill methods exposed in the collections, you can fall back to using stored procedures.  This will help you do whatever is needed.

I thoroughly appreciate SubSonic’s stance on the use of stored procedures. Basically, if you want to use them, the DAL provides ample support. (Each stored procedure in the database is wrapped in their own custom, publicly available function.) If you don’t want to use SPs, that’s fine too.   Make no mistake about it, however.  This product’s concentration is on building dynamic queries which is smart for a performance and usability stand point.

I’ve used code generation tools which attempt to generate a stored procedure for every CRUD condition which could be dreamed up.  I found that most of the routines performed just fine.  After all, it is difficult to slow down a query which pulls back a single row filtered on a clustered index.  However, other stored procedures tried to do too much.  For example, the routine which accommodated for filtering results on one or many fields through the use of “like” and “coalesce()” abused the SQL Server.  It is this type of performance nightmare which SubSonic cleverly avoids by, first and foremost, generating SQL and but also by making existing SPs available. 

One comment about the generated SQL:  Dynamic SQL has raised a lot of red flags because of SQL injection concerns, but SubSonic is actually leveraging parameterized queries which are a safe “dynamic” SQL implementation.  For example, “select productname from products where productid = @productid;” is generated.  Prior to execution, the @productid parameter value is provided to the Command object.  This technique is as safe from SQL injection as a properly coded stored procedure simply because there is no room for injection.  Try to maliciously hack code into the @productid parameter and the command will fail entirely.

I had a chance to take a look at the algorithm to build SQL strings.  It is far less code than I expected and, as a matter of fact, it is here where I stumbled across a couple lines of code which made this entire review worth while.  Per the comment, the author really liked it too.  

   1:  //the following line is my favorite... Moe, Larry, Curly...
   2:  foreach (Where wWhere in qry.wheres) {
   3:      ...
   4:  }

Wrap Up 

After spending a few hours with the SubSonic, I don’t think its name does it justice.  I’m not saying the tool deserves to be called SuperSonic just yet, but Sonic seems perfectly reasonable.  If one were to draw a line in the sand, you still won’t find me on the side with the code generator advocates.  Maybe I’m straddling the line?  After all, I’d have to be a fool not to at least keep my eye on a tool which could potentially decrease my coding (and troubleshooting) time.  For now, let’s say that SubSonic has definitely gotten my attention.


1: Installation of StarterKit Failed: Installation stopped because the directory for the ProjectType value did not exist. The project type is invalid for your installation of Visual Studio.

2: Query the DB > Use a Collection Error: Disallowed implicit conversion from data type varchar to data type money, table ‘Northwind.dbo.Products’, column ‘UnitPrice’. Use the CONVERT function to run this query.


  1. Pingback:
  2. OK- I’m impressed. Johnny you did a great job reviewing and critiquing SubSonic. Thanks for the time spent doing so and the clarity provided for even a “SubNovice” like me.

  3. How did you feel about the choice of the ActiveRecord pattern for the DAL?

    It’s one of the things that has put me off as I prefer the Domain Object/Data Mapper/Registry patterns but am going to try Subsonic on a small project I’m working on anyway to see if I can live with ActiveRecord or, failing that, adapt it to the other patterns.


  4. I won’t claim to use the Active Record Pattern in the “manual” coding I do, however, I think the SubSonic team was wise to use it.

    First and foremost, SubSonic is an “effort by a group of .NET geeks to bring Ruby On Rails simplicity to .NET.” I’m not a Rails developer, but I understand Active Record is a core part of the language. So, I think the SubSonic team would have been hardpressed to separate the two.

    Active Record is also a relatively straight-forward patten to understand. This allows users to get up-and-running (and benefit from) SubSonic right away.

    Finally, though I don’t believe application code should necessarily mirror one’s database structure, this pattern will suffice the majority of the time.

    I think it is great you are going to give SubSonic a shot. Thanks for the comment.

  5. Pingback: Hulkster
  6. Outstanding review! Thank you for taking the time to do this, and to thoughtfully point out the things that can get better. I know (dreadfully so) that docs are sparse and I’ve been asked repeatedly to update the webcast and sample site to better reflect what we’re all about. I promise I will!

    I want to add that SubSonic also wraps your views for you in Read-only classes. You can easily solve your sorting/joining that way if you like (this is my favorite).

    Finally, the Commerce Starter Kit (the other thing that keeps my up at nite) is built using SubSonic :).

    Thanks again –
    Rob Conery (the guy who wrote the Larry, Moe, Curly quote).

  7. I want to add that I put your thoughts to work and have added in a nice start to our documentation up at CodePlex. I’ve also updated the home page to more accurately talk about what and why.

    Thanks again for the writeup.

  8. Rob, you are very welcome. SubSonic was a pleasure to review. It’s a great product which I can tell you really believe in (or I doubt you would have started putting my feedback to use already.) I look forward to keeping up with your progress. Keep us posted.

  9. Hi Johnny,

    I thought you might be interested in a little project I am working on called GENNIT ( It is a online web 2.0 style code generator which creates C# ActiveRecord code and allows you to download it in a zip from the site based on a class diagram built with the online designer. It is a very early day project that I have started myself in my spare time but thought you might be interested.



  10. Just what I needed to help me come to some sort of decission of whether or not to use this codplex…stuff!
    this is the second trip I made out to codeplex I am specially interested in the clubsite starterkit and as a cautious person I do not just in stall anything on his computer I did some checking. I was able to install the starterkit but on build time it failed I am not sure if subsonic needed to be installed along with the clubsite 3 ver. or not. But if it does then I missed something and also makes me wonder why! I guess I have to wait and see if the failure to build is the cause of subsonic notbeing present. What does this subsonic do for security!

  11. I’ve been using ROR 2.0 for a couple months and love the fact that its scaffolding generates the .rhtml and .rb files for a given model – saving me LOADS of coding time. After hearing about subsonic, I installed it in VS2005 and it works well too. However, it seems that the subsonic scaffolding occurs only at run time, and does not generate the corresponding physical .aspx and aspx.cs files. Is this by design? Does anyone know how to generate the physical .aspx and .cs files via subsonic?

  12. Great question, Bill. I think you are correct that scaffolding unfolds only at run time. I might suggest submitting your question to Rob Conery via his blog or the Subsonic project site. Best of luck.

  13. Thanks for sharing excellent informations. Your website is so cool. I’m impressed by the details that you¡¦ve on this blog. It reveals how nicely you perceive this subject. Bookmarked this website page, will come back for more articles. You, my friend, ROCK! I found simply the info I already searched all over the place and simply could not come across. What a great web-site.