SubSonic: Version 2.1 (Pakala) Preview: The New Query Tool

Thursday, January 10, 2008 -

Eric and I have been working just about non-stop on our bug list as well as some very cool innovations that he and I have cooked up over the last six months. We've had to wait a bit for the dust to settle in the .NET world (not to mention our personal lives), but I think the wait has been worth it. Over the holidays I was able to create the querying API I've been thinking on for the last year, and it has come together nicely. I'd like to show you what I've come up with, and ask for your comments and help before we release it with version 2.1 of SubSonic.

The Next Rev
This is going to be a long post, so I'm going to be as brief here as I can. We have an issue with how to rev SubSonic with respect to loving both .NET 2.0 and .NET 3.0. I've noodled on this a lot, and I think our current plan is the smartest:

Rev 2.1 (Pakala) will be the last .NET 2.0 version of SubSonic. We will support bugs, as always, and will release support patches, but this is the last major rev for .NET 2.0.

I'll talk more about 3.0 stuff in a later post - for now I want to focus on the upcoming good stuff. One thing you might note is that I've decided to go ahead and finish what I started with the new Query tool. I had some inspirations over the holidays (and lots of boring hours to fill) and I fiendishly created (what I think) is our coolest feature yet: a very LINQy Query Tool for .NET 2.0.

The New Query Tool: Query2
I was pretty bummed out after writing my post on which tool to use for your Data Access using MVC. Don't get me wrong - I really love everybody, but I'm a selfish brat and I really want SubSonic to be world class and at the top of the list when it comes to ... well everything :). So over the holidays I completely geeked out (more like OCD overdosed on caffiene and chocolate) and finished up this project that's been sitting around for far too long.

My "story" - or focus - was simple:

• Create a "LINQy" experience for .NET 2.0, using every trick I could think of to create a fluent API
• Create a query tool where you would be able to do 95% of all the queries you'd need, in a readable, discoverable way (which is up from 80% with our initial release)
• Make Jeff Atwood praise me :):) and SubSonic for not having any "Object Noise" and creating something that's very, very close to SQL (and in which you can even "just use sql").

That's it really - I'm selfish and I want Jeff Atwood to love me. That's the secret sauce in SubSonic.

Being serious for a second - I get asked about LINQ a whole bunch. We have a plan for LINQ - specifically LINQ to Sql - and as I mention above I'll get into that in a later post. For now please know that this query tool is for .NET 2.0 (though it will indeedy be usable in 3.0). I'm not into competing with Microsoft (and never have been) - they (we) can do the heavy lifting (create the platform) - I'll just make it pretty.

Query Factories
With Pakala, I've updated the code generation bits so that you have a factory built off of your Provider. So if we're using Northwind (and our namespace is Northwind), you can use:

