At some point you need to stop polishing and let it go already – so I did just that today: SubSonic 3.0 is released. This thing took me just about a year to finish up, and a lot of work has gone into it.

So, what’s new? Well…

• A nice Linq engine which parses IQueryable “stuff” into SQL (thanks to Matt Warren’s excellent work with the IQToolkit, which we stole mercilessly).
• A templating system which allows you to use ActiveRecord, good old Linq in Linq to SQL style, or whatever floats your boat.
• A SimpleRepository which is just about zero-drag and even builds your database for you
• A brand-new project site that will hopefully answer the question “is SubSonic dead”? Or probably not
• A new docs site that has permanently crushed my ability to type. It’s a wiki so if you feel like being helpful and like SubSonic and want to help… nudge… nudge

Should You Care?
That’s a tough question – so it’s the first I’ll address.

• If you like Linq and you’re not working with SQL Server and EF is a bit much for you – yes.
• If you like Linq and you are working with SQL Server… well nobody ever got fired for using Microsoft so “maybe”. Linq to SQL is awesome and it’s not dead .
• If you like a “low-friction” approach from a helper framework – then yes. You can’t get much lower-friction then SimpleRepository.
• If you like SubSonic to begin with, then yes. There’s still the same grooviness here.

Should You Be Afraid?
Hopefully not – I’ve really tried to test the tar out of this thing before letting it go (that’s why this took me so long). I’ve had a lot of help with this (many thanks to Eric, Paul, George Capnias, and Jim Zimmerman) and a lot folks reporting bugs (and a TON) of patches so hopefully it’s solid. That said – this is a major release and it’s also Open Source. You will need to be a bit flexible and hopefully you won’t mind helping us out. Which brings me to my final point…

Our Repo Has Moved. Yes, Again
All was going very well until 3 things happened:

1. I needed to put the 3.0 repo together with the 2.0 repo. It’s not a branch – it’s its own repo for sure. 2.2 is not a “tag” so… what to do? The only thing to do is to have the trunk split into versions which is ALL kinds of messy. I needed another repo for my project, and Google doesn’t let you do that.
2. I needed a really cool, collaborative system for our template sharing, which also should have its own repo.
3. Google SVN began to crumble and crash on me, causing me to drop/reload every week or so.

I’ve been using GitHub for a while and I absolutely LOVE it (more on that later) so with this release, we’ve moved to GitHub. I’ve kept the Google site open so we can work on existing issues, etc but I’ll be closing it in the coming month or so.

The Goods

• The new project site is here
• The new docs site is here
• The new Source site is here.

I’m going to go have a beer or five and then go blow stuff up for the 4th like a good Amrrrcan.

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).

I have a little downtime right now (which I’ll write about in a later post – and yes I still have my job) so I’m using it to put the wraps on SubSonic 3.0 (soon, soon). One feature I’m very, very happy about is the new SimpleRepository, which allows you to use POCOs the way you want, using Migrations the SubSonic way: magically.

It’s Rev 1, Be Gentle! I’ve put peddle to metal with this feature, using it as the backbone of my entire test suite – I think it’s pretty close to being “good to go”. That said – this is Open Source, if you find a bug I’d love your help with a patch. This is not Big Software and we won’t think of every scenario – but hopefully most of them.

Elevator Summary
The SimpleRepository is for people who don’t want a bunch of “fluff and object noise” in their application and who want their objects “to just be persisted somewhere” without having to design a DB to create an object model. It’s all about POCOs first – sort of an Object Database feel where your object is “just saved”.

This Repository is bare-knuckles, get it going quick, ZERO CONFIGURATION “just gimme my damn data and get out of my way and no don’t ask me which DB I’m using” kinda deal. I should have called it “BareKnuckleRepository”… hmmm.

It works with SQL 2005, 2008, MySQL, and SQLite. Oracle drives me crazy and if someone’s inclined the fix to put it in takes all of 30 minutes. Same with PostGres.

