Azure Table Storage: How can I create a dynamic where clause?

Question

Ok, so I am using Azure Table Storage for the first time in a ASP.NET MVC 3 application.

I have a table entity that has a user ID as its RowKey. I have a list of user IDs and need to get all of the entities that have one of the User IDs.

In traditional SQL it would be a simple OR statement in the where clause that you can dynamically add to:

select * from blah
where userID = '123' or userID = '456' or userID = '789'

but I haven't found the equivalent in the Azure SDK.

Is this possible with Azure Table Storage?

Thanks, David

Answer 1

• The .Net client for Azure Table Storage has features to generate and combined filters.

So that you can write your filter expression like that

string[] split = IDs.Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
string mainFilter = null;
foreach (var id in split)
{
    var filter = TableQuery.GenerateFilterCondition("RowKey", QueryComparisons.Equal, id);
    mainFilter = mainFilter != null ? TableQuery.CombineFilters(mainFilter, TableOperators.And, filter) : filter;
}

var rangeQuery = new TableQuery<Blah>().Where(mainFilter);
var result = table.ExecuteQuery(rangeQuery);

• I am using Windows Azure Storage 7.0.0 and you can use Linq query to filter.

Unfortunately Contains method is not supported by the Table Service but you can write a simple method to build dynamically your linq query:

public static class ContainsExtension
{
    public static Expression<Func<TEntity, bool>> Contains<TEntity,
        TProperty>(this IEnumerable<object> values,
        Expression<Func<TEntity, TProperty>> expression)
    {
        // Get the property name
        var propertyName = ((PropertyInfo)((MemberExpression)expression.Body).Member).Name;

        // Create the parameter expression
        var parameterExpression = Expression.Parameter(typeof (TEntity), "e");

        // Init the body
        Expression mainBody = Expression.Constant(false);

        foreach (var value in values)
        {
            // Create the equality expression
            var equalityExpression = Expression.Equal(
                Expression.PropertyOrField(parameterExpression, propertyName),
                Expression.Constant(value));

            // Add to the main body
            mainBody = Expression.OrElse(mainBody, equalityExpression);
        }

        return Expression.Lambda<Func<TEntity, bool>>(mainBody, parameterExpression);
    }
}

So that you can build dynamic queries easily :

var storageAccount = CloudStorageAccount.Parse(ConfigurationManager.AppSettings["TableStorageConnectionString"]);

var tableClient = storageAccount.CreateCloudTableClient();
var table = tableClient.GetTableReference("Blah");
var split = IDs.Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);

// Create a query: in this example I use the DynamicTableEntity class
var query = table.CreateQuery<DynamicTableEntity>()
        .Where(split.Contains((DynamicTableEntity d) => d.RowKey));

// Execute the query
var result = query.ToList();

Answer 2

Alrighty, with a bit more digging I found the answer.

You can construct a where filter using the syntax found here: http://msdn.microsoft.com/en-us/library/windowsazure/ff683669.aspx

So for my little example it ended up looking like this:

I have a comma delimited string of IDs sent to this method

CloudStorageAccount storageAccount = CloudStorageAccount.Parse(ConfigurationManager.AppSettings["TableStorageConnectionString"]);

CloudTableClient tableClient = storageAccount.CreateCloudTableClient();
CloudTable table = tableClient.GetTableReference("Blah");

string[] split = IDs.Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);

string filter = null;
for (int i = 0; i < split.Length; i++)
{
    filter += " RowKey eq '" + split[i] + "' ";
    if (i < split.Length - 1)
        filter += " or ";
}

TableQuery<Blah> rangeQuery = new TableQuery<Blah>().Where(filter);
var result = table.ExecuteQuery(rangeQuery);

Result has the list of goodies I need.

One thing to keep in mind is that you wouldn't want to use this on a really large table because I am only getting the RowKey which causes a table scan. If you use the PartitionKey and RowKey together it is more efficient. My table is pretty small (few hundred records at most) so it shouldn't be an issue.

Hope this helps someone.

David