Simple.Data 0.6.5

New features

I haven’t kept up to date with posts about the new features that are in the Simple.Data 0.6.x releases. I’m going to go ahead and blame the new baby, because he can’t defend himself. 🙂

So let’s have a bit of a catch-up. The 0.6 changes are all about more advanced querying. Up to now, it’s been possible to use complex criteria to search a single table, but the data that comes back is limited to the full row from the “central” table. That’s all well and good for the not-really-an-ORM use cases, but I’ve always envisaged Simple.Data as an alternative to ADO.NET and WebMatrix.Data, so it needs to be able to do complex queries and give the developer more control. Let’s take a look at some of the features that are in 0.6.5, which I’ve just pushed to NuGet this evening.

SimpleQuery

The core of these new features is the new SimpleQuery type. This is a dynamic type which allows queries to be constructed in a fluent style that resembles the dotted notation form of LINQ. Since 0.6.1, the All, FindAll and FindAllBy methods have returned a SimpleQuery instead of a result set. There are also new Query and QueryBy methods, which are effectively identical to All and FindBy, but make the intent clearer in your code. The query executes when it is enumerated in any way, so all this should be transparent to you, and backwards-compatible with any existing code.

Ordering and Paging

The first thing to get implemented, because it was the thing the most people were asking for, was paging. Paging allows you to specify which subset of rows within a result set you want to return, which is very useful for web applications where you don’t want to return 100,000 rows to your JavaScript client.

Ordering

Of course, if you’re returning a subset of rows, you need to specify an order so you get consistent results. So SimpleQuery supports ordering in a couple of ways:

If you’re only selecting from a single table, you can use the magic methods approach:

db.Users.All().OrderByJoinDate();

Or for most recent first:

db.Users.All().OrderByJoinDateDescending();

As much as possible, I’m trying to keep the method names similar to LINQ operators, so for ordering on multiple columns, there’s ThenBy:

db.Users.All().OrderByJoinDate().ThenByNickname();

In 0.6.4, you’ll also be able to specify a column list within an OrderBy[Descending] or ThenBy[Descending], like:

db.Users.All().OrderBy(db.Users.JoinDate, db.Users.Nickname);

Paging

Again trying to use LINQ conventions to keep things simple, paging is handled using Skip and Take methods:

db.Users.All().OrderByNickname().Skip(10).Take(10);

Although ideally you should, you don’t have to specify an OrderBy; if you omit it the query will be arbitrarily ordered on the first column, since that is commonly the table’s key.

The important thing to note here is that the paging is applied at the database level, using syntax specific to each RDBMS, or NoSQL datastore. (In the case of SQL Server, it uses ROWNUMBER OVER ORDER syntax; for SQL Compact, it’s the alternative OFFSET x FETCH NEXT y syntax. The authors of the other providers have to implement an interface to make it work with their RDBMS; adapter authors can implement paging however it is appropriate for their underlying store.) The thing I think is really neat about this is that it’s taken something which is differently implemented in almost every different database, and abstracts it away from you, meaning that you can seamlessly work with, for example, SQL Server Compact or SQLite in your development and test environment, and then SQL Server 2008 or Oracle in production.

Explicit column selection

As I’ve already said, prior to 0.6, Simple.Data has just returned complete rows from a single table. It just did a “SELECT * FROM [table]…” under the covers. This is not ideal for some scenarios, such as very wide tables, or tables with lookup data that should be pulled back.

Now, SimpleQuery has a Select method, which works like this:

db.Users.All().Select(db.Users.Id, db.Users.Name);

You can also use the natural joining capabilities within that list:

db.Users.All().Select(db.Users.Name, db.Users.UserProfiles.BioText);

Obviously if you do that with a one-to-many join, you’ll get the master table’s data repeated as you would with a SQL statement. Which is what is getting run. So that makes sense, really.

Aliasing

This introduces the possibility of column name ambiguity into the mix; you might want to select two columns with the same name from two tables.

To cope with this, you can now assign aliases to columns within the selection using the As method:

