Querying Table Storage

Querying the Table

We’ve successfully added data to the table both via code and by importing with a tool.  While the Storage tools allow us to verify that we uploaded what we expected from our code, the applications are likely to be using code to retrieve their data.  Let’s look at a very simple sample query:  To do this we replace the code in the Try catch of the Main method with the following code:

try {     StorageCredentials creds = new StorageCredentials(accountName, accountKey);     CloudStorageAccount account = new CloudStorageAccount(creds, useHttps: true);       CloudTableClient client = account.CreateCloudTableClient();     CloudTable table = client.GetTableReference("sportingproducts");       TableOperation retrieveOperation = TableOperation.Retrieve<SportingProductEntity>("Baseball", "BBt1032");       TableResult query = table.Execute(retrieveOperation);       if (query.Result != null)     {         Console.WriteLine("Product: {0}", ((SportingProductEntity)query.Result).ProductName);     }     else     {         Console.WriteLine("The Product was not found.");     }   } catch (Exception ex) {     Console.WriteLine(ex); }

When you execute this code, you’ll see that the entity was retrieved and the product name was displayed.  In this case you will see that we queried by the partition key and row key, so essentially we were performing a lookup by the entity’s primary key.  There are numerous ways of querying for the data you have in storage, but knowing the exact partition key and row key will always be the fastest.

For the new Table Service Layer you can also use the lightweight query class TableQuery.  This class allows you to construct a query to send to the server that is more complex than simply asking for the PartitionKey and RowKey; however, the syntax is not as easy as working with LINQ.  If our data had an integer property for StockOnHand we could search for all items in the Baseball category that we have in stock using the following:

//.... CloudTable table = client.GetTableReference("sportingproducts");   TableQuery<SportingProductEntity> baseballInventoryQuery = new TableQuery<SportingProductEntity>().Where(     TableQuery.CombineFilters(         TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, "Baseball"),         TableOperators.And,         TableQuery.GenerateFilterConditionForInt("StockOnHand", QueryComparisons.GreaterThan, 0)));   var baseballInventory = table.ExecuteQuery(baseballInventoryQuery);   if (baseballInventory.Any()) {     foreach (SportingProductEntity product in baseballInventory)     {         Console.WriteLine("Product: {0} as {1} items in stock", product.ProductName, product.StockOnHand);     } } else {     Console.WriteLine("No inventory was not found.  Better order more!"); }