Over the last week I’ve made massive improvements and upgrades to SubSonic 3.0 and the templates that it comes with. I haven’t been this obsessive and inspired since I created SubSonic in the first place, and I have to tell you I am more than stoked to see this pull together.

The Foundation
I’ll be really concise with this post – there’s so much here … it will be hard but I really want to keep to the point. SubSonic 3.0 is a Layer Cake which is focused on openness and transparency. Here are the layers:

1. Core: Data Access and SQL Translation. This is the part that executes commands against the database, and translates queries (SubSonic’s or Linq’s) into SQL.
2. Surface: Working with your database. This layer “surfaces” your database and implements “IQuerySurface” (I couldn’t think of a better name). In short, it exposes every table as IQueryable<T>, every Stored Procedure as an executable method, and also provides you with Aggregation queries (Count, Sum, Min, Max, etc).
3. Implementation: Your Favorite Data Pattern. This can be Repository, ActiveRecord… whatever. I’ve created (with this release) an IRepository pattern for you that I think covers 99% of the needs out there (you’ll see in a moment) and also allows for some nice testability.

Of these, the only thing that is locked down is part 1 – The Core (as part of our DLL). The rest is built using T4 templates that you add to your project. So if you hear me constantly say “you can change this as you need to in the templates” – well it’s because you can. These aren’t hard to learn, and are created using plain old C# code so changing/altering as you need should be easy.

In fact I was explaining to Steve Harman yesterday that if you wanted to create a full-blown Unit Of Work ORM, you could. You could also create an ActiveRecord pattern too. Really – it’s that flexible.

The “Surface”
There’s probably a better name for this – but it seems to capture what I’m after: a simple, easy to understand way to work with your database. This template (called “QuerySurface” – which used to be “Provider”) exposes the tables in your database as IQueryable<T> so you can work against them using Linq:

            Northwind.DB db = new DB();
            var result = from p in db.Products
                         where p.CategoryID==5
                         select p;

            foreach (var item in result) {
                Console.WriteLine(item.ProductName);
            }

I can go off here and show you all the ways to query with Linq. But you’ve seen em before and hopefully you won’t notice the difference with SubSonic. If you’ve used Linq To Sql, this will look extremely familiar to you. However know that my approach differs from Linq To Sql in that there is no change tracking and need to have a “context”. The DB, in this sense, is simply a wrapper.

One thing you might not have seen with Linq To Sql is the ability to run Updates and Inserts, which I’ve always missed and have now implemented with SubSonic 3.0:

            db.Update<Products>().Set(
                x => x.Discontinued == false,
                x => x.ReorderLevel == 100)
               .Where(x=>x.Category==5)
               .Execute();

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

I know a lot of people don’t like the Lambda thing – you can still use our old query tool (with structs or strings in place of these lambdas) if you like.

This one’s a personal favorite – deleting has become a one-line operation:

            db.Delete<Region>(x => x.RegionID == 6).Execute();

This next thing is what I consider to be a “Killer Feature” – a batch query (or as NHibernate calls it, a “Future” query). One thing that always gets me about ORMs is having to manage the connections that it makes to a database. Wouldn’t it be nice to lob a bunch of IQueryable queries into a batch querier and have it make only one call to the DB, in one execution step? If you think so, I have good news for you:

    db.Queue(from p in db.Products where p.CategoryID == 5 select p);
    db.Queue(from p in db.Products where p.CategoryID == 7 select p);
    db.Queue(from p in db.Products where p.CategoryID == 9 select p);
    IDataReader rdr=db.ExecuteBatch();

With this release I’ve included a new “BatchQuery” class that takes either one of SubSonic’s queries or an IQueryable query, parses the SQL and pulls out the parameters into one, big batched SQL statement. It then will execute as a pass-through, or return a multi-result set. Yes, I will do my best to support transactions this way in the future .

If you don’t like working with DataReaders, never fear, one of the new members of SubSonic’s Extension classes (DatabaseExtensions) has a new method for IDataReader called “ToList<T>()” – which will take a reader and shove the results into a “List<T>”.

So to complete this example (making sure to reference SubSonic.Extension.DatabaseExtensions):

    db.Queue(from p in db.Products where p.CategoryID == 5 select p);
    db.Queue(from p in db.Products where p.CategoryID == 7 select p);
    db.Queue(from p in db.Products where p.CategoryID == 9 select p);

    List<Products> result1 = null;
    List<Products> result2 = null;
    List<Products> result3 = null;

    using (IDataReader rdr = qry.ExecuteReader()) {
        result1 = rdr.ToList<Products>();

        if (rdr.NextResult())
            result2 = rdr.ToList<Products>();

        if (rdr.NextResult())
            result3 = rdr.ToList<Products>();

    }

One SQL statement, one execution step, 3 typed lists. I like it .

Believe it or not you probably shouldn’t work “this close” to your database. This is where the 3rd tier comes in.

The Implementation
Hopefully you can see what I’m trying to do here – give YOU the power. By way of example I’ve created an IRepository<T> interface that’s part of SubSonic:

    namespace SubSonic {
        public interface IRepository<T> {

            IQueryable<T> GetAll();
            PagedList<T> GetPaged(int pageIndex, int pageSize);
            IQueryable<T> Find(Expression<Func<T, bool>> expression);
            void Add(T item);
            int Update(T item);
            int Delete(T item);
            int Delete(object key);
            int Delete(Expression<Func<T, bool>> expression);

        }
    }

One of the templates you’ll see in the download below is “Repository.TT”, which is an implementation of this interface that you can use right now if you like. One day I might make an ActiveRecord one as well – but for now you can use this code like this:

            NorthwindRepository<Region> repo = new NorthwindRepository<Region>();
            Region r = repo.Find(x => x.RegionDescription == "Hawaii").SingleOrDefault();
            r.RegionDescription = "Hawaii FIVE O";
            repo.Update(r);

Again, one of my very favorites is “Delete”:

            NorthwindRepository<Region> repo = new NorthwindRepository<Region>);
            repo.Delete(x=>x.RegionID==6);

You can also use the item, or a primary key:

            repo.Delete(6);
            //OR
            Region r=repo.Find(x=>x.RegionID==6).SingleOrDefault();
            rept.Delete(r);

Let’s Watch a Video!
A lot of people have asked for a “Rob special” video, and not to disappoint I’ve created one to show you how to get started (props to whomever knows the tune). It’s 4 minutes and covers how to use SubSonic 3.0 (and its templates) with a Console App. You can also use this with any other app EXCEPT for a Web Site – for some reason T4 doesn’t like those:

(Double-click for full screen, or you can download the WMV here).

The Download

You can download Preview 2 here. I’ve fixed/tweaked/changed a lot of stuff, including:

• Fixed an issue with Columns not generating. I think this was because I wasn’t closing connections properly, and some databases were case-sensitive. I’ve tested this on 2008 and 2005 and it works well.
• Made every class Partial
• Changed tables from Fields to Properties
• Fixed a paging bug with the Linq translator
• Renamed some TT files. Provider has become “QuerySurface” and Connection.TT is now called “_Utility.tt”.
• Renamed the no-code, required TT files to have underscore
• Implemented MANY of the changes suggested in this post by jguerts (thanks!)

As always- if you have any questions please let me know. If they’re long ones, please email me at microsoft (robcon) or my full name at Gmail.

Cheers!

Tags: LINQ

This entry was posted on Wednesday, November 12th, 2008 at 11:18 pm and is filed under SubSonic. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.