Performing an Azure table storage case-insenstive query

Question

This may be somewhat related to this SO question How to perform a case-sensitive LINQ query in Azure?. However I'm using Storage Client 3.0, not the linq queries and the TableStorageContext in that question.

I have a table storage entity called Account that has a string property for email address. The email property is not a partition key or a row key.

I want to search for an entity with a matching email address in a case-insensitive way, such that a search for "[email protected]" returns "[email protected]", etc.

My code looks something like this:

TableQuery<Account> rangeQuery = new TableQuery<Account>().Where(
    TableQuery.CombineFilters(
        TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, "account"),
        TableOperators.And,
        TableQuery.GenerateFilterCondition("Email", QueryComparisons.Equal, email)));

var results = accountsTable.ExecuteQuery(rangeQuery).ToList();

Is there a way to perform a case insensitive query using the tableQuery class, or is there another approach? Should I just concentrate on data grooming and ensure that all data is forced to a consistent case?

Answer 1

To answer your question about doing case-insensitive search, it is not possible to do so with Windows Azure Table Storage.

One way is the one you suggested i.e. doing data grooming and store all in either lower or upper case.

Other approach would be to download the data on the client side and then doing case sensitive search on the data which is fetched on the client. 2nd approach may be feasible for smaller data sets but would not be practical if the data set is much bigger.

Answer 2

Searching for anything that is not the leftmost part of the partition+row key would result in a table scan.

One approach would be to store, in another table, the email in lower or upper case so that it can be searched without doing a table scan.

In your example say,

Partition key = test.com 
Row key = bob 

would work quite well and give you many querying options.