Messing around with SubSonic

This post is part of a small series on .NET ORM tools. You can find the rest of them here.

I recently heard a complaint about SubSonic queries, especially with regards to joining between tables. After looking at the forums, which basically recommended replacing complex queries or joins with SPs, I decided to have a quick play around myself. Note that all of this is really quick and hacky, as it is just to get a little familiarity with the tool rather than to uncover any “best practices” or similar. If you know better ways of doing the stuff below then you are more than welcome to leave a comment. :-)

Update 11 Jan 2008: Rob has announced that development of SubSonic version 2.1 is underway. 2.1 will have an updated query tool, so chances are the queries attempted in this post will be easier in the new version. This post relates to version 2.0.

Setting the scene

I have decided to use a <sarcasm>very realistic and common scenario</sarcasm> for my tests. I have a table of suppliers, and a table of states (or provinces, territories, prefectures etc.). Both suppliers and states have names, which are stored as strings/varchars, and IDs, which are stored as Guids/uniqueidentifiers. Each supplier can service many states. So we have a simple many-to-many relationship between the two main entities. It looks a bit like this:

I am using Aussie states for my tests, so I have populated the State table with the following names: NSW, VIC, QLD, TAS, SA, WA, ACT, NT (yes, my state table holds territories). This is all sitting in a little SQL Express database that I called SubSonicWorkshop.

Setting up SubSonic

After creating a new C# class library project and adding a reference to SubSonic, I add an app.config file that looks like this*:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
<section name="SubSonicService" type="SubSonic.SubSonicSection, SubSonic" requirePermission="false"/>
</configSections>
<connectionStrings>
<clear/>
<add name="SubSonicWorkshop" connectionString="Data Source=127.0.0.1\SQLEXPRESS;Initial Catalog=SubSonicWorkshop;Integrated Security=True" />
</connectionStrings>
<SubSonicService defaultProvider="SubSonicWorkshop">
<providers>
  <clear/>
  <add name="SubSonicWorkshop" type="SubSonic.SqlDataProvider, SubSonic"
    connectionStringName="SubSonicWorkshop"  generatedNamespace="SubSonicWorkshop.DataAccess" />
</providers>
</SubSonicService>
</configuration>

I then created a Generated folder within the solution and ran SubCommander to populate it:

C:\Program Files\SubSonic\SubSonic 2.0.3\SubCommander\sonic.exe generate /out Generated

After getting VS to show all files in the solution, then including all the generated class files, we are ready to go.

Populating the database

I tend to like working in test fixtures, so I created a test fixture class and use my test runner to run the following snippets of code. Firstly, let’s write a method to encapsulate the process of creating a supplier and mapping the states it services:

private static void createSupplier(String name, String[] statesServiced) {
 Supplier supplier = new Supplier();
 supplier.SupplierId = Guid.NewGuid();
 supplier.Name = name;
 supplier.Save();

 if (statesServiced.Length == 0) return;

 Query stateQuery = State.CreateQuery().IN("Name", statesServiced);
 StateCollection states = new StateCollection();
 states.LoadAndCloseReader(stateQuery.ExecuteReader());

 //Not sure why this doesn't work (raises exception):
 //Supplier.SaveStateMap(supplier.SupplierId, states);
 //Using this instead:
 foreach (State state in states) {
   SupplierStatesServiced.Insert(supplier.SupplierId, state.StateId);
 }
}

This code creates and saves a supplier with the given name. It then retrieves a collection of states that match the state names passed in via the array, and maps these to the newly saved supplied. For some reason I couldn’t get the Supplier.SaveStateMap(...) call to work, so I did the mapping using a loop. Seeing as I don’t have SVN access through the firewall here I haven’t delved into the SubSonic source to see where things are going wrong. I then made the following calls to add some test data:

createSupplier("Dave^2 Quality Tea", new string[] { "NSW", "VIC" });
createSupplier("ORMs'R'Us", new string[] { "NSW" });
createSupplier("Lousy Example", new string[] { "TAS", "VIC" });
createSupplier("Bridge Sellers", new string[] { "QLD" });

Querying the data

First up I wanted to see if I could load all my suppliers:

[Test]
public void Should_be_able_to_get_all_suppliers() {
 SupplierCollection suppliers = new SupplierCollection().Load();
 Assert.That(suppliers.Count, Is.EqualTo(4));
}

Yep, no problems there. How about getting all the suppliers that have an “s” in their name?

[Test]
public void Should_be_able_to_get_all_suppliers_with_s_in_their_name() {
 SupplierCollection suppliers = new SupplierCollection();
 suppliers.Where(Supplier.Columns.Name, Comparison.Like, "%s%");
 suppliers.Load();
 Assert.That(suppliers.Count, Is.EqualTo(3));
}

No problems there either. Now let’s get a bit uglier. I want to navigate over the supplier-state relationship and get all suppliers that service NSW. I think this may be one of the situations mentioned on the SubSonic forums where you would probably want to use an SP to cut down the amount of chatting with the DB:

[Test]
public void Should_be_able_to_get_all_suppliers_that_service_NSW() {
 State nsw = new State(State.Columns.Name, "NSW");

 SupplierStatesServicedCollection statesServicedMap = new SupplierStatesServicedCollection();
 statesServicedMap.Where(State.Columns.StateId, nsw.StateId);
 statesServicedMap.Load();

 SupplierCollection nswSuppliers = new SupplierCollection();
 foreach (SupplierStatesServiced supplierService in statesServicedMap) {
   nswSuppliers.Add(supplierService.Supplier);
 }
 Assert.That(nswSuppliers.Count, Is.EqualTo(2));
}

The code above first gets the state we are interested in (NSW), and then gets all the mappings that feature this state. It then loops through all the mappings and adds the supplier the the SupplierCollection. This worked, but I’d hate to use that with more than a handful of records.

I think in the last situation you’d probably want to use an SP (or direct SQL). Because SubSonic generates partial classes, you could also add a method to State, StateCollection or StateController like FetchByServicedState(String stateName) to do this in a reusable manner.

That’s it for now. I’m going to have a quick play with doing this in NHibernate and LinqToSql.

* I know I prevoiusly committed to giving colourful code samples, but I ran in to a few hurdles with the tool and am sticking to plain, boring code samples for now.

Comments