CODEWITHSUNDEEP
azureazure-table-storageazure-tablequery
Sam
Sam

Reputation: 30388

Can PartitionKey be queried with StartsWith?

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?

Upvotes: 21

Views: 11170

Answers (3)

richaux
richaux

Reputation: 2672

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

Upvotes: 37

Pedro G. Dias
Pedro G. Dias

Reputation: 3222

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

Upvotes: -3

Gaurav Mantri
Gaurav Mantri

Reputation: 136196

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/.

Upvotes: 3

Related Questions