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.
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.
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.
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.
The above query retrieves the names of all the items in the Table.
Defines the upper bound for the number of entities the query returns.
Below is the query which uses - Where, Select and Take methods, together.
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);
See More Questions and Answers on - Azure Table Storage (cont..)
- How to access the Azure table storage from a .NET application?
- What is an Azure storage connection string?
- How to configure Azure connection strings?
- How to configure Azure connection strings for connecting to the storage emulator?
- How to create a table in Azure storage?
- How to create a table using a simple c# console application?
- How to insert entities into the Azure table?
- How do I retrieve a single entity from table storage using tableoperation?
- How to update records in a table storage?