paging (ascendingly ordered) with azure table storage

Question

This works fine to page over data in steps of at most 1000 items:

var q1 =
  (from book in table.CreateQuery<DynamicTableEntity>()
   where book.PartitionKey == "TestPartition"
   select book).AsTableQuery();

TableContinuationToken continuationToken = null;
do
{
    var counter = 0;
    var queryResult = q1.ExecuteSegmented(continuationToken);

    foreach (var entity in queryResult)
    {
    Console.WriteLine(entity.Timestamp + " " + ++counter);
    }

    continuationToken = queryResult.ContinuationToken;
    Console.WriteLine("####" + counter);
} while (continuationToken != null);

What I would really like to do is start with the oldest items first. In other words page over items ordered ascendingly by entity.Timestamp. This query does not work:

    var q1 =
  (from book in table.CreateQuery<DynamicTableEntity>()
   where book.PartitionKey == "TestPartition"
   select book).OrderBy(x => x.Timestamp).AsTableQuery();

    TableContinuationToken continuationToken = null;
    do
    {
        var counter = 0;
        var queryResult = q1.ExecuteSegmented(continuationToken);

        foreach (var entity in queryResult)
        {
        Console.WriteLine(entity.Timestamp + " " + ++counter);
        }

        continuationToken = queryResult.ContinuationToken;
        Console.WriteLine("####" + counter);
    } while (continuationToken != null);

As OrderBy is not supported. Is there anything I can do to achieve this? Thanks.

PS:

This may help. However it retrieves the newest items first, whereas I want to retrieve the oldest first.

Peter · Answer 1 · 2016-02-21T13:21:15.223

1

The TableQuery does not support order by. You can find the supported operators: Supported operators

edited Feb 21 '16 at 13:21

answered Feb 21 '16 at 12:58

Peter

27,590
8
64
84

Thanks. This throws the same exception I received when I tried to use OrderByDescending: {"Unable to cast object of type 'Microsoft.WindowsAzure.Storage.Table.TableQuery`1[Microsoft.WindowsAzure.Storage.Table.DynamicTableEntity]' to type 'System.Linq.IOrderedQueryable`1[Microsoft.WindowsAzure.Storage.Table.DynamicTableEntity]'."} – cs0815 Feb 21 '16 at 13:05

cs0815 · Accepted Answer · 2016-02-21T14:31:52.310

This link and the quoted white paper helped me to solve this one.

Step 1:

Use ticks as rowkey whilst creating the entities like so:

var rowKey = (DateTime.UtcNow.Ticks - DateTime.MinValue.Ticks).ToString();
var entity = new DynamicTableEntity("TestEventPartition", rowKey);

To order the entities ascendingly during paged retrieval, use the 'TableContinuationToken approach' involving the rowkey ticks:

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

var q1 =
  (from entity in table.CreateQuery<DynamicTableEntity>()
   where entity.PartitionKey == "TestPartition"
   && string.Compare(entity.RowKey, rowKeyToUse, StringComparison.Ordinal) > 0
   select entity).AsTableQuery();

TableContinuationToken continuationToken = null;
do
{
    var counter = 0;
    var queryResult = q1.ExecuteSegmented(continuationToken);

    foreach (var entity in queryResult)
    {
    Console.WriteLine("[" + entity.RowKey + "]"
         + ++counter
         );      
    }

    continuationToken = queryResult.ContinuationToken;
    Console.WriteLine("####" + counter);
} while (continuationToken != null);

Hope this helps someone else. Any improvement suggestion/criticism welcome.

If you use regular Execute rather than ExecuteSegmented you don't have to do any of the continuation token logic. These will be follow automatically and the next list call made under the covers as you iterate through entities. — Emily Gerner, Feb 23 '16 at 18:07

paging (ascendingly ordered) with azure table storage

2 Answers2