dynamic query to azure tables

Question

I'm using azure table storage to store blog posts. Each blog post can have different tags. So I'm going to have three different tables.

One which will store the blog posts.
One to store the tags
One that will store the relation between the tags and posts

So my question is as following, is it possible to create dynamic search queuries? Because I do not know until at run time how many tags I want to search. As I understand it you can only query azure table using LINQ. Or can I input a string query that I can change dynamically?

UPDATE

Here's some example data that's in the blog table

PartitionKey,RowKey,Timestamp,Content,FromUser,Tags
user1, 1, 2012-08-08 13:57:23, "Hello World", "root", "yellow,red"

blogTag table

PartitionKey,RowKey,Timestamp,TagId,TagName
"red", "red", 2012-08-08 11:40:29, 1, red
"yellow", "yellow", 2012-08-08 11:40:29, 2, yellow

relation table

PartitionKey,RowKey,Timestamp,DataId,TagId
1, 1, 2012-08-08 11:40:29, 1, 1
2, 1, 2012-08-08 13:57:23, 1, 2

One usage example of these tables is for example when I want to get all blog post with certain tag.

I have to query the tagId from the blogTag table
There after I need to search in the relation table for the dataId
Lastly I need to search blog table for blog post with that dataId

I'm using LINQ to perform the query and it looks like following

CloudTableQuery<DataTag> tagIds = (from e in ctx2.CreateQuery<DataTag>("datatags")
                                   where e.PartitionKey == tags
                                   select e).AsTableServiceQuery<DataTag>();

I tried Gaurav Mantri suggestion of using filter, and it works. But I'm afraid of how the effiency of that will be. And about the limitation of 15 discrete comparison that's only allowed.

You are not limited to LINQ. In the end it is a REST call to ATS. But you can can use LINQ to create dynamic queries(E.G. State = 'WA' or State = 'CA'). Please provide and example of a query you cannot do in LINQ and the ATS design. I think REST API is limited to like 40 filters. The 40 might be the wrong number but there is a limit. — paparazzo, Aug 09 '12 at 14:59
Multiple conditions can be multiple queries of a single filter (in parallel) then you perform the intersection or union in .NET. Design PartitionKey and RowKey for your queries. — paparazzo, Aug 09 '12 at 15:08
@Blam I updated by question with the query I'm using to search for tags, and also some example tables. I found out that the limitation is 15, or 14 accordingly to breischl's answer — starcorn, Aug 09 '12 at 15:24
Lucifer is strong on ATS. So consider his response on Lucene.NET. Your table design needs some work. Label the PartitionKey and RowKey for what data it will hold. I appears you are repeating a the Key in another property - if so bad design. http://msdn.microsoft.com/en-us/magazine/ff796231.aspx — paparazzo, Aug 09 '12 at 16:30

score 2 · Answer 1 · edited Apr 30 '14 at 08:32

2

You can simple build where clause and pass to where method for example:

var whereClause="(PartitionKey eq 'Key1') and (PartitionKey eq 'Key2')"

CloudStorageAccount storageAccount = CloudStorageAccount.Parse("AccountDetails");
            CloudTableClient tableClient = storageAccount.CreateCloudTableClient();

            CloudTable table = tableClient.GetTableReference(<TableName>);
            table.CreateIfNotExists();

            TableQuery<YourAzureTableEntity> query =
               new TableQuery<YourAzureTableEntity>()
                  .Where(whereClause));
            var list = table.ExecuteQuery(query).ToList();

edited Apr 30 '14 at 08:32

sharkbait

2,980
16
51
89

answered Apr 30 '14 at 08:14

Rukhsar Ahmad

171
9

+! I'm trying to figure out how to pass the entire odata string through Linq, including $filter, $top and $select: https://learn.microsoft.com/en-us/rest/api/storageservices/fileservices/querying-tables-and-entities – I Stand With Russia Feb 27 '17 at 22:31

score 1 · Answer 2 · answered Aug 09 '12 at 13:54

1

I am also facing exactly same problem. I did find one solution which I am pasting below:

public static IEnumerable<T> Get(CloudStorageAccount storageAccount, string tableName, string filter)
    {
        string tableEndpoint = storageAccount.TableEndpoint.AbsoluteUri;
        var tableServiceContext = new TableServiceContext(tableEndpoint, storageAccount.Credentials);
        string query = string.Format("{0}{1}()?filter={2}", tableEndpoint, tableName, filter);
        var queryResponse = tableServiceContext.Execute<T>(new Uri(query)) as QueryOperationResponse<T>;
        return queryResponse.ToList();
    }

