There are two more features I want to get into Simple.Data before releasing 1.0beta1: upserts, and eager loading. Upserts are pretty straight-forward, but there are a few different approaches to eager loading and I want to get some input from the community before I pick one.

In case you don’t know, eager loading is a pattern for getting data from the database in an optimal fashion, with the minimum number of requests or least-possible server load. It’s the opposite of lazy loading, which Simple.Data already does.

The implementation syntax will look like this:

    IEnumerable<Customer> customers = _db.Customers.FindAllByRegion("South")
                .With(_db.Customers.Orders)                .Cast<Customer>();

This will create the customer objects and populate an ICollection<Order> Orders property in each one, assuming there is one.

There are two directions in which a class may have relationships with other classes: it may have “parent” (or “lookup”) classes, where there is at most a single instance of that parent class within the instance of the class in question; and it may have “child” classes, where there is a collection of instances of the child class. Eager loading of parent classes is trivial; you just add the necessary join(s) to the master table(s) and marshal the data into the right places in code.

Eager loading of child classes is similarly easy when there is only a single child collection to be loaded; you join to the detail table and then process the results so that a single instance of the main table row is created, and instances of the detail table rows are grouped into collections.

For example, loading user objects with a list of phone numbers:

SELECT User.Id, User.Name, Phone.Number
FROM User
LEFT JOIN Phone ON User.Id = Phone.UserId

Returned rows:

From this we create a User object for Alice with her two phone numbers, and an object for Bob with his. Even if there are a lot of rows in the detail table, this is still the preferred way of handling eager loading for this scenario as it involves the fewest round-trips to the database. Often, you can handle a parent-child-child relationship using this algorithm, too, although beyond that point you’re getting a bit scary.

The complexity arises when you want to load two unrelated child collections, say, load Customers with their Invoices and Orders:

SELECT Customer.*, Invoice.*, Order.*
FROM Customer
LEFT JOIN Invoice ON Customer.Id = Invoice.CustomerId
LEFT JOIN Order ON Customer.Id = Order.CustomerId

This creates what is technically called an “outer Cartesian product”, where a row is returned for every possible combination of the rows from the two outer-joined tables. So if you’re grabbing the details for a single valuable customer who has placed a thousand orders and generated a thousand invoices, you get a million rows back from the database. I have a hunch this might not be optimal.

So this is my Request for Comments: what would you like to see done here?

The options are:

1. Only allow one detail-table With clause to be specified per query. Throw an exception if there is more than one.
2. Allow multiple detail-table With clauses, and use additional SELECT operations for all except the first.
3. Add a parameter to the With method allowing the developer to specify the technique on a per-detail-table basis:

    var customers = _db.Customers.FindAllByRegion("South")
                .With(_db.Customers.Orders, WithUsing.Join)
                .With(_db.Customers.Orders.Items, WithUsing.Join)
                .With(_db.Customers.Invoices, WithUsing.ExtraQuery);

If only one detail table is specified, or one detail with a detail below it (e.g. _db.Customers.Orders.Items), the default behaviour when no WithUsing parameter is supplied will be to use JOIN. Multiple detail tables without a WithUsing will cause the ADO adapter to throw an exception (other adapters, such as the one for MongoDB, can use the With clause as they see fit).

I think I’ve rubber ducked here a bit, as Option 3 is looking like a no-brainer, but I’ve typed it all now, so if you do have any comments or suggestions, please do leave them below. I’m planning on starting code for this on Sunday morning, so there’s plenty of time to change my mind.

Several people who weren’t following the show at the inception of Simple.Data have asked whether it does anything to prevent SQL Injection attacks. Those of you who remember the start of the project will understand why I’m kind of amused by the question.

I originally built Simple.Data as a proof-of-concept when there was a big web-scuffle over what was then called Microsoft.Data (now WebMatrix.Data). David Fowler, a developer I have since come to respect greatly, posted an example on his blog which used concatenated SQL, and everybody lined up to tear him a new one. I’m a big fan of code over cockfights, so I had a go at implementing a simple data access library which made SQL Injection impossible by eliminating SQL strings. Thus Simple.Data. You told it what you wanted, and provided the necessary values, and then it wrapped them safely up in parameters and executed them. I stuck it on GitHub, and posted about it on my blog.

(In fact, the first version did allow you to execute SQL strings, much like Dapper and Massive, because the functionality was extremely limited otherwise. As I’ve iterated on it, I’ve been able to expose nearly all the required functionality using the dynamic approach, so the only way you can execute SQL directly now is by getting an IDbConnection object from the AdoAdapter and using ADO.NET against it.)

Deserves its own post, even though I’m about to write the 0.11 one.