How It Works
The first thing is to create a simple object – I’ll use the ubiquitous Product here:

    public class Product {
        public int ProductID { get; set; }
        public Guid Sku { get; set; }
        public int CategoryID { get; set; }
        public string ProductName { get; set; }
        public decimal UnitPrice { get; set; }
        public bool Discontinued { get; set; }
    }

Next, you need to tell SubSonic about your database. You do this by using our ProviderFactory and passing in a connection string, or not:

var provider=ProviderFactory.GetProvider("SubSonicTest");

UPDATE: I just checked in a change so you don’t need to declare the Provider – it will do it for you. You can just pass in the connection string name, or nothing at all and we’ll try and guess what it is.

You can pass in a connection string name, the full connection string, or nothing at all and we’ll do our best to pull it from your config. This is basically a glorified wrapper to your database.

Then you create a SimpleRepository, passing in the provider you just created:

var repo=new SimpleRepository(provider,true);

The boolean argument there is the magic button – I’ll talk more about this in a second. This repository is very, very simple and has things like “Single, Find, Add, Update, Delete” etc. and can save bunches of items in a transaction.

For this example let’s use an empty DB I just created (the connection string above points to it):

Next, work up your Product object:

var newProduct=new Product();
newProduct.Sku=Guid.NewGuid();
newProduct.CategoryID=5;
newProduct.ProductName="Pretzel";
newProduct.UnitPrice=100;
newProduct.Discontinued=false;

And then save it using the Repo:

repo.Add<Product>(newProduct);

This is where the magic happens . By setting “true” in the SimpleRepository constructor, you’re telling it to run Migrations for you – automatically – creating your table if it’s not there.

If you refresh your DB…

Yes – if you change your model then your DB will be synchronized. No, you will not lose data (unless you’re using SQLite , which doesn’t support ALTER of anything). You can change names, types, lengths, add/remove columns etc – all without dropping the table.

Notice that the Primary Key was created for you, and the columns defaulted to nice lengths etc. It also made sure the data was added…

And yes, the Add() method returns the newly inserted key – for all providers.

SimpleRepo Convention

It’s not easy to make decisions as to how to store certain columns – and I had to do something I normally don’t like, and that is create a set of very, very small Attributes to help out.

• Primary Keys: If you call a column “ID” or “Key” or “[ClassName]ID” – no matter it’s type – that will be your Primary Key. If you have other things in mind you can use a primary key attribute (“SubSonicPrimaryKey”) and we’ll use that column.
• String length: there are two ways to tell SubSonic how to handle this – both using attributes. The first is “SubSonicStringLength(int length)” and the second is “SubSonicLongString” which sets to nvarchar(MAX) or LONGTEXT – depending on your provider.
• Nullability: The default is not null, but you can change this by making your propery a nullable type.
• Numeric Precision: the default is a Precision of 10 and a scale of 2 but you can change that with the “SubSonicNumericPrecision(int precision, int scale)” attribute.
• Ignoring a property: you can ignore generation of a property by using “SubSonicIgnore” attribute.

This is my test class:

    public class SubSonicTest {

        public Guid Key { get; set; }
        public int Thinger { get; set; }
        public string Name { get; set; }
        [SubSonic.SubSonicStringLength(500)]
        public string UserName { get; set; }
        public DateTime CreatedOn { get; set; }
        public decimal Price { get; set; }
        public double Discount { get; set; }

        [SubSonic.SubSonicNumericPrecision(10,3)]
        public float? Lat { get; set; }
        [SubSonic.SubSonicNumericPrecision(10, 3)]
        public float? Long { get; set; }

        public bool SomeFlag { get; set; }
        public bool? SomeNullableFlag { get; set; }
        [SubSonic.SubSonicLongString]
        public string LongText { get; set; }
        [SubSonic.SubSonicStringLength(800)]
        public string MediumText { get; set; }
        [SubSonic.SubSonicIgnore]
        public int IgnoreMe { get; set; }
    }

