Better support for paging with Table Storage in Azure Mobile Services .NET backend

When we released the .NET backend for Azure Mobile Services, we provided support to store data in Azure Table Storage and MongoDB databases (in addition to the SQL Azure storage which has been used since the first version of the service). The support for table storage, however, wasn’t great, in a sense that if the table had a large number of items: the table storage doesn’t support “skipping” items like SQL queries do, so the application couldn’t use paging to navigate through all the items in their tables. Instead of using skip / take, table storage supports paging via continuation links (exposed as HTTP Link headers), in which the response for a request which doesn’t include all items will have a link which the client should use to retrieve additional items. Those continuation links weren’t exposed in the client, however, so there was no way to consume large tables.

With the latest release of the .NET backend packages (version 1.0.405), and some updates to the client SDK (managed version 1.2.5 for now, others coming soon), we can now retrieve and follow the continuation links on the client so that we can do proper paging for table storage-backed data. Let’s walk through an example on how this can be done.

Retrieving data and paging

For the server side there’s no code changes we need to do – once you update the NuGet package for the Azure Storage extension the Azure Mobile Services .NET Backend, you should get the continuation links when the request would have more items than the ones returned. If you don’t have a service setup yet, you can check out the bonus material later in this post. Now that the service setup is out of the way, we can start with the client code. As I mentioned before, the full support is only available in the managed SDK for now, so let’s take a look at it. For this scenario, I’ll use a simple controller which returns list of people (a very limited contact list), with my class in the client defined as follows:

    public class Person
    {
        [JsonProperty("id")]
        public string Id { get; set; }

        [JsonProperty("name")]
        public string Name { get; set; }

        [JsonProperty("age")]
        public int Age { get; set; }
    }

By default, a read operation in a table controller will return up to 50 items. If we have more in our table storage, then the client will need to request more, by casting the result of the ToListAsync or ToEnumerableAsync methods to the IQueryResultEnumerable interface. The code below shows how to go through all elements in the table.

    public async Task CalculateAverageAge()
    {
        var client = new MobileServiceClient(AppUrl, AppKey);
        var table = client.GetTable();
        var sum = 0.0;
        var count = 0;
        var items = await table.Take(10).ToEnumerableAsync();
        while (items != null && items.Count() != 0)
        {
            count += items.Count();
            sum += Enumerable.Sum(items, i => i.Age);

            var queryResult = items as IQueryResultEnumerable;
            if (queryResult != null && queryResult.NextLink != null)
            {
                items = await table.ReadAsync(queryResult.NextLink);
            }
            else
            {
                items = null;
            }
        }

        return sum / count;
    }

If we’re using JSON tables (i.e., not using a type such as Person, and using the JToken family instead), we can request the response to be wrapped in an object by passing true to the wrapResult parameter of the ReadAsync method. If that’s the case, then the result is wrapped in an object with the following properties: results, which will contain the array with the actual results from the service; and nextLink, which will be present if the HTTP response had a Link header with the continuation token that should be passed to the ReadAsync method to retrieve the next set of entries from the table.

    public async Task CalculateAverageAge2()
    {
        var client = new MobileServiceClient(AppUrl, AppKey);
        var table = client.GetTable("person");
        var sum = 0.0;
        var count = 0;
        var response = await table.ReadAsync("$top=10", null, wrapResult: true);
        while (response != null)
        {
            var items = (JArray)response["results"];
            var nextLink = (string)response["nextLink"];

            count += items.Count();
            sum += Enumerable.Sum(items, i => (int)i["age"]);

            if (nextLink != null)
            {
                response = await table.ReadAsync(nextLink, null, true);
            }
            else
            {
                response = null;
            }
        }

        return sum / count;
    }

With the continuation links, the client can now traverse all the items from a table in Azure Storage by passing them to the read methods in the table objects.

Bonus material: setting up a service using Azure Storage tables

In case you haven’t yet set up a service with a table storage backed controller, here are the steps which you can follow to do so.

Setting up a storage account

If you haven’t done so yet, you’ll need to set up an Azure Storage account to use table storage. Before we can start saving and retrieving data in tables, we need a storage account in Azure for that. You can follow the instructions at the “How To Create a Storage Account” tutorial to create the account.

