Home > View Post

Going deep with Mobile Services data

My friend Patrick asked on twitter if it was possible to expose data relationships in Mobile Services and since this is a common question I thought it might be worthy of a blog post. As you no doubt know, in Windows Azure Mobile Services your data is your own; that is – it’s your SQL database and there’s nothing abnormal about the data schema within – it’s a canonical representation of the data your client inserted via our JSON api. Which means it’s easy to use for reporting, analysis and you can easily back up your data – it’s just an Azure SQL database.

A core theme held throughout the design of Mobile Services was simplicity with enablement. That is, we wanted to make backend development easy for all kinds of developers, but avoid closing the gate on scenarios wherever possible.

In the public preview of Mobile Services we peeled back the client to keep it as thin and easy to use as possible and I think it’s a delight to use.

Of course, one of the great things about SQL Server is that it’s a relational database and, sometimes, you want to access those relationships at the client. In this post, I’ll show one of the ways you can do this today. In this case – using scripts.

Imagine we have two tables – Posts and Comments. You get the idea - Comments are related to Posts via a PostId property on the Comment. Here’s the two C# classes that would go into my client application (you don’t need a class/type in JS)

[DataTable(Name = "posts")]
public class Post
    public int Id { get; set; }

[DataMember(Name="text")] public string Text { get; set; } }

[DataTable(Name = "comments")] public class Comment { public int Id { get; set; }

[DataMember(Name="postId")] public int PostId { get; set; }

[DataMember(Name="text")] public string Text { get; set; } }

Straightforward enough. Now imagine, we’d like to retrieve a post’s comments whenever we read the posts table. One way to do this is inside the read script for our Posts table:


And here’s what my script looks like. The code comments should help you work out what’s going on:


var commentsTable = tables.getTable('comments');

function read(query, user, request) {
    // run the query for posts
        success: function(results) {
            // grab all the post ids into 
// an array using JS’ map
var postIds = results.map(function(r) {
return r.id;
}); // find all comments that match
//these post ids
commentsTable.where(function(postIds) { return this.postId in postIds; }, postIds).read({ success: function(comments) { // attach the comments to
// each post
results.forEach(function (r) { // find only the comments
// that match this post
// id using JS’ filter
console.log(r); r.comments = comments.filter(
function(c) { if (c.postId === r.id) { return c; } }); }); // now the results have
// been augmented,
// return to the client
request.respond(); } }); } }); }

This is a fun example because I get to stretch my scripting legs. And we’re done (note, you could also just use T-SQL to perform a join in the database). We’ll now be sending JSON over the wire that has a comments array for each post!

C# (Managed) Client

The remaining challenge is to have the C# client deserialize that comments array. My friend Carlos already posted an awesome article ‘Supporting arbitrary types in Azure Mobile Services managed client – simple types’ and hints that his next post will talk about complex types. Therefore this is a sneak preview of some of the goodness to come in his next post.

We add a Comments property to the Post class and attribute it to identify a converter:

typeof(CommentListConverter))] public List<Comment> Comments { get; set; }

Now we need an IDataMemberJsonConverter called CommentListConverter:

public class CommentListConverter : 
IDataMemberJsonConverter { public object ConvertFromJson(IJsonValue value) { return value.GetArray().Select(c => MobileServiceTableSerializer.
Deserialize<Comment>(c)).ToList(); }

public IJsonValue ConvertToJson(object instance) { // don't reverse the conversion. We don't // want to push the collection back up to // the mobile service return null; } }

And you should be golden. You’ll now have a populated List<Comment> for all posts you read from the Mobile Service (empty if there were no comments). Note that we don’t want to reverse the conversion as you don’t want to push the comments to the server on insert or update – a null works fine here.

JS (WinJS) client

In the JS world we have slightly less work to do as the object will automatically have the comments array property (JavaScript rocks at handling JSON – no surprise there). However, since you don’t want to push the comments back to the service, you should remember to delete that property before calling update, e.g.:

function updatePost(post) {
    delete post.comments;

Josh Post By Josh Twist
6:44 PM
02 Sep 2012

» Next Post: Understanding the pipeline (and sending complex objects into Mobile Services)
« Previous Post: Introducing Windows Azure Mobile Services

Comments are closed for this post.

Posted by Matt @ 03 Sep 2012 6:16 PM
Very informative!

© 2005 - 2022 Josh Twist - All Rights Reserved.