This release took much longer than I would have liked, mainly because I bit off more than I could chew. I had a requirement to provide poly-fills that implement query operations against in-memory datasets, on behalf of adapters for data-stores that don’t support certain operations. Think aggregates, grouping and so on. At some point, it occurred to me that this would make it really easy to create an in-memory adapter which would just have all query functionality implemented by these poly-fills.

Aside: From very early on, Simple.Data has had the XmlMockAdapter which could be used for providing a quick and dirty database for test environments. At the time I wrote that, I wasn’t totally confident in the Simple.Data model, so a way to instantiate that data without using the API seemed important. Since then, of course, the API has become much more stable and I’ve gained confidence, so it seems perfectly OK to use it to set up the test data as well as consume it. Consider XmlMockAdapter deprecated.

Simple.Data.InMemoryAdapter

The InMemoryAdapter lives directly in the Simple.Data namespace in the Simple.Data.dll assembly from the Simple.Data.Core package. It is a first-class component, because testing is a first-class concern.

You can use the adapter like this:

Database.UseMockAdapter(new InMemoryAdapter());
var db = Database.Open();
db.Test.Insert(Id: 1, Name: "Alice");
var record = db.Test.FindById(1);
Assert.IsNotNull(record);
Assert.AreEqual(1, record.Id);
Assert.AreEqual("Alice", record.Name);

That UseMockAdapter method is now directly on the Database class. After calling that, all subsequent calls to any of the Database.Open methods will return a database using that adapter.

Any tables you reference in the database will be “created” if they don’t already exist. Internally, the tables are just lists of dictionaries. Any data you insert will retain its CLR type, so the adapter can handle byte arrays and so on quite happily.

Joins are supported:

var adapter = new InMemoryAdapter();
adapter.ConfigureJoin("Customer", "ID", "Orders", "Order", "CustomerID", "Customer");
Database.UseMockAdapter(adapter);
var db = Database.Open();
db.Customer.Insert(ID: 1, Name: "NASA");
db.Customer.Insert(ID: 2, Name: "ACME");
db.Order.Insert(ID: 1, CustomerID: 1, Date: new DateTime(1997, 1, 12));
db.Order.Insert(ID: 2, CustomerID: 2, Date: new DateTime(2001, 1, 1));

var customers = db.Customer    .FindAll(db.Customer.Orders.Date < new DateTime(1999, 12, 31)).ToList();
Assert.IsNotNull(customers);
Assert.AreEqual(1, customers.Count);

And most query operations work OK.

YMMV: This is probably not perfect, but it was stalling other development so I took the decision to release it. So far in my own projects it has not caused any problems; I’ve had one reported which is fixed in 0.11 (more on that in the next post).

Bug fixes and such

There were a bunch of fixes for various issues in 0.10, most of which were contributed by Richard Hopton, who has also released an ADO provider for Sybase SQL Anywhere.

I know it’s early for New Year resolutions, but I’m going to share this one now partly so I don’t forget in six weeks.

I buy a lot of media. I have an entire 6′x3′ bookcase full of technical books, most of which are out of date by at least one version. There’s a cubic hectare of Blu-ray and DVD boxes lying around the place, and I’m pretty sure there are boxes of CDs (remember those?) in the loft somewhere. Probably half the available storage space in my house is taken up with things which would actually fit on a few terabytes of hard disk.

It’s not like everything in the house isn’t already digital. We’ve got a 160GB iPod Classic, iPhones, iPads, Kindles, three laptops, an Xbox 360, a PS3 (with a 320GB HD), a 50Mbps internet connection, and on Saturday we bought an Apple TV.

It’s ridiculous. It has to stop. It ends, starting in January. From then on, where a digital purchase option is available, I shall avail myself of it. I will invest in a home server with a few TB of storage on mirrored drives; I’m thinking of the Lacie 5big Network 2, which will take up to 10TB. Then I can “back up” my movie collection to that, and install iTunes on it for music streaming. Paper books will go to charities and libraries; if I want to read any of them again, I’ll buy them in ebook format. Magazines through Newsstand and papers through the Times iPad app.

Now, I just need Sony and Microsoft to distribute all PS3 and Xbox games digitally, and I need never darken the door of another shop. Not that I do anyway. All this crap comes from Amazon.

I just sent this to our recruiter (our only recruiter, so please don’t bother if you’re not him), and it occurred to me that it might make an interesting mini-post, so here it is, copied and pasted from my email.

The main thing we look for is strong core programming skills, ideally in C# but will also consider Java and C++ programmers. The interview will include technical questions about Object Oriented programming and whichever skills the candidate claims to have.

Web development is the second most important thing for us these days. Again, there will be questions about web applications, ASP.NET fundamentals and so on.

