SubSonic 3.0 Preview 1: Linq Has Landed

Friday, November 07, 2008 -

I've been working a lot over the last few months on our next rev of SubSonic, and I think I have something that's good enough to issue as a preview. Please understand that this is a preview in every sense of the word, and may shatter into a zillion pieces and make you want to pour your Red Bull over my head. This is bleeding edge edge crazy talk. It's everything bad that wakes you up screaming at night, and quite possibly is the coolest thing I've ever made... or somewhere in between. Just know that if you download and play with it, you're in for a ride... maybe. Or maybe it will shine like a million suns at the dawn of man...

SubSonic 3.0 Is...
A complete rewrite. The culmination of the things I've learned over the last year as I've (once again) Changed Everything and shared some wonderful Koolaid with my Alt.NET buddies :). Seriously - I've ripped everything out and redone it, with a focus on:

• Simpler
• Lighter
• Simpler
• Meaner
• Simpler
• Better
• Faster
• Simpler

(Not in that order - but generally I've placed an emphasis on simplicity). I've installed interfaces everywhere, and focused on making everything lightweight, testable, and injectable. I hope. We'll see.

Overall I've had exactly 5 false starts. I literally have 6 folders on my hard drive right now, each named "SubSonic 3.0 [X]" where "X" is the iteration number. Sometimes it's a swear word - but I won't go there now. Linq is hard - no really - just ask Ayende. It's not a matter of smarts - it's just really hard to try to fit Linq in... trust me. I was hoping originally that I could just create an Expression parser and build stuff using our core bits... but umm... that didn't happen.

One of the blogger people I've been following as I've been working through all of this is Matt Warren, the Zeus of Linq To Sql. He has a great series of posts on how to implement IQueryable, and when he got to post number 9 I began to think I should just use his code wholesale (yes, I know it's cheap of me). He's now on post number 11, and I asked him a while back if I could just steal all of his code - and this is why Matt Warren is my personal Santa Claus - he said "yes - it's all under MS-PL anyway. Just forward the license". 

So there it is - the SubSonic.Linq namespace is 99.999999% Matt Warren's code. I've tweaked it a touch to work with our core Query stuff, but everything else is him and his large brain. Thank him for this and have a read on what you get with this provider.

My philosophy while developing SubSonic 3.0 is this:

1. I really dig Linq as a language feature - I want to include it as a query tool
2. I don't like all the extra "shmekt" that we have right now for things like Code Generation and figuring out how to rename bad table names
3. I want to change our Provider Model - this can be much simpler
4. I want to give you a lot more power to make code you want to use
5. I want to use POCOs
6. I want Linq to work where it should, SubSonic to work where it should, and seemlessly make them work together
7. If I do this right, you can build on top of SubSonic, to do what YOU want to do.

There it is. And I want it to be stupid simple.

SubSonic 3.0 Is Not...
I think this is as important as what I want to do. So, what I don't want to do is:

• Take over as the new Linq To Sql. I know this is weird timing, given the latest grumblings happening in .NET space, but believe me Linq is scarey, and translating Expressions to SQL is not easy. It's really, really hard (see below) and if I didn't like Linq so much (and IQueryable) I would have kicked it to the curb.
• Create another ORM. SubSonic's not an ORM believe it or not. There is no "mapping" ability really - I spose you can say it's "ORM-y". It's a data access tool for sure - I'll leave the mapping to you, which is why I decided to use Linq cause it makes is very simple.
• Suggest in any way that we're done. There's some work to do here :) so this is for you to play with sort of like a sleeping cat (which could scratch you) or a pocket full of kryptonite. It's preview and may splode.
• Backwards Compatible. Yes I know this will bum a lot of people out but it's the only way I can really rev this thing properly. Eric and I stressed over this, but the issue is that language/tooling features are letting us do something completely different and if it helps trim the API, bloat, and footprint than that's what we do. Very sorry about this.

Onward! To Some Code!

The Setup
To use SubSonic 3.0 you'll need to do exactly 3 things (assuming you're using .NET 3.5, which is required):

