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:

[sourcecode language="csharp"]var q = _db.Employees.Query()
.Join(_db.Department, Id: _db.Employees.DepartmentId)
.Select(_db.Employees.Name, _db.Department.Name.As("Department"));[/sourcecode]

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

[sourcecode language="csharp"]var q = _db.Employees.Query()
.Join(_db.Department).On(_db.Department.Id == _db.Employees.DepartmentId)
.Select(_db.Employees.Name, _db.Department.Name.As("Department"));[/sourcecode]

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:

[sourcecode language="csharp"]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"));[/sourcecode]

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:

[sourcecode language="csharp"]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"));[/sourcecode]

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!