The third thing we expect is database skills, and I’ll ask questions about database design and SQL. I don’t want people who only know ORM abstraction layers.

Obviously the level to which the questions go varies according to the level we’re interviewing for, but the basics of all three would be covered for all levels.

My big bugbear is when I ask a question which the candidate’s CV implies they should know the answer to, but it turns out they don’t.

Any code questions are asked and answered on the whiteboard, so individuals who rely totally on IntelliSense are in trouble.

If you’re looking for a developer role with a great company in Windsor, UK, and the above doesn’t put you off, please get in touch!

This is a Public Service Announcement

TL;DR

Simple.Data returns dynamic objects. You don’t have to use statically-typed POCOs unless you want to.

And in full for those without Attention Deficit disorders

I just caught this post on Google+, about using Simple.Data with MongoDB. (I’m really pleased that there’s a MongoDB adapter, because (a) MongoDB is awesome, and (b) it’s the poster child for the fact that Simple.Data is not exclusively an RDBMS-targeting solution. The adapter works really well, although some query operations don’t make sense in MongoDB; I’m working on some code for Core which provides polyfills for any operations which adapters might not be able to interpret meaningfully, such as grouping and aggregates and so forth.)

Anyway, back to the main point of this short post. The comments from Jak Charlton about static typing made me realise that there is potentially a fundamental misunderstanding about what Simple.Data actually does, and a belief that it requires static POCO types to work. It doesn’t, but I haven’t made this clear enough, so sorry about that, and this is me rectifying that situation.

So this misunderstanding probably arises from the fact that most of the demo apps and blog posts that have been written do use POCOs, doing things like this:

User user = db.Users.FindByEmail(email);
VerifyPassword(enteredPassword, user.Password, user.PasswordSalt);

That code works, but in terms of clarity it hides a step in the process. If I split it into two steps, it becomes clearer:

var record = db.Users.FindByEmail(email);
User user = record;
VerifyPassword(enteredPassword, user.Password, user.PasswordSalt);

The first line creates an object of type SimpleRecord, and the type inferred for var is dynamic. (SimpleRecord is very similar to the BCL ExpandoObject type, but with some data-access-specific intelligence.)

The second line performs a kind of “implicit cast” of the dynamic SimpleRecord object to the static User type. (SimpleRecord handles this by overriding the DynamicObject.TryConvert method, if you’re interested.)

The Very Important Thing that I want you to take away from reading this post is that the second line is not required. This code will work every bit as well:

var user = db.Users.FindByEmail(email);
VerifyPassword(enteredPassword, user.Password, user.PasswordSalt);

Here, user will be compiled as dynamic, created as a SimpleRecord instance, and will return values for the Password and PasswordSalt properties when asked. You just won’t get IntelliSense™ for the object.

Other things you can do with these dynamic objects is pass them to your View for rendering, or bind to them in WPF applications. If you don’t want the “statically typed noose dragging you down” (and personally I really don’t), then Simple.Data is your new best friend.

Tonight’s movie

will be Pitch Black. A group of marooned space travellers struggle for survival on a seemingly lifeless sun-scorched world.

That is all.

I really wasn’t happy with the way you need to break up the method chain to do certain joins, and while writing the release post last night an alternative occurred to me, which I have just spiked and liked:

dynamic manager; // To hold the aliased reference
var q = _db.Employees.Query()
    .Join(_db.Employees.As("Manager"), out manager).On(manager.Id == _db.Employees.ManagerId)
    .Select(_db.Employees.Name, manager.Name.As("Manager"));

This requires a variable to be declared ahead of the query, which gets passed as an out parameter to the Join method and receives the aliased ObjectReference. That variable can then be used for that reference throughout the rest of the query. It’s still a compromise, but I think it’s a better one.

It still works with named parameter syntax, but because runtime “magic” methods don’t support out parameters in .NET 4, I’ve had to shunt the named parameters into an overload of the On method:

dynamic manager; // To hold the aliased reference
var q = _db.Employees.Query()
    .Join(_db.Employees.As("Manager"), out manager).On(manager.Id == _db.Employees.ManagerId)
    .Select(_db.Employees.Name, manager.Name.As("Manager"));

I don’t know about you, but I occasionally release a piece of software into production and then, next time I look at the code, think of a better way of achieving something. The nice thing about having good test coverage is that you can make those changes and be confident that what you’ve done hasn’t broken anything.

Anyway, this syntax will be in 0.6.9, along with the Having clause support.

I have been told that I tilt at windmills, and been compared to King Lear raging at the storm. It seems I hold forth about things over which I have no control. So be it.

The deployment model that Microsoft are using for .NET is, in my opinion, outdated and untenable. Each release of the framework is dropped on the world as a monolithic block of CLR, language updates, a new Visual Studio version and a “Base” class library which tries to be all things to all developers. In this post, I would like to call into question the efficacy, if not the sanity, of this approach, particularly regarding the BCL.

The “Base” class library?

The first couple of versions of .NET included two UI paradigms within the BCL: ASP.NET and Windows Forms. If you built an ASP.NET application, you still had Windows Forms in the framework; if you built a Windows Forms application, you still had ASP.NET.

With .NET 3.0, Microsoft added WPF into the mix; the full version of that framework now included 3 UI paradigms. It also added WCF and Workflow, and then in the oddly-numbered 3.5 came the born-moribund LINQ-to-SQL ORM. Whether you wanted them or not, you got them all. This made the framework distribution large and unwieldy, and so the Client Profile was conceived, a “lightweight” version of the BCL which excluded all the server-related classes (and quite a few classes which were very useful for client development, such as HttpUtility).

During the lifetime of .NET 3.5, Microsoft discovered the “out-of-band” release model. They created another ORM, Entity Framework, and distributed it out-of-band. Scott Guthrie got bored on a plane and made ASP.NET MVC, and it was distributed out-of-band. The out-of-band model was good, although Microsoft’s apparent desire to create the kind of framework-proliferation that Java suffered from, but comprised entirely of in-house projects, was bizarre and inexplicable.

Then it was time for .NET 4, and into that was rolled Entity Framework 4.0, and ASP.NET MVC 2, an update to WPF, and a new version of Workflow for good measure. Again, there was a Client Profile. (Again, it was missing useful classes.)

Shortly after .NET 4 was released, ASP.NET MVC 3 became the current version. A little while after that, Entity Framework 4.1 appeared, and it appeared in a way that was new (to .NET) and exciting: NuGet.

Package Management

The idea of package management has been around in various guises for a long time. The Linux distribution Debian introduced the APT package manager in 1999 to distribute everything from system libraries to office application suites. In the software development world, at RubyConf 2003, Ruby programmers were first introduced to the RubyGems system: a centrally-managed repository of libraries large and small which could be installed to your system with a three-word command (plus –no-rdoc –no-ri, of course).

It took a few years, but then suddenly several package management projects were announced for .NET within a relatively short space of time, some based on (and piggy-backing) RubyGems, and others taking a ground-up, self-hosted approach. All of this would probably have remained the bailiwick of open-source enthusiasts and the ALT.NET community, but then Microsoft suddenly snapped up one of the projects, brought it in-house, hosted the repository, (changed the name to NuGet) and generally made it official, and started using it to distribute their own .NET libraries.

Brave NuGet World

So we now have a situation where the current versions of ASP.NET MVC (3) and Entity Framework (4.1) are not a core part of the framework, but are distributed and maintained through NuGet. It seems likely that the new-and-improved WCF Web API will also appear through NuGet before we get another full framework. Of course, to use these packages, you still have to install .NET 4.0, with the now-deprecated versions of all these things in it. That’s annoying.

I’d like to see the next version of .NET, be it 4.5 or 5.0, ship with a BCL that has been stripped down to the absolute basics: fundamental types, I/O, LINQ, the Task Parallel Library and ADO.NET. I don’t want to be able to write anything but Console applications with it. I don’t want ASP.NET, MVC 4, WPF, Windows Forms, WCF, Workflow, LINQ-to-SQL, Entity Framework 5.0 or any of that stuff. Instead, I want the “Add Reference” dialog in Visual Studio 11 to show Packages where currently there are GAC-registered assemblies.

Ideally, within this new Packages reference tab, I would like to see equal weight given to third-party and open-source libraries, so that things like OpenRasta, FubuMVC, Nancy, NHibernate, Dapper and Simple.Data are presented on a par with ASP.NET and Entity Framework.

In conjunction with that, I’d like it if NuGet, the enabler of this new paradigm, were distributed as a core part of the framework, much like RubyGems is now a part of Ruby, so that when I deployed my application to a web server, desktop or shiny new “device”, it could automatically retrieve – and potentially NGen and GAC – those dependencies if they were not already present, thus reducing the size of my deployment.

By adopting this model, Microsoft could not only embrace and support the many excellent third-party solutions that exist; they could also optimise the lifecycle of their DevDiv output. No longer would a release of a new .NET version herald a massive set of breaking changes to existing libraries; no longer would releases have to wait until the combined efforts of countless development teams could be wrangled into a single MSI. A new framework version would mean the next generation of languages with their supporting classes, and potentially a new CLR version. Surely that would be easier to manage than having to wait until the WPF team have got text not to look like it’s been smeared in petroleum jelly?