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();