Extending Windows Azure Mobile Services queries to include relational data and optional metadata
The last few weeks I’ve worked a lot with Xamarin, doing Android, iOS and Windows Phone apps. By utilizing the Azure Mobile Services you can get started really quickly and get a proper and stable backend environment running (in Azure, obviously).
While this is all fun and dandy, there’s a problem that presented itself rather soon. Namely that the queries by default wasn’t extended to include metadata or relational data.
Background
Below is a sample of what my entities look like in Azure Mobile Services (as designed with the code-first approach) and their representation in the App project (Xamarin.Android project).
Essentially, this is a PoC application I’ve created, storing all your todo data in the Azure Mobile Services backend of your project – making it accessible from any device, anytime, and allows you to share lists with friends/family etc. But more importantly, the following sections describe the data definitions of my app.
The App Service (Azure Mobile Services) entity definitions
File: TodoItem.cs:
public class TodoItem : EntityData
{
[ForeignKey("TodoListId")]
public TodoList TodoList{ get; set; }
public String TodoListId { get; set; }
public string Text { get; set; }
public bool Complete { get; set; }
}
File TodoList.cs:
public class TodoList : EntityData
{
[ForeignKey("UserId")]
public User User { get; set; }
public String UserId { get; set; }
public string ListName { get; set; }
public bool Archived { get; set; }
public ICollection<TodoItem> TodoItems { get; set; }
public ICollection<Invitation> Invitations { get; set; }
}
File User.cs:
public class User : EntityData
{
public string AccountId { get; set; } // Should represent the signed-in account id.
public string DisplayName { get; set; }
public string Email { get; set; }
public DateTime? Joined { get; set; }
public ICollection TodoLists { get; set; }
}
As you can see here, we’ve defined a relationship between the various entities. One User can have many TodoLists. One TodoList can have many TodoItems.
The App: (MyProject.Android) entitiy representations
File TodoItem.cs:
public class TodoItem
{
public string Id { get; set; }
[JsonProperty(PropertyName = "text")]
public string Text { get; set; }
[JsonProperty(PropertyName = "complete")]
public bool Complete { get; set; }
[JsonProperty(PropertyName="todoListId")]
public string TodoListId { get; set; }
}
File TodoList.cs:
public class TodoList
{
public string Id { get; set; }
[JsonProperty(PropertyName = "archived")]
public bool Archived { get; set; }
[JsonProperty(PropertyName = "listName")]
public string ListName { get; set; }
[JsonProperty(PropertyName = "userId")]
public string UserId { get; set; }
}
File User.cs:
public class User
{
public string Id { get; set; }
[JsonProperty(PropertyName = "joined")]
public DateTime? Joined { get; set; }
[JsonProperty(PropertyName = "email")]
public string Email { get; set; }
[JsonProperty(PropertyName = "displayName")]
public string DisplayName { get; set; }
[JsonProperty(PropertyName = "accountId")]
public string AccountId{ get; set; }
}
As seen here, this is just a way to map your entities into the App project with their respective JSON attributes.
Problem 1: Relational queries doesn’t work unless you extend the query
The first problem I bumped into with this scenario is that relational queries didn’t work. Meaning that the following code snippet from my Xamarin.Android app failed miserably in the TodoItemActivity class, where I wrote this code:
List list = await toDoTable.Where (item => item.TodoListId ListIdFromIntent && item.Complete false).ToListAsync();
So what happened was that the TodoItem table was trying to request information from the related table TodoLists by executing the "item.TodoListId" part of the code. Unfortunately, it appears that the REST query being sent to the server doesn’t include the $expand=table part.
Let’s figure out how this works.
Investigating the REST queries
Normal REST GET query:
https://[yourAppUrl].azure-mobile.net/tables/todolist
Renders the following result, without any related data:
{
"id": "2210b1ccc689441f96aa018c7e76747b",
"archived": false,
"listName": "Fantastic Four",
"userId": "Twitter:249311152"
}
The problem here is that it doesn’t by default include any relational data, nor any optional metadata. Where did my relational data go? Let’s figure that out.
REST query with the $expand parameter
https://[yourAppUrl].azure-mobile.net/tables/todolist?$expand=user
Renders the following results, including the related data:
{
"id": "2210b1cbc689441f96aa028c7e767474",
"archived": false,
"listName": "Fantastic Four",
"userId": "Twitter:249314152",
"user": {
"accountId": "Twitter:249314152",
"displayName": "Sample User from Twitter",
"email": "sample-user-awesomeness@tozit.com",
"joined": "2014-10-21T18:38:35.213Z",
"id": "Twitter:249314152",
"version": "AAAAAAAACk4=",
"createdAt": "2014-10-21T18:38:35.706Z",
"updatedAt": "2014-10-21T18:38:35.722Z",
"deleted": false
}
}
As you see, if we include the $expand operator in the REST query, the relational data is returned.
So, how do we make sure our app understands that it needs to send the expand-operator? That’s the next thing we need to figure out, tag along.
Extending the query using a System.Net.Http.DelegatingHandler
When using Azure Mobile Services, we need to instantiate a new MobileServiceClient object. I do that like this:
var MobileSvcClient = new MobileServiceClient(
Constants.ApplicationURL,
Constants.ApplicationKey,
new QueryExtensionHandler()
);
The one thing that differs my code with the default samples and most of the Mobile Services samples I’ve seen is that I’ve added a new instance of my own class which is inheriting from DelegatingHandler.
The MobileServiceClient class can take additional DelegatingHandler instances in order to modify its behavior.
My class to extend the request looks like this:
public class QueryExtensionHandler : DelegatingHandler
{
protected override async TaskSendAsync(HttpRequestMessage request, CancellationToken cancellationToken)
{
// If it's one of our Get requests that need extending.
if (request.Method == HttpMethod.Get)
{
if (request.RequestUri.PathAndQuery.Contains("/tables/todolist"))
{
var uriBuilder = new UriBuilder(request.RequestUri);
var uriBuilderQuery = uriBuilder.Query;
if (!uriBuilderQuery.Contains("$expand"))
{
if (string.IsNullOrEmpty(uriBuilderQuery))
{
uriBuilderQuery = string.Empty;
}
else
{
uriBuilderQuery = uriBuilderQuery + "&";
}
uriBuilderQuery = uriBuilderQuery + "$expand=user";
uriBuilder.Query = uriBuilderQuery.TrimStart('?');
request.RequestUri = uriBuilder.Uri;
}
}
}
var result = await base.SendAsync(request, cancellationToken);
return result;
}
}
Kudos to Pragna Gopa, from where I could get inspiration for the DelegatingHandler class.
Now, your app should return the user data as well, and the LINQ-query we saw in the beginning of this post would work.
Problem 2: I want to order by created date
In a traditional data table I would most likely be able to order by the ID column, if it were an auto-incrementing number. However, the implementation I’ve done is that each user get’s a unique string (the ID of their social network account) instead of an int as an identifier. This makes more sense in a lot of aspects for my application.
Investigating the JSON response from the REST query
The problem I was presented with was that I couldn’t do a LINQ query to order by. There simply wasn’t any field from the JSON result that I could order by .
Default query, without any query strings
https://[yourAppUrl].azure-mobile.net/tables/todoitem
Gave the following JSON response, without any created date:
{
"id": "a45a312ed19047b786e8f429247049d8",
"complete": false,
"text": "New todo item",
"todoListId": "2210a1ccc689441f96aa018c7e767474"
}
Extending the query to include the created date
While we want to keep data transfer to a minimum, we may also want to specify additional metadata to retrieve that isn’t part of the default request. We can do this by extending the __systemProperties query string.
https://[yourAppUrl].azure-mobile.net/tables/todoitem?__systemProperties=createdAt
Gave the following JSON response, including the created date:
{
"__createdAt": "2014-10-21T18:44:41.405Z",
"id": "a45a302ed19047b786e8f429247049d8",
"complete": false,
"text": "New todo item",
"todoListId": "2210b1ccc689441f96aa018c7e76747b"
}
As you see, the Azure Mobile Services DOES include the data, but you need to explicitly ask for it.
The solution, again, is to use the DelegatingHandler.
Extending the query using a System.Net.Http.DelegatingHandler
By using the same approach as before, but just slightly modifying the parameters and values, we can make sure our request is modified to include the created date:
public class QueryExtensionHandler : DelegatingHandler
{
protected override async Task<HttpResponseMessage>SendAsync(HttpRequestMessage request, CancellationToken cancellationToken)
{
// Append the query with the CreatedAt system column on every request. We want this data.
var pathAndQuery = request.RequestUri.PathAndQuery;
bool requestToTodoItemsOrListTable = (pathAndQuery.StartsWith("/tables/todoItem") || pathAndQuery.StartsWith("/tables/todoList"));
if (requestToTodoItemsOrListTable)
{
UriBuilder createdAtBuilder = new UriBuilder(request.RequestUri);
string createdAtQuery = createdAtBuilder.Query;
if (!createdAtQuery.Contains("__systemProperties=createdAt"))
{
if (string.IsNullOrEmpty(createdAtQuery))
{
createdAtQuery = string.Empty;
}
else
{
createdAtQuery = createdAtQuery + "&";
}
createdAtQuery = createdAtQuery + "__systemProperties=createdAt";
createdAtBuilder.Query = createdAtQuery.TrimStart('?');
request.RequestUri = createdAtBuilder.Uri;
}
}
var result = await base.SendAsync(request, cancellationToken);
return result;
}
}
Again, kudos to Pragna Ropa for the code snippet for the DelegatingHandler – it has made my days easier in extending my Azure Mobile Services-hosted apps.
With this post I hope someone else can benefit from it during their journey into the world of mobile :-)
Enjoy.