And if you have a look here at our source testing, you can see how this class is used in my unit tests (not an easy thing to do!).

You can also have a look here to see how the SimpleRepository works under test.

Summary Summary

I’m working on perf tweaks right now, and still adding things to the docs so it will be a bit yet until I release this. If you want to have at our source – it’s here.

I’m not going to release 3.0 until I feel that people can get up and going – and there is a TON of stuff going on with 3.0 that people should know about. I’ll have more on this in the coming weeks.

I’ve been up way too late over the last week – but it’s been really fun . I tore into the Issues list (both on Google and on our old Codeplex site) and loaded up about 13 total patches which, I think, solved a TON of problems. THANK YOU to all who sent in the patches and I’m sorry it took me so long to get to it. I have no excuses other than I’ve been stretched too thin.

You can download it here.

Change List
There are no major improvements (short of a new method to check if a DB is online) – this is  maintenance release mostly. I think there might be another after this one – hopefully not. The biggest thing with this release is the love we threw at Oracle – I loaded up 3 different Oracle patches that, I hope, solved some issues people were having.

Here’s the change list with the revisions committed:

492:
FIXED: Codeplex 17617 – Added patch for Distinct Query values – thanks to GeoffAtDataGaard
FIXED: Patch applied from Jason Kealey for composite Primary Keys (Google issue 3)
FIXED: Patch applied from boyank for Scaffolding and composite keys (Google issue 9)
FIXED: Google issue 12 (Migration column/numberscale for decimals) – thanks to robbam
FIXED: Google issue 14 (Migration max length for char/non-int PKs) – thanks to robbam
ADDED: Initial rev of ActiveRecord T4 templates
REMOVED: Substage project – will added as "extra"
REMOVED: SubSonicCentral web site. Will add as "extra"

493
Oracle Patch Applied for Joins and Columns

494
FIXED: Google Issue 18 – dual foreign key columns resulted in a constraint with same name

495
FIXED: Google issue 20 -  if item not found in SubSonicRepository an empty object is returned (instead of null)

496
FIXED: Google issue 17 – LoadByParam would not set IsLoaded to false when no record present

500
FIXED: Google issue 29 – using IN with Aggregates cause error

501
FIXED: Google Issue 28 – ExecuteJoinedDataSet parameters incorrectly set

502
FIXED: Google Issue 31 – Where Expression not formatting correctly with qualified column name

503
ADDED: IsOnline() method to SubSonicRepository (and ISubSonicRepository) with overload for connectionString to test whether a DB is connected and online. Returns bool.

504
FIXED: Google Issue 36 – Paging doesn’t work with SqlQuery and requires Select(). Patch applied from Jim Zimmerman (thanks dude!)

505
FIXED: Google Issue 38: Removed Oracle functions used with SPs

507
FIXED: Google Issue 40: Adding // <auto-generated /> tag to C-sharp templates

508
ADDED: Google Issue 41 – output version when running a migration (applied patch from developingchris)

509
FIXED: Google Issue 46 – TableSchema.Table equality improperly set.

510
FIXED: Google Issue 50 – boolean translation error with MySQL. Updates default to DbType.AnsiString when only a columnName is passed. This was default behavior for IsDeleted. Changed to lookup column in FromTables which solved this and I’m sure other issues.

I’m finally getting around to organizing SubSonic a bit and, aside from flaming me for the obvious (docs suck, project’s dead, etc) – I’d really like your help on a few things.

Forums
Our forums are doing decently well but managing the server/software/upgrade sucks. I’ve been at it all day today and it’s not terribly fun. If you boil down what users are trying to do (get their questions answered, find answers) – it’s a bit overkill to host this stuff yourself. Especially when someone else is doing it SO MUCH BETTER.

So I have an idea. I sent Jeff an email about this and hopefully he’ll consider it. Here’s the idea:

Subdomains for Stackoverflow for Open Source project support. Subsonic.Stackoverflow.com. Win/Win.

Let’s face it – people will probably head there for answers quicker than our forums. As it stands I really don’t need a subdomain – I can just navigate to the tagged questions if I like. But having a subdomain could allow for some branding – maybe a logo or advertising support (which I could split with Jeff). Possibly even domain-specific “Hot” and “Votes” etc.

So in response to Jeff’s latest post (which I liked a lot), I’d say this kind of thing would really, really, really help. And I promise not to make fun of him anymore .

Docs
I really like the way Wordpress does there stuff. Clean, easy to find – boom, answers. The thing is that, well, they get paid to do that stuff full time, and there’s a lot of em. I’d like to do something like this and I think I can do it easily with the platform I have now (Graffiti) – the thing I’m after is organization and help. Your thoughts on this would really help me!

Thanks for your thoughts on this…

Many thanks to the folks who downloaded and ran the alpha. As I figured, I missed a few things. The good news is that I’ve fixed them, and 99% of them were with the templates – which is good news because it means that you could have fixed them if you had to .

But first – I’d like to say a big HELLO! to all the folks at CodeBetter, because as of today, I will be cross-posting my blog there. Indeed it’s quite an honor and many thanks to Jeremy Miller for inviting me!

Here’s the updated list of bits:

1. Fixed an SP bug that would result in the command type not getting set properly
2. Restructured and re-organized the namespacing
3. Renamed the DLL to SubSonic.Core, rather than SubSonic (forgot to do that earlier)
4. Fixed up the _Settings.tt template to more accurately convey just WTF it is you’re setting
5. Fixed a foreign-key naming bug which allowed spaces through
6. Fixed a bug with self-referencing tables, wherein a child property could wiggle through and end up having the same name as the parent
7. Fixed a commenting issue in the Repositories, which VS didn’t like

You can download the source, if you like, or grab the download here (DLL plus templates). If you have a question – you can leave a note here or (preferably) ask it here, on our mailing list.

Setup Walkthrough
I was asked on the comments of my last post for a walkthrough – so here it is. Setting up SubSonic 3:

1) Download the bits.

2) Open Zip file, and put on your hard drive somewhere

3) Create a project and add an app/web.config file

4) Add a VALID connection string to your favorite DB

5) Edit the _Settings.tt file, letting it know the name of your connection string and whatever Namespace you want for your generated objects:

6) Drag the _Generated file into your project – put it where you like

7) You’re done. Go grab a soda, then start coding.

If you want more information about how to use what’s generated for you, read more here and also read up here.

Many thanks to the folks testing!

SubSonic 3 is ready for Alpha. It’s a pretty significant upgrade and I think I’ve tackled all the bugs and issues. Lofty hopes, but with your help maybe we can make this thing solid.

Changes
Here are the changes for this release:

• Fixed a bug with Connection.GetSchema() that only retrieved columns when it felt like it. Now I’m using INFORMATION_SCHEMA
• Altered the Class template to implement INotifyPropertyChanging, INotifyPropertyChanged. Added the eventing to support this as well (for anyone out there who wants to help make an UnitofWork container… I already have the basic bits in place…)
• Added a ProviderFactory to make the instancing of IDataProvider work with an Abstract Factory as opposed to leaving it completely up to System.Data.Common. This will allow for using providers that don’t implement System.Data.Common.
• Added a Factory for creation of the QuerySurface (The DB() context, for lack of better words). This allowed me to …
• Added ForiegnKey properties to the Class template, so you can now access Product.Suppliers a la Linq To Sql.
• Added Validation methods for each property on the Class template, so when you set a property you can override a "OnEmailChanged" and "OnEmailChanging(string value)" – just like in Linq To Sql.
• Added Rodrigo Diniz’s change to the _Settings template that will use your config file’s connection string so you don’t have to set manually.
• Added the Inflector to the Utility class, allowing you access to methods such as Pluralize(), PluralToSingular(), ToProperCase, ToPascalCase, etc. This is a class for naming only and is part of the templates.

