Building a photoblog with Nancy and Simple.Data Part 5: Updating Simple.Data

This is the fifth part in my Building a photoblog with Nancy and Simple.Data series:

1. Setting up the project
2. Defining the routes
3. Rendering some views
4. Adding the database
5. Updating Simple.Data
6. Adding comments
7. The archives

In the previous post we added a SQL Server Compact 4.0 database to our project and we used Simple.Data to fetch the latest photo to display it on the homepage.

In the previous we were using Simple.Data 0.5.6.0. Since then quite a few things have changed in Simple.Data 0.6.1.0. It is now possible to do paging and sorting at the database level. Let’s see how this impacts our code from the previous post.

Updating Simple.Data

Open the Package Manager Console and enter this command: Update-Package Simple.Data.SqlCompact40. This will fetch the latest version of Simple.Data.SqlCompact40 (and its dependencies Simple.Data.Core and Simple.Data.Ado) from the official NuGet feed and add them to our project. The old versions get removed. That was easy!

Updating our RootModule

Remember how we had to select all published photo’s and manually sort them?

// Get all photo's that are published
var photos = DB.Photos.FindAllByPublished(true);
List<Models.Photo> photoList = photos.ToList<Models.Photo>();

// Order them so the newest come first
photoList = photoList.OrderByDescending(p => p.DatePublished).ToList();

That’s not good for two reasons:

1. We are requesting an unbound result set. If I happened to be a very active photographer (which I’m not ), that could mean that we are asking the database to return a million records at once. That’s bad.
2. We have to sort everything in our code. In combination with the unbound result set, this could become very imperformant.

Let’s see how we can solve this with the new features in Simple.Data.

To sort the records directly in the database, we just append .OrderByXXX() or .OrderByXXXDescending() where XXX is the name of column belonging to the table we are querying:

var photos = DB.Photos.FindAllByPublished(true).OrderByDatePublishedDescending();

We now get list of photo’s where Published is set to true and is ordered by the DatePublished field with the highest date first. So now we have an ordered result set, but it’s still an unbound result set. Fixing that is also easy. Since we only need the first two photo’s, we need a top 2. This is solved in a very LINQish way in Simple.Data. We can simply append .Take(2):

var photos = DB.Photos.FindAllByPublished(true).OrderByDatePublishedDescending().Take(2);

That’s all there is to it!

Here is the complete code for the Get["/"] route:

Get["/"] = parameters =>
{
	var photos = DB.Photos.FindAllByPublished(true).OrderByDatePublishedDescending().Take(2);
	List<Models.Photo> photoList = photos.ToList<Models.Photo>();

	if (photoList.Count > 0)
	{
		var model = new Models.PhotoDetail();
		model.Photo = photoList[0];
		model.NextSlug = String.Empty;

		if (photoList.Count > 1) model.PreviousSlug = photoList[1].Slug;
		else model.PreviousSlug = String.Empty;

		return View["photodetail", model];
	}
	else
	{
		return View["nophoto"];
	}
};

That’s it for this time. As usual, the changes to the source code can found on Github.