Can PartitionKey be queried with StartsWith?

Question

In Azure Table Storage, is it possible to query PartitionKey with a StartsWith or some other operator e.g. Contains, etc.

I know I can do this with RowKeys but is it possible to do it with PartitionKeys?

A follow up question is: even if it's doable, is it advisable? Should PartitionKey always be an exact match -- say, for performance reasons?

Jacksnipe

Answer 1

Well, the good news is that you CAN do partial matches, and you will get "good" performance as long as the number of partitions being hit is small. If you have lots of partition keys, performance will suffer.

I could try to summarize an excellent write-up on this, but it's already written, so if you point your browser to the following link, you should learn all there is to know about partitions, rowkeys, and expected performaces:

https://msdn.microsoft.com/en-us/library/azure/hh508997.aspx

Bequest

This answer is incorrect! You can't do partial searches with Azure Tables using operators like StartsWith or Contains. – Kimon

We prefer answers here without voting requests please - posts are best attracting +/- votes organically. Thanks! – Triptych

Answer 2

Here's an example using the GreaterThanOrEqual and LessThan operators, as an extension on the target column name.

The filter combines two parts:

• anything greater than or equal to your startsWith prefix,
• increment the last character of your prefix and find anything less than this.

For example, startsWith prefix "CAR" will prepare something like s >= "CAR" && s < "CAS".

    public static string GetStartsWithFilter(this string columnName, string startsWith)
    {
        var length = startsWith.Length - 1;
        var nextChar = startsWith[length] + 1;

        var startWithEnd = startsWith.Substring(0, length) + (char)nextChar;
        var filter = TableQuery.CombineFilters(
            TableQuery.GenerateFilterCondition(columnName, QueryComparisons.GreaterThanOrEqual, startsWith),
            TableOperators.And,
            TableQuery.GenerateFilterCondition(columnName, QueryComparisons.LessThan, startWithEnd));

        return filter;
    }

Usage:

var query = new TableQuery<MyTableEntity>().Where(myColumnName.GetStartsWithFilter(prefix));

Based on Alexandre B's blog post

Fade

You can get rid of the dependency on the WindowsAzure.Storage NuGet package by just constructing the query as a string: return $"({columnName} ge '{startsWith}') and ({columnName} lt '{startsWithEnd}')"; – Maunsell

Answer 3

In Azure Table Storage, is it possible to query PartitionKey with a StartsWith or some other operator e.g. Contains, etc.

No, it is not possible to do queries using StartsWith or Contains query operator with Azure Tables. To simulate StartsWith, you would need to use the combination of Greater Than Equal To and Less Than operators. You can't use Contains operator. What you would need to do is first fetch all the data on the client side and then use Contains to filter the data on the client side only.

For a list of supported query operators, please see this link: https://msdn.microsoft.com/en-us/library/azure/dd135725.aspx.

I know I can do this with RowKeys but is it possible to do it with PartitionKeys?

I don't think it is possible. I'm curious to know why you're saying this.

A follow up question is: even if it's doable, is it advisable? Should PartitionKey always be an exact match -- say, for performance reasons?

I would highly recommend reading this excellent guide for this: https://azure.microsoft.com/en-in/documentation/articles/storage-table-design-guide/.

Kimon