CODEWITHSUNDEEP
c#azureazure-table-storageazure-tablequery
cs0815
cs0815

Reputation: 17418

paging (ascendingly ordered) with azure table storage

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.

Upvotes: 2

Views: 2036

Answers (2)

cs0815
cs0815

Reputation: 17418

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.

Upvotes: 1

Peter
Peter

Reputation: 27944

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

Upvotes: 1

Related Questions