Q81: How do I query azure table storage using TableQuery class?

We can construct a query using the TableQuery class and then execute the query against the Table using ExecuteQuery method.

For example, if the data had an integer property for Stock-in-hand, we can retrieve items in the RawMaterials category that is in stock using the following code. Note that the result of the query is in IEnumerable form and so we can use LINQ operators on the result.

  
// Retrieve the storage account from the connection string
CloudStorageAccount storageAccount = CloudStorageAccount.Parse(
    CloudConfigurationManager.GetSetting("StorageConnectionString"));

// Create the table client
CloudTableClient tableClient = storageAccount.CreateCloudTableClient();

// Create the CloudTable object that represents the "items" table
  CloudTable table = client.GetTableReference("items");

TableQuery<Item> itemStockQuery = new TableQuery<Item>().Where(
TableQuery.CombineFilters(
TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, "RawMaterial"),
	TableOperators.And,
TableQuery.GenerateFilterConditionForInt("Stock-in-hand", QueryComparisons.GreaterThan, 0)));

var rawMtlStock = table.ExecuteQuery(itemStockQuery);
if (rawMtlStock.Any())
{
    foreach (ItemEntity  item in rawMtlStock)
    {
      Console.WriteLine("Item: {0} as {1} items in stock", item.Name, item.Stock-in-hand);
    }
}


Table query methods


We have already seen how to retrieve a single entity using a RowKey and a PartitionKey combination. The TableQuery class can be used for more complex data queries like the following. The storage client library exposes many methods through the TableQuery class. In the below query, there are two main elements - GenerateFilterCondition and QueryComparisons. Any complex query can be built using the different combinations. The syntax for the GenerateFilterCondition method is given below.

GenerateFilterCondition method generates a property filter condition string for the string value.

public static string GenerateFilterCondition (
	string propertyName,
	string operation,
	string value)
  


In the above GenerateFilterCondition() method, PropertyName is a string containing the name of the property to compare, operation is a string containing the comparison operator to use and value is a string containing the value to compare with the property.

 //The following query retrieves all entities with a PartitionKey=”RawMaterial”
 string pkFilter = TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, "RawMaterial");

// The following query retrieves all entities with a RowKey greater than or equal to “10”
string rkLowerFilter = TableQuery.GenerateFilterCondition("RowKey", QueryComparisons.GreaterThanOrEqual, "10");

// The following query retrieves all entities with a RowKey less than to “20”
string rkUpperFilter = TableQuery.GenerateFilterCondition("RowKey", QueryComparisons.LessThan, "20");



TableQuery.Select Method


Defines the property names of the table to return when the table query is executed. The select clause is optional on a table query, used to limit the table properties returned from the server. By default, a query will return all properties from the table entity.

CloudTable customerTable = tableClient.GetTableReference("items");
TableQuery query = new TableQuery().Select(new string[] { "ItemName" });
customerTable.ExecuteQuery(query);


The above query retrieves the names of all the items in the Table.

TableQuery.Take Method


Defines the upper bound for the number of entities the query returns.
query.Take(10);


Below is the query which uses - Where, Select and Take methods, together.

TableQuery query = new TableQuery().Where(TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, "RawMaterial")).Select(new string[] { "ItemName" }).Take(5);