Intro

In the previous post I have showed how to work with batch operations and provided some basic code samples, in this post I am going to show how to query data from Azure Table

Prerequisites

Refer to previous posts to get started with Azure Storage Table Service.

Code

There are 4 different types of queries that can be performed against Azure Table. I am going to briefly cover them all in this post. For more information visit Azure Storage Table Design Guide: Designing Scalable and Performant Tables

Test Data

The following code will fill a table with required data:

var auBatch = new TableBatchOperation();
var nzBatch = new TableBatchOperation();
var euBatch = new TableBatchOperation();
for (var i = 0; i < 100; i++)
{
    auBatch.Insert(new Profile("AU", $"EMP{i:D3}", $"John Doe {i}", $"The {i} employee of the company"));
    nzBatch.Insert(new Profile("NZ", $"EMP{i:D3}", $"John Doe {i}", $"The {i} employee of the company"));
    euBatch.Insert(new Profile("EU", $"EMP{i:D3}", $"John Doe {i}", $"The {i} employee of the company"));
}

table.ExecuteBatch(auBatch);
table.ExecuteBatch(nzBatch);
table.ExecuteBatch(euBatch);
Point Query

This is the most efficient lookup to use and is recommended to be used for high-volume lookups or lookups requiring lowest latency. You simply retrieve an entity by PartitionKey and RowKey. Consider the following example:

var result = table.Execute(TableOperation.Retrieve<Profile>("AU", "EMP001"));
Console.WriteLine(result.Result);

This query retrieves the entity blazingly fast because all your data is indexed by PartitionKey and RowKey, it doesn't matter how much entities you have.

Range Query

This lookup is less efficient, it will scan through some amount of keys in a partition with given PartitionKey. The PartitionKey value identifies a specific partition, and the RowKey values identify a subset of the entities in that partition.

The following example show such type of query:

var partitionKeyFilter = TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, "AU");

var rowKeyFilter = TableQuery.CombineFilters(
    TableQuery.GenerateFilterCondition("RowKey", QueryComparisons.GreaterThanOrEqual, "EMP010"), TableOperators.And,
    TableQuery.GenerateFilterCondition("RowKey", QueryComparisons.LessThanOrEqual, "EMP015"));

var filter = TableQuery.CombineFilters(partitionKeyFilter, TableOperators.And, rowKeyFilter);

var result = table.ExecuteQuery(new TableQuery<Profile>().Where(filter));
foreach (var profile in result)
{
    Console.WriteLine(profile);
}

This code will output following lines:

AU:EMP010: John Doe 10, The 10 employee of the company
AU:EMP011: John Doe 11, The 11 employee of the company
AU:EMP012: John Doe 12, The 12 employee of the company
AU:EMP013: John Doe 13, The 13 employee of the company
AU:EMP014: John Doe 14, The 14 employee of the company
AU:EMP015: John Doe 15, The 15 employee of the company

The more entities you have in partition - the more time query will take.

Partition Scan

This query is less efficient than Range Query. It uses the PartitionKey and filters on another non-key property and that may return more than one entity. The PartitionKey value identifies a specific partition, and the property values select for a subset of the entities in that partition.

Sample query:

var partitionKeyFilter = TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, "AU");
var titleFilter = TableQuery.GenerateFilterCondition("Title", QueryComparisons.Equal, "John Doe 10");
var filter = TableQuery.CombineFilters(partitionKeyFilter, TableOperators.And, titleFilter);

var result = table.ExecuteQuery(new TableQuery<Profile>().Where(filter));
foreach (var profile in result)
{
    Console.WriteLine(profile);
}

This code outputs the following:

AU:EMP010: John Doe 10, The 10 employee of the company

This query is slower than a similar query with RowKey because it scans non-key property.

Table Scan

The most inefficient query of all. If query does not include the PartitionKey it will search all of the partitions that make up your table in turn for any matching entities. It will perform a table scan regardless of whether or not your filter uses the RowKey.

Consider the following code:

var filter = TableQuery.GenerateFilterCondition("Title", QueryComparisons.Equal, "John Doe 10");
var result = table.ExecuteQuery(new TableQuery<Profile>().Where(filter));
foreach (var profile in result)
{
    Console.WriteLine(profile);
}

It will output the following:

AU:EMP010: John Doe 10, The 10 employee of the company
EU:EMP010: John Doe 10, The 10 employee of the company
NZ:EMP010: John Doe 10, The 10 employee of the company

So the full scan over all partitions has been performed. It is the most inefficient operation.

Summary

In this post I showed how to perform different types of queries against Azure Table. In the next post I am going to continue descibing Azure Table query capabilities.


;