• Create project references to SubSonic, System.Data, and System.Core (if you don't have it already)
• Make sure you have a connection string (at least one), with the providerName set appropriately (usually it's System.Data.SqlClient)
• Drop in the T4 templates, edit the connection string in "Connection.tt", and watch it generate your action.

No providers, no funkiness. That's it!

Generated Code
There are fiveT4 templates as of right now (this will probably change), and they are:

1. Connection.tt - this is a non-code template (utility) that gets included with the other templates and does things like connect to your DB to get a list of tables and SPs, etc.
2. Classes.tt - this T4 template generates 1 class per table in your DB using POCOs
3. Provider.tt - this class wraps your Database in a class and adds IQueryable<T> fields as well as some factory methods for SubSonic queries (more below).
4. SPs.tt - wraps your Stored Procedures and "appends" them to your provider class so you can call them as methods
5. Structs.tt - wraps the columns and tables in your database so you can refer to them without using strings.

There's a lot more you can do here - and that leads me to the next thought...

This Aint No ORM Disco
The "Big Guy" ORMs (NHibernate, EF, Linq To Sql) keep track of an object's state and interacts with the DB in what's known as a "Unit of Work". This can be great sometimes, a PITA others. I've realized that ORMs are a slippery slope in this regard, and no one can really agree on what they should really do, when, and why.

So I've decided to focus one step below and try to make data access and manipulation silly simple. That way, in the future, maybe I (or you!) can work up some cool ORM templates to sit on top of the core SubSonic bits, and we can all share and have a party.

Just know that my focus, for right now, is to make working with the DB bloody crazy simple. The more complicated stuff can come later (eager loading, unit of work, etc) in the form of T4 templates.

Enough Already - Show Me Some Code!
The meat of the system is worked up by Provider.tt. This is a "wrapper" for your database and allows you to work with IQueryable and SubSonic at the same time:

namespace Northwind{
    public partial class DB{
    
        public IDataProvider DataProvider = new DbDataProvider("Northwind");
        public  DbQueryProvider provider;

        public Query<CustomerDemographics> CustomerDemographics;
        public Query<Region> Region;
        public Query<Employees> Employees;
        public Query<Categories> Categories;
        public Query<Customers> Customers;
        public Query<Shippers> Shippers;
        public Query<Suppliers> Suppliers;
        public Query<EmployeeTerritories> EmployeeTerritories;
        public Query<Order_Details> Order_Details;
        public Query<CustomerCustomerDemo> CustomerCustomerDemo;
        public Query<Territories> Territories;
        public Query<Orders> Orders;
        public Query<Products> Products;        
        
        public Select Select() {
            return new Select(DataProvider);
        }
        public Insert Insert() {
            return new Insert(DataProvider);
        }        
        public Update<T> Update<T>() where T:new(){
            return new Update<T>(DataProvider);
        }
        public Delete Delete(){
            return new Delete(DataProvider);
        }

There's more that's generated here - but I'll get to that in a second. To use IQueryable, you do what you normally do with Linq To Sql:

    Northwind.DB db = new Northwind.DB();
    var products = from p in db.Products
                   where p.CategoryID == 5
                   select p;
    foreach (var p in products) {
        Console.WriteLine(p.ProductName);
    }

Which produces what you might think (using delayed execution):

Nothing really extraordinary about this - we've seen it before :). Most things are covered using the Linq provider that Matt started, but I'm sure there are some things missing - the good news there is that you have SubSonic's query tool to back you up if you get stuck (which is a lot of fun since you're not locked into one way of doing things now):

    var products2 = db.Select.From<Northwind.Products>()
        .Where(Northwind.ProductsTable.CategoryID)
        .IsEqualTo(5)
        .ExecuteTypedList<Northwind.Products>();

Note that you can also just use a string in the Where() statement.

And if you really get stuck, we've got your back so you can avoid all of the "Proprietary Object Noise" (with apologies to Jeff Atwood):

    var products3 = new CodingHorror("SELECT * FROM Products WHERE CategoryID=@id", 5)
        .ExecuteTypedList<Northwind.Products>();
    foreach (var p in products2) {
        Console.WriteLine(p.ProductName);
    }

I Like Lambdas
I've tried to rethink everything in terms of how the bits are put together so we can flex the changes in the language. To that end I've included lambdas where appropriate to add some type-safety etc. I know some people don't like em, but they're optional, as always.

    db.Insert.Into<Northwind.Region>(x => x.RegionID, x => x.RegionDescription)
        .Values(6, "Hawaii").Execute();

    object avg = db.Avg<Northwind.Products>(x => x.ProductID);

The latter example here is why I love lambdas so much - they can (if implemented properly) "terse up" your code nicely. You could use Linq on db.Products for this as well - but I like the terseness of this one line :).

All of our aggregates are now reachable from the db wrapper class:

    db.Avg<Northwind.Products>(x => x.ProductID);
    db.Sum<Northwind.Products>(x => x.ProductID);
    db.Count<Northwind.Products>(x => x.ProductID);
    db.Min<Northwind.Products>(x => x.ProductID);
    db.Max<Northwind.Products>(x => x.ProductID);
    db.Variance<Northwind.Products>(x => x.ProductID);
    db.StandardDeviation<Northwind.Products>(x => x.ProductID);

The best part is that if you don't like any of this, just change your template! It's all right there for you to tweak and alter to fit your project, which is a major goal of mine here: I want to give you the power to use SubSonic as you please. And if you do it better than us (which I'm sure you will) - perhaps you'll share. My focus is on the engine - you guys can hop it up with chrome and neon.

The biggest support issue we've had is with regards to our generated stuff. Specifically how we name objects. Our code generation is now completely transparent so if you don't like it - you can change the name of that table to be what you like!

Using System.Data.Common
At the core of the flexibility we now have is our use of System.Data.Common and the DataFactory pattern. If you don't know what this is, have a read here. The idea behind System.Data.Common is that we shouldn't have to rewrite all of our data access stuff if we happen to change database platforms. To that end the DataFactory pattern was created so that ADO data provider developers could write against a common API, and you could benefit.

We've used that to our advantage with SubSonic 3.0. So if you need to switch your DB provider, for instance, you can change from this:

    <connectionStrings>
        <add name="Northwind" 
        connectionString="server=.\SQLExpress;Integrated Security=true;database=northwind;" 
        providerName="System.Data.SqlClient"/>
    </connectionStrings>

To this (provided you have the DLL in your /bin):

    <connectionStrings>
        <add name="Northwind" 
        connectionString="server=localhost;user id=root;pwd=HAHA?;database=northwind;" 
        providerName="MySql.Data.MySqlClient"/>
    </connectionStrings>

There is no other setting - no "provider tweaks", no jiggity dances and prayers. The only assumption here is that you have the same DB on your target platform (which I do - I've ported Northwind to MySQL) and run:

Most database providers support ADO.NET 2.0 and the Data Factory. Some off the top of my head that I know of are MySQL, Oracle, and SqlLite and I'm sure there are others - I just don't know.

You can download SubSonic 3.0, Preview 1 here.

We're still actively working on it and I'm sure you'll encounter bugs, etc. Feel free to send me an email (see the README) and please try to let me know what you were doing in the context of Northwind, so I can repro the issue.

As always, please be patient.

Epilogue: SubSonic.Sugar is now SubSonic.Extensions.
Wanna have some fun? Have a look at the SubSonic.Extension methods that I've ported from the Sugar class and turned into applicable extensions. To use them, you'll need to make sure you reference the appropriate namespace:

    using SubSonic.Extensions.Strings;
    using SubSonic.Extensions.Dates;
    using SubSonic.Extensions.Files;
    using SubSonic.Extensions.Linq;
    using SubSonic.Extensions.Numbers;
    using SubSonic.Extensions.Objects
    using SubSonic.Extensions.Validation;

In there is some fun, and some damn handy utilities. Things like Date math, file creation and text loading, string pluralization, regex validation and matching (Validation's "Is" functions rock - things like "IsValidEmail" and "IsCreditCard" are super groovy).

I mention them last as they are, literally, sugar utilities to help you through your day, and help us with SubSonic. They're not the main point at all.