• Northwind.DB.Select
• Northwind.DB.Insert
• Northwind.DB.Update
• Northwind.DB.Delete
• Northwind.DB.Query (an adhoc query - I'll explain more below)

The Code, Please
Joins, Inserts, Updates, Typed Lists, Collections, INs, Nested Statements, Parametered Literals - It's all in there. Rather than babble - here's the code that shows what you can do. I've tried to make this very readable - hopefully you can tell what's going on:

Northwind.ProductCollection products = 
    Northwind.DB.Select().From("Products")
    .Where("categoryID").IsEqualTo(5)
    .And("productid").IsGreaterThan(50)
    .ExecuteAsCollection<Northwind.ProductCollection>();

A Joined Collection:

Northwind.CustomerCollection customersByCategory = new Select()
    .From(Northwind.Customer.Schema)
    .InnerJoin(Northwind.Order.Schema)
    .InnerJoin(Northwind.OrderDetail.OrderIDColumn, Northwind.Order.OrderIDColumn)
    .InnerJoin(Northwind.Product.ProductIDColumn, Northwind.OrderDetail.ProductIDColumn)
    .Where("CategoryID").IsEqualTo(5)
    .ExecuteAsCollection<Northwind.CustomerCollection>();

You can use any type of JOIN you want here - Inner, Outer, Right/Left Inner/Outer, Cross, Unequal - it's up to you.

And now (this is where we get sorta LINQy) - you can pass in any "type" of object you want, and have it returned in a typed list. So let's say we make a condensed class:

class TestProduct {
    private int _id;
    public int ProductID {
        get { return _id; }
        set { _id = value; }
    }
    private string _name;
    public string ProductName {
        get { return _name; }
        set { _name = value; }
    }
    private decimal _price;
    public decimal UnitPrice {
        get { return _price; }
        set { _price = value; }
    }
}

You can use this class in a Query expression:

List<TestProduct> result = new 
    Select("productid", "productname", "unitprice")
    .From(Northwind.Product.Schema)
    .ExecuteTypedList<TestProduct>();

Constraint Expressions
If you have some complicated expressions, you can add those in too:

Northwind.ProductCollection products = new Select(Northwind.Product.Schema)
    .WhereExpression("categoryID").IsEqualTo(5).And("productid").IsGreaterThan(10)
    .OrExpression("categoryID").IsEqualTo(2).And("productID").IsBetweenAnd(2, 5)
    .ExecuteAsCollection<Northwind.ProductCollection>();

Anything following "WhereExpression" (or Or/AndExpression) will be wrapped in parentheses. You can close the expression by using "CloseExpression()", or it will be closed for you if another is started (as with OrExpression above) or if the query ends.

Aggregates
You can use any Aggregate function you want as well:

double result = new 
    Select(Aggregate.Sum("UnitPrice*Quantity", "ProductSales"))
    .From(Northwind.OrderDetail.Schema)
    .ExecuteScalar<double>();

I should mention here that you can "new up" the query (using new Select()...) or you can use Northwind.DB.Select if you like the factory approach - it's up to you.

Paging
IDataReader rdr = Northwind.DB.Select("ProductId", "ProductName", "CategoryName")
    .From("Products")
    .InnerJoin(Northwind.Category.Schema)
    .Paged(1, 20)
    .ExecuteReader();

I should mention here that we're taking into account which DB you're using, and if it's SQL 2005, we'll use ROW_COUNT to generate the paged bits (which is something people have asked for a lot).

Params - I <3 The Params Keyword
I've gone nuts with the "params" keyword - which allows you flexibility when sending values in as an argument. You can see this in action with our Select() constructor (see above):

Select("productid", "productname", "unitprice")

I've added it the In() and NotIn() constraint methods as well:

int records = new Select().From(Northwind.Product.Schema)
    .Where("productid").In(1, 2, 3, 4, 5)
    .GetRecordCount();

Another fun way to use In() is with a nested Select() - this is recursive by the way, so go nuts if you like:

int records = new Select(Northwind.Product.Schema)
    .Where("productid")
    .In(
        new Select("productid").From(Northwind.Product.Schema)
        .Where("categoryid").IsEqualTo(5)
        )
    .GetRecordCount();

Insert uses params and nested Select() statements as well:

int recordsAffected = new Insert().Into(Northwind.Category.Schema)
    .Values("Test", "TestDescription", DBNull.Value)
    .Execute();

int recordsAffected = new Insert().Into(Northwind.Category.Schema)
    .Select(new Select("CategoryName", "Description", "Picture").From(Northwind.Category.Schema))
    .Execute();

You may have noticed that you can use one of three things in a Select() constructor - a column list, a table schema, or nothing at all. If you pass in the schema you can save yourself from typing "From(...)" but you lose a little readability. If you pass in nothing then you'll need to pass in a from - either way, with the latter two, a fully qualified column list will be built for you.

Updates are pretty simple as well:

int recordsAffected = new Update(Northwind.Product.Schema)
    .Set("UnitPrice").EqualTo(100)
    .Where("productid").IsEqualTo(1).Execute();

We've been asked a few times to support SQL Expressions here - in other words allowing for "UPDATE table set column=column+1", and you can now do that using SetExpression:

int records = new Update(Northwind.Product.Schema)
    .SetExpression("UnitPrice").EqualTo("UnitPrice * 3")
    .Where("productid").IsEqualTo(1)
    .Execute();

Deletes work much the same way - and hopefully you're seeing a pattern here:

int recordsAffected = Northwind.DB.Delete()
    .From(Northwind.Region.Schema)
    .Where("regiondescription").Like("test%")
    .Execute();

Doing It All At Once With InlineQuery
One thing we didn't have, necessarily, is a "back door" in SubSonic so you could let yourself out after coding yourself into a corner :). This can happen with any ORM tool and it's one major reason I've embraced Views and Stored Procs for the more complex stuff. But sometimes (if you're Jeff Atwood) you might not want to deal with our API - just get your data.

