This post is part of a series of posts that aims to capture a number of emerging design patterns for the Windows Azure Platform. This series will begin with emerging design patterns for Windows Azure Table storage.

The Chronological Query Pattern aims to support queries that must return data in chronological or reverse chronological order.

Example

Looking at a standard example of blog data, stored as:

Challenge

Using LINQ to return the list of blogs in chronological or, in this case, reverse chronological order the following query might be tried:

var results =

    from p in this.context.Posts

    where p.PartitionKey == "Marcus"

    orderby p.DatePosted desc

    select p;

However, the orderby statement is not supported by Windows Azure Table storage (http://msdn.microsoft.com/en-us/library/dd135725.aspx).

Solution

The entities within a partition in a table are returned in the lexicographical order of the row key. This is a feature that can be utilised to ensure that entities are returned with a particular sort order. To create a row key that enables the data to be returned in reverse chronological order, the following statement with a string representation of ticks can be used:

RowKey  =

    String.Format("{0:D19}",

        DateTime.MaxValue.Ticks -      

            DateTime.UtcNow.Ticks );

Updating the blog table and replacing the Row Key with this derived value yields:

The original LINQ query can  be used with the order by statement omitted with the blog records returned in reverse chronological order.

Summary

Motivation:

To support queries that must return data in chronological or reverse chronological order.

Implementation:

The data within a partition in a table is returned in the lexicographical order of the row key.

Uses:

Where the chronological order of the data is important. Such as retrieving the “newest” or “oldest” items.

Reference

Windows Azure Table (Tips and Tricks Section)

Also See

Table Name Key Pattern

Hash Partitioning Pattern

Transactional Master-Item Record Pattern

Chronological Query Pattern

Starts With Query Pattern

@drmarcustillett

Tweet