How do I select 50 to 100 rows from Azure Table using the StorageClient?

Question

I need to select several rows within a single partition of Azure Tables for later update. Since they all have the same PartitionKey, how do I structure my query to select multiple RowKeys?

I'm interested in what the raw (on the wire) query should look like, and if possible the Linq query as well.

I attempted this on my own and started with this query:

 var results = from c in _ServiceContext.ForumThreadTable
                          where(
                          (
                          (c.RowKey == rk)    ||
                          (c.RowKey == "n1q") ||
                          (c.RowKey == "gm1w") ||
                          (c.RowKey == "fm1e") ||
                          (c.RowKey == "zbm1r") ||
                          (c.RowKey == "km1te1") ||
                          (c.RowKey == "jm1ye1") ||
                          (c.RowKey == "hm1u") ||
                          (c.RowKey == "gm1i") ||
                          (c.RowKey == "fm1te1") ||
                          (c.RowKey == "d4m1ye1") ||
                          (c.RowKey == "bm1u") ||
                          (c.RowKey == "bm1i") ||
                          (c.RowKey == "bm1o") ||
                          (c.RowKey == "bp1p") 
                          ) &&

                          c.PartitionKey == pk )
                          select c;

My query looked like this (from fiddler)

GET http://127.0.0.1:10002/devstoreaccount1/ForumThreadTable()?$filter=(((((((((((((((RowKey%20eq%20'0634205427898279774')%20or%20(RowKey%20eq%20'n1q'))%20or%20(RowKey%20eq%20'gm1w'))%20or%20(RowKey%20eq%20'fm1e'))%20or%20(RowKey%20eq%20'zbm1r'))%20or%20(RowKey%20eq%20'km1te1'))%20or%20(RowKey%20eq%20'jm1ye1'))%20or%20(RowKey%20eq%20'hm1u'))%20or%20(RowKey%20eq%20'gm1i'))%20or%20(RowKey%20eq%20'fm1te1'))%20or%20(RowKey%20eq%20'd4m1ye1'))%20or%20(RowKey%20eq%20'bm1u'))%20or%20(RowKey%20eq%20'bm1i'))%20or%20(RowKey%20eq%20'bm1o'))%20or%20(RowKey%20eq%20'bp1p'))%20and%20(PartitionKey%20eq%20'GUIDeec4550c-a3fd-472b-9b7d-c79fae664415') HTTP/1.1
User-Agent: Microsoft ADO.NET Data Services
DataServiceVersion: 1.0;NetFx
MaxDataServiceVersion: 2.0;NetFx
x-ms-version: 2009-09-19
x-ms-date: Mon, 20 Sep 2010 02:49:48 GMT
Authorization: SharedKeyLite devstoreaccount1:MINFtQhWqbnYhn1spDGTGvPmNmW24YNzOeqBBtOletU=
Accept: application/atom+xml,application/xml
Accept-Charset: UTF-8
Host: 127.0.0.1:10002

and here is my error:

"<?xml version=\"1.0\" encoding=\"utf-8\" standalone=\"yes\"?>
 <error xmlns=\"http://schemas.microsoft.com/ado/2007/08/dataservices/metadata\"&gt;
 <code>InvalidInput</code>
 <message xml:lang=\"en-US\">One of the request inputs is not valid.</message>
 </error>"

I think it may have to do more with the quantity of RowKey conditions I have, rather than the syntax... but I'm open to try anything.

Thanks!

Answer 1

That's something similar to what we've been doing in Lokad.Cloud. As I recall you can select up to 100 rows in this manner without problems. You can check the source code for the details and reuse either the entire open source project or just the parts you like.

Here's how the syntax generation looks like:

public IEnumerable<CloudEntity<T>> Get<T>(string tableName, string partitionKey, IEnumerable<string> rowKeys)
{
  Enforce.That(() => tableName);
  Enforce.That(() => partitionKey);
  Enforce.That(!partitionKey.Contains("'"), "Incorrect char in partitionKey.");

  var context = _tableStorage.GetDataServiceContext();

  foreach (var slice in rowKeys.Slice(MaxEntityTransactionCount))
  {
    // work-around the limitation of ADO.NET that does not provide a native way
    // of query a set of specified entities directly.
    var builder = new StringBuilder();
    builder.Append(string.Format("(PartitionKey eq '{0}') and (", HttpUtility.UrlEncode(partitionKey)));
    for (int i = 0; i < slice.Length; i++)
    {
      // in order to avoid SQL-injection-like problems 
      Enforce.That(!slice[i].Contains("'"), "Incorrect char in rowKey.");

      builder.Append(string.Format("(RowKey eq '{0}')", HttpUtility.UrlEncode(slice[i])));
      if (i < slice.Length - 1)
      {
        builder.Append(" or ");
      }
    }
    builder.Append(")");

    foreach(var entity in GetInternal<T>(context, tableName, builder.ToString()))
    {
      yield return entity;
    }
  }
}

However, please keep in mind, that this operation will not be optimized by the table storage, resulting in suboptimal performance (table storage is not using indexes for this operation). This is a bug that was reported to Microsoft and is expected to be fixed some time soon.