SubSonic 3.0: ActiveRecord

Wednesday, June 10, 2009 -

Google’s project site is down for a while and I have some perf tests running in the background so I thought it might be time to crank out a few more “preview” posts of SubSonic 3.0. This one’s about ActiveRecord – one of my favorite patterns for its ease of use and versatility.

Elevator Summary
ActiveRecord is for people who want the biggest bang for their buck in terms of working with their data. It raises the database implementation right into the application as each object represents, literally, a row in the database.

Ruby on Rails uses the ActiveRecord pattern to great success – essentially telling people to “get over it”. Well, actually they say “F*** You” – but this is a family blog :) and it would be nice to keep this above board.

ActiveRecord is extremely intuitive and simple to use, but it does have its drawbacks – one of which is testability. But, as you’ll see, SubSonic has some magic here for you. Currently ActiveRecord works with SQL Server, MySQL, and SQLite.

Setting Up ActiveRecord
As with all things SubSonic 3.0 – ActiveRecord is implemented using a set of T4 Templates, which automatically generate the code you need within Visual Studio:

These templates are divided into 4 parts:

• SQLServer.ttinclude talks to SQLServer to get information about your tables
• Settings.ttinclude sets your connection string, database name, namespace, and other goodies (like the names of your classes, etc)
• Context.tt is an IQueryable layer and is akin to the DataContext in Linq to Sql
• ActiveRecord.tt is the class generator

You just set the connection string name in Settings.ttinclude, as well as the namespace, and you’re off to the races.

Working With ActiveRecord
This is designed to be brain-dead simple with no configuration or contexts to set, etc:

var product = Product.SingleOrDefault(x => x.ProductID == 1);

In the old days getting a record from ActiveRecord meant passing the key in through the constructor – but we’ve replaced that with a factory call so you can get a nice null back if the product doesn’t exist.

Each object is created with an IQueryable<T> reference to it’s foreign-key friends. These are two-way references, so you’ll get all objects that reference your main table.

You can grab a list like this:

var products = Product.Find(x => x.ProductID <= 10);

If you want your list paged, you can do that too:

var products = Product.GetPaged(1,10);
Assert.Equal(10, products.Count);
Assert.Equal(100, products.TotalCount);

Finally – if you want to work with LINQ you can using the factory method “All()”:

var products = from p in Product.All()
          join od in OrderDetail.All() on p.ProductID equals od.ProductID
          select p;

Assert.Equal(500, products.Count());

The factory methods are pretty complete and are pretty much sugar for a very Linq-y type of interaction:

The methods at the bottom there - “Setup()” and “ResetTestRepo()” are the goodness which I’ll go into now.

ActiveRecord Is Was Hard To Test

One of the things that people asked for when we rolled out SubSonic 2.0 was better testability. I remember asking Phil about this one day and said “isn’t about time we build this crap in?” and he agreed – but we couldn’t figure out the best way to do it. I thought I’d take a stab at it anyway – so here goes.

Let’s say you have a nice ASP.NET MVC application you’re working up, and you’re a being a Good Person and you’ve created a nice Test Project as well. Now you want to test your ProductController but you have plugged in SubSonic’s ActiveRecord and you don’t want to hit the database because it might make your test fail due to issues not under test (like bad data, for instance).

The good news is you can intercept the call to the database and effectively “Auto-Fake” the ActiveRecord repository, and how you do it is the funnest part. This is the App.Config in a sample Test Application:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
        <add name="Site" connectionString="Test"/>
    </connectionStrings>
</configuration>

If SubSonic sees that the the connectionString is “Test”, it will automatically work with an in-memory repository, not use a database. You can test this out by writing a test:

Product.Setup(100);
var products= Product.All().Count();
Assert.AreEqual(100,products);

By using the “Setup()” method, you’re filling the TestRepository that SubSonic uses under the covers – which is basically an IList<Product>. You can tell it how many items you want created or you can pass in your own list.

CRUD operations work as well:

Product.Setup(10);
Assert.AreEqual(10, Product.All().Count());

var newProduct = new Product();
newProduct.ProductID = 1000;
newProduct.Save();
Assert.AreEqual(11, Product.All().Count());

Album.Delete(x=>x.ProductID==1000);
Assert.AreEqual(10, Product.All().Count());

The interception will work 90% of the time for your application – however there are some circumstances where we can’t intercept the DB call – primarly because we don’t know, at the level, that you’re using ActiveRecord.

One of these places is our Linq implementation. It doesn’t know what’s calling it or why – so it won’t know it’s ActiveRecord and moreover won’t know if it’s under test – so we can’t intercept it. It should be a simple matter to test at the execution level of the connection string is “Test”, but the way we work with connections doesn’t allow for this – an Exception is thrown if a connection string can’t be parsed by the DbFactory.

Summary
One thing that I hope will become clear as we launch SubSonic 3.0 is that it’s really a core framework for you to build your DAL on using T4 templates. This is approach #2 to working with your data – tomorrow I’ll go into George Capnias’ excellent “Advanced Templates” which flex the Repository Pattern to present a very Linq to Sql-style experience.

I’ll also have a lot more to say about these on our forthcoming docs site (which I’m 70% of the way through).