For you, I made InlineQuery. It will execute your query happily, and parameterize it too so you don't get yourself SQL-injected:

Northwind.ProductCollection products=
   new InlineQuery()
    .ExecuteAsCollection<Northwind.ProductCollection>
    ("SELECT productID from products WHERE productid=@productid", 1);

SubSonic will see "@productid" and build a parameterized statement for it on the fly, then execute the results into a collection for you. Embedding SQL is almost never optimal - but if you need it, we got ya covered.

In some cases this can work in your favor, however. Each of the methods you've seen (Select, Update, Insert, Delete) has the ability to return their SQL by using "BuildSqlStatement()". You can use this for batch operations - like multiple inserts:

StringBuilder sb = new StringBuilder();
//insert a test product

sb.AppendLine(new Insert().Into(Northwind.Region.Schema).ValueExpression("'test1'").BuildSqlStatement());
sb.AppendLine(new Insert().Into(Northwind.Region.Schema).ValueExpression("'test2'").BuildSqlStatement());
sb.AppendLine(new Insert().Into(Northwind.Region.Schema).ValueExpression("'test3'").BuildSqlStatement());
sb.AppendLine(new Insert().Into(Northwind.Region.Schema).ValueExpression("'test4'").BuildSqlStatement());
sb.AppendLine(new Insert().Into(Northwind.Region.Schema).ValueExpression("'test5'").BuildSqlStatement());
sb.AppendLine(new Insert().Into(Northwind.Region.Schema).ValueExpression("'test6'").BuildSqlStatement());
string sql = sb.ToString();

//insert the values
new InlineQuery().Execute(sql);

There are a lot of ways that these things can work together - and hopefully you're starting to see the "interop" pattern I'm going for here.

Transactions
Transaction work as they always have with SubSonic, but I've added a quick way to do it so you don't have to keep writing all that using{} code:

List<Insert> queries = new List<Insert>();
queries.Add(new Insert().Into(Northwind.Region.Schema).Values("test1"));
queries.Add(new Insert().Into(Northwind.Region.Schema).Values("test2"));
queries.Add(new Insert().Into(Northwind.Region.Schema).Values("test3"));
queries.Add(new Insert().Into(Northwind.Region.Schema).Values("test4"));
queries.Add(new Insert().Into(Northwind.Region.Schema).Values("test5"));
queries.Add(new Insert().Into(Northwind.Region.Schema).Values("test6"));
queries.Add(new Insert().Into(Northwind.Region.Schema).Values("test7"));

//execute in a transaction
SqlQuery.ExecuteTransaction(queries);

When?
Well, that's up to you in large part. I've touched on perhaps 60% of the functionality we now have with this tool in this post, and that means I need to test it to the nines before it goes out the door. I have about 44 unit tests (if there's a chance you can have negative code coverage... well I'm close) - but need a whole bunch more before I'm happy with it. If you'd like to help push this out the door, you can:

• Head over to our source, and grab the latest.
• Open up SubSonic.Tests, and start adding Unit Tests for anything you please
• Find as many weirdnesses/bugs as you can, and either fix them or report them on our forums
• Docs! HALP!

Patches are the life blood of our project - and I'd be extremely happy to have yours and/or any documentation you'd like to help with. If you're into it - leave a message on our forums and I'll make sure to sign you up!