Is this possible using Azure Tables?

Question

I get a error message “the method ‘join’ is not supported” in the below linq query:

tableServiceContext = new CustomTableServiceContext(storageAccount.TableEndpoint.AbsoluteUri, storageAccount.Credentials);
tableServiceContext.RetryPolicy = RetryPolicies.Retry(3, TimeSpan.FromSeconds(1));
var results = (from c in tableServiceContext.CreateQuery<ChannelEntry>("Channels").AsTableServiceQuery<ChannelEntry>()
    join v in tableServiceContext.CreateQuery<VideoEntry>("Videos").AsTableServiceQuery<VideoEntry>() on c.PartitionKey equals v.ChannelID
    join h in tableServiceContext.CreateQuery<HitEntry>("Hits").AsTableServiceQuery<HitEntry>() on v.PartitionKey equals h.VideoID
    where c.RowKey.Equals(UserID)
    group h by h.RowKey into g
    select new BiggestFan { UserID = g.Key, Hits = g.Count() }).AsTableServiceQuery().Execute().OrderByDescending(b => b.Hits).Take(1);

If “join” is not supported in this context then what would be the most efficient way to do my query ?

I have Channels which are made up of Videos which in turn have Hits. I’m trying to find the biggest fan (highest hits) of the currently logged in user.

What would be the most efficient way of doing this type of this without using joins? Would I have to grab all the Channels then Videos and then Hits as 3 separate calls to the Table Storage and then do the joins after that?

Answer 1

Yes you can't join. You have a couple options here.

1) Multiple scans - slap a couple of .ToArray() statements before you joins so that its doing the join in memory in your app. This is not performant but table storage is pretty fast. Really comes down to how many rows this will result in.

2) Denormalise your tables so that you have references to all the keys you need in a single table. This will let you get your results in 1 query, but means all insert/update logic needs to be updated.

Answer 2

There are 3 things in your query that are not supported by Azure Table Storage (AZT, my abbreviation, not generally used by others) querys.

1. Joins
2. Grouping
3. Aggregate functions

The short version is that if you want to run an efficient query in AZT then you need to run it against just one table and query against the partition key or partition key and row key.

This doesn't mean that your base data has to be stored in just this one table, you can keep the structure that you currently have, but you may need to build a table that is basically an index to allow you to get the info that you want. It might have a structure similar to this:

PartitionKey = ChannelUserId.PadWithLeadingZeros() + "-" + (int.MaxValue - NumberOfHits).PadWithLeadingZeros();
RowKey = Fan User Id;

Your query would then look something like this:

tableServiceContext = new CustomTableServiceContext(storageAccount.TableEndpoint.AbsoluteUri, storageAccount.Credentials);
tableServiceContext.RetryPolicy = RetryPolicies.Retry(3, TimeSpan.FromSeconds(1));
var results = (from i in tableServiceContext.CreateQuery<BiggestFansIndex>("BiggestFansIndex").AsTableServiceQuery<BiggestFansIndex>()
    where i.PartitionKey.CompareTo(UserId.PaddedWithLeadingZeros()) >= 0
        && i.PartitionKey.CompareTo((UserId + 1).PaddedWithLeadingZeros()) < 0
    select i}).Take(1).Execute();

Your biggest problem I suspect will be keeping this index table up to date as I'm sure hits will change with reasonable regularity.