Liam Cavanagh's Blog

I just learned a handy trick for sorting results from the Azure Table Store chronologically.  The problem I had is that Windows Azure Tables did not allow me to perform an OrderBy LINQ query on the results that were coming back and by default all of the items were returned sorted by the PartitionKey and RowKey.  This meant that the results were coming back lexically sorted which was the opposite order to what I wanted because I wanted to show the top 100 most recently added items first.   If I used the default sorting from Azure Tables, I would have to retrieve all the rows within a certain timeframe and then sort them in my code.  This is not only innefficient, but also costly because of all the data than would need to be retrieved when I just want the top 100.

The trick I learned to handle this came from this whitepaper  which uses a RowKey value of DateTime.MaxValue.Ticks -  DateTime.UtcNow.Ticks to allow me to sort the items by an offsetted time from newest items to older items.

For example, to add the row:

 LogEvent logEvent = new LogEvent();
 logEvent.PartitionKey = userName;
 logEvent.RowKey = string.Format("{0:D19}", DateTime.MaxValue.Ticks - DateTime.UtcNow.Ticks); ;
 logEvent.UserName = userName;
 logEvent.EventType = EventType;
 logEvent.EventValue = EventValue;
 logEvent.EventTime = DateTime.Now;

Then to retrieve the values, I query the results like this:

 string rowKeyToUse = string.Format("{0:D19}", DateTime.MaxValue.Ticks - DateTime.UtcNow.Ticks);

 var results = (from g in tableServiceContext.CreateQuery<LogEvent>("logevent")
where g.PartitionKey == User.Identity.Name
&& g.RowKey.CompareTo(rowKeyToUse) > 0
select g).Take(100);

By doing this, I was able to retrieve the results in the correct order or most recently added items first and then limiting the results to the most recent 100 rows by using the the .Take(100) parameter.

Liam