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…

Some API changes and enhancements

After the slow-down in development caused by all that InMemoryAdapter stuff, there were a few important things I needed to address quickly. One of these will have broken third-party adapters (but not providers) so let’s talk about that one first.

Get

var db = DatabaseHelper.Open();
var user = db.Users.Get(1);
Assert.AreEqual(1, user.Id);

I’m not really sure why Get wasn’t already there, to be honest. Part of the problem is that it requires a new abstract method internally, and until adapter authors implement that method, their users are stuck on <0.11, which I try to avoid where possible.

For the ADO adapter, Get will use the table’s primary key to construct the query (once; it’s then cached internally, no worries about performance). For the MongoDB adapter, I’d expect it to use the built-in id value that Mongo assigns to all records. Somebody is working on an OData adapter, for which, e.g., Customers.Get(1001) will resolve to the /Customers(1001) URL.

Get is supported in the InMemoryAdapter, but you’ll have to configure the key(s) for each table:

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

Trace configurability

The ADO adapter has been writing all generated SQL to the Trace output at the point of execution for a while now. While this is often very useful, I’ve had a couple of people ask if I could make it turn-off-and-on-able, so I have. You can do this in two ways:

In code:

Database.TraceLevel = TraceLevel.Off;

In config:

<?xml version="1.0" encoding="utf-8" ?> <configuration> <configSections> <sectionGroup name="simpleData"> <section name="simpleDataConfiguration"                type="Simple.Data.SimpleDataConfigurationSection, Simple.Data"/> </sectionGroup> </configSections> <simpleData> <simpleDataConfiguration traceLevel="Error"/> </simpleData> </configuration>

(Gotta love XML.)

ADO SQL output will happen with the trace level set to Info, Warning or Error.

More ADO connection control

I occasionally see how Simple.Data performs compared to other ORM/micro-ORM tools, using the PerformanceTests project from Dapper. I was running this through the other day, and I realised that Simple.Data was losing a lot of time opening and closing connections, while the other test cases were mostly using an open connection for the duration of the test. I’ve had a few comments that they’d like more control over the connection, or that Simple.Data is too aggressive in closing connections, so I decided to improve my standing in the Dapper smack-down and hopefully help some real people out too.

Start using an open connection like this:

SqlConnection connection = Program.GetOpenConnection();
((AdoAdapter) db.GetAdapter()).UseSharedConnection(connection);

And stop using it again like this:

((AdoAdapter) simpleDb.GetAdapter()).StopUsingSharedConnection();

And that’s it. In my performance test project (which is in the solution on Github), this knocked 20-30% off the runtime, with 500 FindById operations taking ~80ms, versus ~50ms using plain ADO.

I’ve also tried to tone down the aggression a little when it comes to closing connections, doing it as soon as possible, instead of (occasionally) before.

Immediate road-map

I want to get the Azure Table Service adapter done, and help with the OData adapter, and I’ve got a cool website to build using Simple.Data and Nancy, so Core will go into maintenance while I do those things for a while. I’ll try to fix any problems in a timely fashion, as usual. If you’ve got anything still outstanding as of 0.11.1 (now on Nuget) please gently remind me on Twitter.

I just pushed Simple.Data 0.6.8 to NuGet. Closing in on 1.0 now, just a couple more things to go. So what’s new in this release?

Explicit join syntax

Queries have supported implicit, “natural” joins for a while, where the relationship between two tables could be discovered from the database system catalogs. This was used by criteria expressions, column lists and so on, which would automatically add the join clause(s) to the select statement. But implicit joins have their limits. Your database may not have referential integrity implemented fully, if at all. And even if it does, there is no way to infer from it the nature of self-joins. Here are some examples:

Straight-forward joins without referential integrity

There are two syntaxes available. The first uses named parameters to specify columns from the table being joined:

var q = _db.Employees.Query()
.Join(_db.Department, Id: _db.Employees.DepartmentId)
.Select(_db.Employees.Name, _db.Department.Name.As("Department"));

The second uses an interim operator, On, which takes a criteria expression:

var q = _db.Employees.Query()
.Join(_db.Department).On(_db.Department.Id == _db.Employees.DepartmentId)
.Select(_db.Employees.Name, _db.Department.Name.As("Department"));

The second form is more verbose but allows greater flexibility. In either case, the forms that are supported when using the analogous Find method (e.g. ranges and arrays, and literal values) are also supported for join criteria.

Self-joins

The above two syntax forms are also supported for self-joins, but there is an additional level of complexity caused by table aliasing.

First, the named parameter syntax:

var q = _db.Employees.Query()
.Join(_db.Employees.As("Manager"), Id: _db.Employees.ManagerId);

q = q.Select(_db.Employees.Name, q.Manager.Name.As("Manager"));

In this case, we’ve had to split the query into two statements, because the aliased form of the table is added to the underlying SimpleQuery object; it has to go somewhere. For the Select clause to work, q must have been assigned a value so that we can use it as a parameter. I’m not wild about this, and if anybody can think of a better approach, I’d love to hear it.

That need for q to have a value arises sooner in the second form:

var q = _db.Employees.Query();
q = q.Join(_db.Employees.As("Manager")).On(q.Manager.Id == _db.Employees.ManagerId);
q = q.Select(_db.Employees.Name, q.Manager.Name.As("Manager"));

Here, we’re passing the alias contained in q into the On method, so we need to interrupt the method chain before that call. And because each method call in the chain returns a new SimpleQuery object, we need to reassign q so that it will understand the alias in the Select call.

I’ve just had a thought about out parameters that might make this a bit more elegant. Hmm.

ToScalarList and ToScalarArray

When materializing a query, you can now use these new methods to pull out the first column value in each row. There are also generic versions, ToScalarList<T> and ToScalarArray<T>, which will cast the result to the required type. (These methods were requested by @kristofclaes, who is writing a Photo Blog app using Simple.Data and Nancy.)

And finally…

I made a silly mistake while optimizing the FindBy code which resulted in the query being run twice. Not very optimal. Thanks go to @korneliuk for spotting this and sending me a pull request with the fix.

Still to do

I’ve got to add support for the Having clause, for completeness, and then I think querying will be done. But I also want to implement a general-purpose system for running queries over in-memory data, so that adapters for non-SQL data sources which don’t support some of the traditional functionality such as grouping or Skip/Take can use it to fill in the missing functionality. I’ve done a spike on this and it’s not as complicated as it sounds, so it shouldn’t take too long.

After that there’ll be a minor version bump to 0.7; releases within that series will focus on optimization and code quality, so lots of profiling and NDepend japery, which might inspire some blog posts. And then… 1.0!