These aren’t the only changes – Matt Warren released part 12 of his Linq tutorial which addressed 99% of the issues people were finding, including the use of variables in the Linq call. Hi IQToolkit is awesome, and is the backbone of SubSonic 3.0.

It’s a complete restructuring, which will be a tremendous help as we try to fit in support for Oracle and other providers (which I’ll need some help with).

I can’t thank Matt enough – his posts have been a tremendous help.

You can download it here.

Setup
You go through the same process as before. To recap, you:

1. Add a web or app.config file to your project
2. Add a connection string to your DB
3. Add a reference to SubSonic, which is in the folder
4. Drop in the "Generated" folder, wherever you want it to go.

Hopefully that will be it. Everything is working pretty well for Sql Server and MySQL, but I know there’s more to do. I definitely know there’s some love that needs to happen WRT to Oracle, and for that I’ll need some help.

The Source
I’ve made the source available here, in a new repository that’s separate from SubSonic 2.1. I’ve also changed the license (on 2.1 as well) to be New BSD. Please don’t read anything into this – it’s just what I wanted to do.

SubSonic 3.0 Code Repo

I also want to keep the discussions out of our forums – it’s too hard to manage, especially for alpha. Instead I’ve created a Google Group list here:

http://groups.google.com/group/subsonicproject

This is for SubSonic 3.0 Alpha ONLY. If you find bugs, I’d really appreciate you logging them here:

http://code.google.com/p/subsonicthree/issues/list

If you would like to create a patch, I’ll hug you.

Using SubSonic 3.0
Just about everything is exactly the same as Preview 2, sans some silly bugs. The great thing is that if you know Linq, you know how to use SubSonic. The one major difference is that now I am using a Factory to create the DB:

And that’s about it.

If you want to take advantage of the ForeignKey stuff, you can do so like this:

Call for Help
If you would like to help ramp out support for your favorite database, please send me an email (using the groups list above) and your google login, and also what you’d like to do in particular.

More than anything, if you have a problem and can write a test that I can use (against the Chinook DB would be great) to verify, that would rock big time.

I’ll put together some more tutorial stuff later this week.

Cheers.

Got a fun email from Will Gant today:

Since everything else is going my way (sorta like Burger king), now comes the “can I have fries with that?” question. Would it be possible for the Add, Delete, and Update methods on the repository class to have overloads that take IEnumerable<T>?

And I sort of started to kick myself a bit – this should have seemed obvious, and I should have done it from the start, but I didn’t and I’m not too sure why. This is the goodness that is Open Source!

Will sent me a quick patch, and I added some spice to it and now we have a batch-updatable (read: one connection, one sql execution) Add/Update/Delete for the Repository which you can download here (I’ll include this in subsequent releases).

Hopefully this underscores a bit more just how much control you have in this!

To show you how this might work, here’s a quick spike using the Chinook database (our new test db):

        [TestMethod]
        public void Update_Should_Update_En_Masse() {

            ChinookRepository<Customer> repo = new ChinookRepository<Customer>();

            var customers = repo.GetAll().ToList();
            int index=0;
            foreach (var c in customers) {
                c.Company = "Company" + index.ToString();
                index++;
            }

            repo.Update(customers);
        }

Just to re-iterate, the Update() method here takes in the List<Customer> that is created from the repo.GetAll().ToList() call. This will not work if you leave it as IQueryable. I’m not sure why – but the bottom line is that I tried and the changes weren’t honored as I think IQueryable doesn’t allow you to assign values to it – it’s just an Expression manager.

The Update method will create one single SQL query and execute it as a batch – one call! I was going to wrap the ExecuteBatch() method in a transaction but decided against it for now – however if you want to do that, you can edit the templates as you need .