db.Employees.All().Select(db.Employee.Name, db.Employees.Department.Name.As("DepartmentName");
Console.WriteLine("{0} works in {1}", emp.Name, emp.DepartmentName);

Aggregates

Long post! I had a good afternoon of coding last weekend. So the last thing to announce for this 0.6.5 release is support for aggregate functions. These are implemented as methods on the query object, the table object or the column objects within a selection list.

Table aggregates

At the table level, there is now support for Count and Exists, which is also exposed as Any. These methods can be called with no parameters on a Query, or as methods similar to Find and FindBy on the table object:

int adults = _db.Users.Query().Where(_db.Users.Age >= 18).Count();
// … is the same as …
int adults = _db.Users.Count(_db.Users.Age >= 18);
bool anyBirthdays = _db.Users.FindByDateOfBirth(DateTime.Now.Date).Any();
// … is the same as …
bool anyBirthdays = _db.Users.ExistsByDateOfBirth(DateTime.Now.Date);
// … and also …
bool anyBirthdays = _db.Users.AnyByDateOfBirth(DateTime.Now.Date);

That’s several ways of doing the same thing, but, hey, this is data access, so it’s kind of traditional. If you’re a provider author, don’t worry, it’s all handled. If you’re an adapter author, the SimpleQuery object that you get to play with will look the same regardless which of these methods are used.

Column aggregates

The most common aggregate methods at column level have been implemented: Min, Max, Sum and Average. You can specify these as methods on the column objects in the Select call:

_db.Employees.Query()
    .Select(_db.Employees.Department.Name, _db.Employees.Age.Average().As("AverageAge"));

The observant reader will have noticed that there is no GroupBy method in that code. I was trying to work out how to implement it in the simplest way, and I realised that whatever was specified in the GroupBy would have to be all the columns from the Select method which weren’t aggregates. Since that information already exists in the Query object, why make the developer write it out again? I did a little checking, and MySQL is of the same opinion, but most RDBMS systems still seem to require an explicit GROUP BY clause. So that’s another place where Simple.Data makes your life a little bit easier.

Length

I’ve also added support for the LEN function, which is represented by a Length method on columns:

_db.Users.Query.Select(_db.Users.Name.Length().As("NameLength"));

Length calls will not apply grouping to the query.

And also

There is more new functionality (for example the ToScalar<T>() method) but I want to wrap this up and get the release done. Do take a look at the code for SimpleQuery if you want to know what other methods it supports; reading the code (especially the tests) is still the best way of learning Simple.Data until I get some proper documentation worked out.

I’ll write another post this week detailing what’s left to do before I bump the version to 0.9 and call it a feature-complete beta, but in the meantime, if any of these new features are useful to you, please do get 0.6.5 and start hammering it; I’ll try to turn around fixes rapidly if you report bugs on Github. My absolute favourite form of bug report is a pull request with a fix, but pull requests with failing tests added to the test suite are a very close second.

Comments

  1. Sam High says:

    Thanks i am just starting simple project and rather new to .net but seasoned asp/sql dev. I love webmatrix and the razor syntax makes the transition very easy. I was looking for a DAL beyond .Data. Your lib is awsome….. I just got the latest and you added just what i was looking for .All .OrderBy etc…

  2. Mark, I’ve heard about Simple.Data for months not and finally downloaded to test it out. Note that I know squat about Dynamics. When I run, I get this error immediately:
    System.Dynamic.ExpandoObject’ does not contain a definition for ‘ConnectionString'”}

    Any suggestions? I am running PerformanceTestConsole. I modified the connection string so that it connects to a valid db – was getting error with the default of course.

    I also then got LoadExceptions, so removed the dll references and added them back as project references: Just Simlpe.Data and Simple.Data.ADO Now I get this missing definition exception.

  3. Hi, how do we get total from the resultset,
    {
    var var_Jobs = db.user_mon_settings.FindAllByusername(username: “Safi”);

    int count = var_Jobs.Keys.Count; // HOW DO WE GET TOTAL
    }

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: