At last

It’s taken me so much longer to get here than I originally expected, but I’ve released the first 1.0 beta version of Simple.Data.

If this post is the first you’ve heard of Simple.Data, then head over to the GitHub page and browse back through previous posts to find out more.

New features

I didn’t do a post for the 0.14 release, so I’ll cover the changes from that as well as what’s new in the 1.0 beta.

Eager-loading with With

Since very early on, Simple.Data has supported lazy-loading when you reference a joined property from the dynamic record type. There were two issues with that: firstly, if you assigned the record to a static type, the joined properties were not hydrated; secondly, multiple selects do not make DBAs happy, and should be avoided if possible.

Now you can use the With method to load the joined data at the same time as the main record.

var db = DatabaseHelper.Open();  
Customer actual = db.Customers.FindAllByCustomerId(1).WithOrders().FirstOrDefault();  

This uses a single SQL select statement to pull all the Customer rows, plus the Order detail rows, and groups the data in-memory; generally, that’s more efficient than running two select operations. When the record is converted to the Customer type, it sets the ICollection<Order> property at the same time, either creating a new instance of List<Order>, or populating an existing instance if the property is readonly.

The inverse works too:

var db = DatabaseHelper.Open();  
Order actual = db.Orders.FindAllByOrderId(1).WithCustomer().FirstOrDefault();  

If the property name is not the same as the database table name, you can use an alias to tweak it:

var db = DatabaseHelper.Open();  
var actual = db.Orders.FindAllByOrderId(1).With(db.Orders.OrderItems.As("Items"));  

And if there’s no referential integrity in the database, you can specify an explicit join separately:

dynamic manager;  
var q = _db.Employees.All()  
    .OuterJoin(_db.Employees.As("Manager"), out manager)
    .On(Id: _db.Employees.ManagerId)
    .With(manager);

 

(Oh, yeah, and check out the OuterJoin method. Finally.)

Of course, if there’s no referential integrity, it’s hard for Simple.Data to work out whether the joined property is a collection or a complex object, so you can specify WithOne or WithMany to help it out:

dynamic manager;  
var q = _db.Employees.All()  
    .OuterJoin(_db.Employees.As("Manager"), out manager)
    .On(Id: _db.Employees.ManagerId)
    .WithOne(manager);

 

And of course, you can mix and match all these. This test gives you a good idea of some of the heavy lifting that’s going on for you with this feature, just with the SQL:

public void MultipleWithClauseJustDoesEverythingYouWouldHope()  
{
    const string expectedSql = 
        "select [dbo].[employee].[id],[dbo].[employee].[name]," +
        "[dbo].[employee].[managerid],[dbo].[employee].[departmentid]," +
        "[manager].[id] as [__withn__manager__id]," +
        "[manager].[name] as [__withn__manager__name]," +
        "[manager].[managerid] as [__withn__manager__managerid]," +
        "[manager].[departmentid] as [__withn__manager__departmentid]," +
        "[dbo].[department].[id] as [__with1__department__id]," +
        "[dbo].[department].[name] as [__with1__department__name]" +
        " from [dbo].[employee] left join [dbo].[employee] [manager] " +
        "on ([manager].[id] = [dbo].[employee].[managerid])" +
        " left join [dbo].[department] " +
        "on ([dbo].[department].[id] = [dbo].[employee].[departmentid])";

    dynamic manager;
    var q = _db.Employees.All()
        .OuterJoin(_db.Employees.As("Manager"), out manager)
        .On(Id: _db.Employees.ManagerId)
        .With(manager)
        .WithDepartment();

    GeneratedSqlIs(expectedSql);
}

(Never mind the logic involved in turning that result set into the correct in-memory object graphs.)

Now, this works on multi-record queries, but not on single-record ones such as FindById or the key-driven Get method, and that’s more problematic since those methods don’t return a query you can modify, just a record. In the past I did actually toy with having the SimpleRecord type do lazy self-evaluation, but the fact that the NUnit Assert.IsNull test wouldn’t accept that the object was null even when it swore black-was-blue that it was put me off. (It works for Nullable<T>; no fair.)

Instead of that, and this is only in the 1.0 beta release, you can specify your With clause before the Get or FindBy:

var db = DatabaseHelper.Open();  
Customer actual = db.Customers.WithOrders().Get(1);  

So now you get all that goodness for single records, where it’s arguably more useful anyway.

Upsert

Inserting and updating is all very well, but sometimes you’ve got some data and you just don’t know whether it’s in the database already or not. If it is, you want to update the row with some new values; if it’s not, you want to insert it. Boring.

To save you the time and trouble, Simple.Data now provides the Upsert method. Give it a record, and it will do all the checking to see if it exists or not. And in beta2, there’ll be back-end database-specific optimizations; for example, if you’re using the SQL Server provider with SQL 2008 or later, it will use the MERGE operation.

Upsert returns the record as it is in the database following the operation, with any database-specified values intact.

var db = DatabaseHelper.Open();  
var user = new User {Id = 2, Name = "Charlie", Password = "foobar", Age = 42};  
var actual = db.Users.Upsert(user);  

 

That’s one example, but there are plenty of other ways to use Upsert. Take a look at the tests to see the others.

NuGet and SemVer

I’ve pushed this release to NuGet using the Semantic Version number for pre-release (1.0.0-beta1), which NuGet added support for in 1.6. Using this form means that NuGet knows that it’s pre-release software, and you’ll have to explicitly tell it that that’s what you want. So to get the 1.0 beta releases, remember to use the –Pre flag for the Install-Package command. The great thing about this is that when we get to 1.0 RTW, I’ll start on 1.1.0-alpha1 and both package types will be available from the repository.

If you’re waiting for the Mono build, I hope to have it out as a tgz by the end of this weekend.

What’s still to do?

So with those two features, I’m drawing a temporary line in the sand and focusing on getting everything to release quality. That means implementing some optimizations around object creation and database tricks, but more importantly, working on test coverage, refactoring some messy code, and making the documentation comprehensive. Help on that last one would be much appreciated!

Come to that, if anybody wants to make a really nice website… :)