Once the account is setup, you’ll need to get the account name and access key to tell the mobile service how to talk to the storage account. To get the key, you can go to the quickstart or dashboard tab in the traditional portal and select the “Manage Access Keys” option:

Or in the new portal:

Once you have the account name and key, we’re ready to move to the mobile service.

Setting up the service

To use the Azure Storage to store data from your mobile service, you need to add the Azure Storage Extension for the Azure Mobile Services .NET Backend. Right-click your service project, select “Manage NuGet Packages…” and search for “mobileservices.backend”. Select the package mentioned above (and shown below) and click “Install”.

Once the package is installed, we can start creating the service. For this example, I’ll create a very simple table which stores my friends, and define a class Person as shown below. Notice that instead of using the EntityData base class (typically used in projects based on Entity Framework / SQL), I’m using the StorageData base class, which defines properties used by Azure Table Storage such as partition / row key, among others.

    public class Person : StorageData
    {
        public string Name { get; set; }

        public int Age { get; set; }
    }

Next, let’s add the connection string for the storage account to the service. We can either set it in the web.config file (easy to do, good for development, can be used when debugging locally, but not as secure) or in the connection strings section of the “configure” tab in the portal (more secure as people with access to the source code won’t be able to see it, but only works when the service is deployed to Azure). For simplicity sake, I’ll make the change in my Web.config:

Finally, we can create the controller class which will expose the data from the table in the storage account as table in the mobile service. The implementation is almost identical to the one which is generated in the download link from the portal quickstart page, or in the Visual Studio template for an Entity Framework-backed data, with the following exceptions:

• The domain manager is of type StorageDomainManager, which maps between the mobile service table and the backing data store (Azure storage)
• Since tables in Azure storage don’t support fully querying capabilities as do tables in a SQL database, returning an IQueryable is not supported. But the base type TableController has one method which can be used with storage tables: QueryAsync (for querying multiple items) and LookupAsync (for querying single items)

Below is the full code for the controller class in this example:

    public class PersonController : TableController
    {
        protected override void Initialize(HttpControllerContext controllerContext)
        {
            base.Initialize(controllerContext);
            var tableName = controllerContext.ControllerDescriptor.ControllerName.ToLowerInvariant();
            var connStringName = "My_StorageConnectionString";
            DomainManager = new StorageDomainManager(connStringName, tableName, Request, Services);
        }

        // GET tables/Person
        public Task> GetAllPerson(ODataQueryOptions queryOptions)
        {
            return base.QueryAsync(queryOptions); 
        }

        // GET tables/Person/48D68C86-6EA6-4C25-AA33-223FC9A27959
        public Task> GetPerson(string id)
        {
            return base.LookupAsync(id);
        }

        // PATCH tables/Person/48D68C86-6EA6-4C25-AA33-223FC9A27959
        public Task PatchPerson(string id, Delta patch)
        {
             return UpdateAsync(id, patch);
        }

        // POST tables/Person/48D68C86-6EA6-4C25-AA33-223FC9A27959
        public async Task PostPerson(Person item)
        {
            Person current = await InsertAsync(item);
            return CreatedAtRoute("Tables", new { id = current.Id }, current);
        }

        // DELETE tables/Person/48D68C86-6EA6-4C25-AA33-223FC9A27959
        public Task DeletePerson(string id)
        {
             return DeleteAsync(id);
        }
    }

One thing that must be noted is that the lookup / delete / update methods take an id of type string – as is part of the contract with the client SDKs. But the id of items stored in tables is made up of two parts: partition and row keys. To merge these two worlds, we define a mapping that the id is of the form , (where the keys can be wrapped in single quotes if necessary). For example, if we run the service with the code shown above, we can insert an item with a request like the following:

POST /tables/person HTTP/1.1
X-ZUMO-APPLICATION: 
Content-Type: application/json; charset=utf-8
Host: mobile-service-name.azure-mobile.net
Content-Length: 75

{
  "id": "partition,row1082",
  "name": "dibika lyzufu",
  "age": 64
}

Wrapping up

That’s it. The support for retrieving continuation links is currently available in the managed client platforms, but it should be coming soon to other platforms as well. If you want to get the code used in this post, you can find it at our mobile samples repository, under the NetBackendWithTableStorage directory.

As usual, please send us feedback either as comments in this post, via twitter @AzureMobile or in our MSDN forums.