Basically it utilizes DataServiceContext's Execute(Uri) method: http://msdn.microsoft.com/en-us/library/cc646700.aspx.

You would need to specify the filter condition as you would do if you're invoking the query functionality through REST API (e.g. PartitionKey eq 'mypk' and RowKey ge 'myrk'). Not sure if this is the best solution :) Looking forward to comments on this.

answered Aug 09 '12 at 13:54

Gaurav Mantri

128,066
12
206
241

So let's say you want to query on PartitionKey value "mypk" and RowKey value greater than "myrk", this is how you will define value for filter: PartitionKey eq "mypk" and RowKey ge "myrk". I wrote a blog post some time ago which you may find useful: http://www.cerebrata.com/Blog/post/Specifying-24filter-criteria-when-querying-Azure-Table-Storage-using-REST-API.aspx – Gaurav Mantri Aug 09 '12 at 14:55
Note that even though this works, you are limited to the length of the uri (I don't know exactly how long this is). – Sandrino Di Mattia Aug 09 '12 at 15:04
Max URL Length: http://stackoverflow.com/questions/417142/what-is-the-maximum-length-of-a-url – Gaurav Mantri Aug 09 '12 at 15:16
You have an error in your code; it should have said `"{0}/{1}()?$filter={2}"`. See http://msdn.microsoft.com/en-us/library/azure/dd179421.aspx for more. – Oct 19 '14 at 15:17

score 0 · Answer 3 · answered Aug 09 '12 at 14:58

It is possible, but it may not be a good idea. Adding multiple query parameters like that always results in a table scan. That's probably OK in a small table, but if your tables are going to be large it will be very slow. For large tables, you're better off running a separate query for each key combination.

That said, you can build a dynamic query with some LINQ magic. Here is the helper class I've used for that:

public class LinqBuilder
{
    /// <summary>
    /// Build a LINQ Expression that roughly matches the SQL IN() operator
    /// </summary>
    /// <param name="columnValues">The values to filter for</param>
    /// <returns>An expression that can be passed to the LINQ  .Where() method</returns>
    public static Expression<Func<RowType, bool>> BuildListFilter<RowType, ColumnType>(string filterColumnName, IEnumerable<ColumnType> columnValues)
    {
        ParameterExpression rowParam = Expression.Parameter(typeof(RowType), "r");
        MemberExpression column = Expression.Property(rowParam, filterColumnName);

        BinaryExpression filter = null;
        foreach (ColumnType columnValue in columnValues)
        {
            BinaryExpression newFilterClause = Expression.Equal(column, Expression.Constant(columnValue));
            if (filter != null)
            {
                filter = Expression.Or(filter, newFilterClause);
            }
            else
            {
                filter = newFilterClause;
            }
        }

        return Expression.Lambda<Func<RowType, bool>>(filter, rowParam);
    }

    public static Expression<Func<RowType, bool>> BuildComparisonFilter<RowType, ColumnType>(string filterColumnName, Func<MemberExpression, BinaryExpression> buildComparison)
    {
        ParameterExpression rowParam = Expression.Parameter(typeof(RowType), "r");
        MemberExpression column = Expression.Property(rowParam, filterColumnName);

        BinaryExpression filter = buildComparison(column);
        return Expression.Lambda<Func<RowType, bool>>(filter, rowParam);
    }

}

You would use it something like this:

var whereClause = BuildListFilter(queryColumnName, columnValues);
CloudTableQuery<RowType> query = (from r in tableServiceContext.CreateQuery<MyRow>("MyTable")
                                    where r.PartitionKey == partitionKey
                                    select r)
                                    .Where(whereClause) //Add in our multiple where clauses
                                    .AsTableServiceQuery(); //Convert to table service query
var results = query.ToList();

Note also that the Table service enforces a maximum number of constraints per query. The documented maximum is 15 per query, but when I last tried this (which was some time ago) the actual maximum was 14.

score 0 · Answer 4 · answered Aug 09 '12 at 16:01

Building something like this in table storage is quite cumbersome; akin to forcing a square peg in a round hole.

Instead you could considered using Blob storage to store your Blogs and Lucene.NET to implement your search of tags. Lucene would also allow more complex searches like (Tag = "A" and Tag = "B" and Tag != "C") and in addition would also allow searching over the blog text itself, if you so choose.

http://code.msdn.microsoft.com/windowsazure/Azure-Library-for-83562538

dynamic query to azure tables

4 Answers4