Supporting complex types in Azure Mobile Services clients – implementing 1:n table relationships

[The object model shown in this post for the client-side is mostly out-of-date; check the updated post at the more up-to-date version]

In this series about complex types, we’ll take where we left off the previous post: we have now added support in the client for complex types, and used an implementation of the IDataMemberJsonConverter interface to teach the managed client how to serialize / deserialize it into / from a JSON payload. On the server side, we “stringified” the complex data coming from the client, and stored into a string column in our database. That worked well, and was simple enough, but there are some drawbacks to this approach: we‘re possibly storing redundant information (a side effect of the denormalization), and we can’t really query the associated data, since its data is compacted into a string format.

Let’s get to an example. I like swimming, and I’m a data freak, so I note of my swimming sessions, including how many sets and distance / style for each of them. I’ve seen people who do similar things for other activities as well, such as running (tempo / interval training), biking, etc. I like to have that data to find out things such as what’s my PB for certain distances (e.g., 1000 yds. – the pool I frequent has a 25 yards length, not 25 meters, go figure), how much I swim in a certain style in a given month, and so on. Now, if I were to store the sets information as a string in my swim session table, I wouldn’t be able to query based on that. So the solution I used in this case was not to use only one table for the swim sessions and the sets, but two tables with a 1:n relationship in them.

Notice that Azure Mobile Services tables don’t have the concept of relationships. Currently they’re stored in SQL Azure databases, where you can in theory go to the database management portal, and create the relationship yourself. But nothing prevents in the future the team to start offering other storage backends, such as Azure Table Storage, MongoDB or something else. When I say that I have  a 1:n relationship is that logically the application implements that relationship itself.

There are two places where we can define this relationship: the client and the server. Let’s see them both.

Relationship on the client

On the client side, we’d have two classes, each with its own identifier. The set class also has one property with the “foreign key” for the other table, so that when we query them, we can correlate between instances of the two classes. Also, since we’ll be storing the sets ourselves (on the client side), we don’t want them to be sent within the SwimSession class, so we can use the [IgnoreDataMember] attribute and the client will not send it over the wire.

  1. public class SwimSession
  2. {
  3.     public int Id { get; set; }
  5.     public DateTime Date { get; set; }
  7.     [DataMemberJsonConverter(ConverterType = typeof(TimeSpanConverter))]
  8.     public TimeSpan Duration { get; set; }
  10.     [IgnoreDataMember]
  11.     public List<SwimSet> Sets { get; set; }
  12. }
  14. public class SwimSet
  15. {
  16.     public int Id { get; set; }
  18.     public int SwimSessionId { get; set; }
  20.     [DataMemberJsonConverter(ConverterType = typeof(EnumConverter<Style>))]
  21.     public Style Style { get; set; }
  23.     publicint Distance { get; set; }
  25.     [DataMemberJsonConverter(ConverterType = typeof(TimeSpanConverter))]
  26.     public TimeSpan Duration { get; set; }
  27. }

The converters listed in the code above are simple – the TimeSpanConverter is the same one used in the previous post, and the EnumConverter<T> is one class which I have for dealing with enum types, shown below.

  1. public class EnumConverter<TEnum> : IDataMemberJsonConverter
  2. {
  3.     publicobject ConvertFromJson(IJsonValue value)
  4.     {
  5.         TEnum result = default(TEnum);
  6.         if (value != null && value.ValueType == JsonValueType.String)
  7.         {
  8.             result = (TEnum)Enum.Parse(typeof(TEnum), value.GetString());
  9.         }
  11.         return result;
  12.     }
  14.     public IJsonValue ConvertToJson(object instance)
  15.     {
  16.         returnJsonValue.CreateStringValue(instance.ToString());
  17.     }
  18. }

Now, on the client, if we get a session which we want to store in the Azure Mobile Services database, we’d first insert the session object, then iterate through the sets in that session and insert them one by one, but first setting the foreign key (the session id) to the appropriate property.

  1. private async Task InsertSession(MobileServiceClient mobileServiceClient, SwimSession session)
  2. {
  3.     var sessionTable = mobileServiceClient.GetTable<SwimSession>();
  4.     await sessionTable.InsertAsync(session);
  5.     var sessionId = session.Id;
  7.     var setsTable = mobileServiceClient.GetTable<SwimSet>();
  8.     foreach (var set in session.Sets)
  9.     {
  10.         set.SwimSessionId = sessionId;
  11.         await setsTable.InsertAsync(set);
  12.     }
  13. }

So that’s actually not so bad… Let’s move on to retrieval. This time we first query for the sessions using whatever filter we want (in this case I’m use the date), and for each session retrieved, we retrieve their associated sets.

  1. private async Task<List<SwimSession>> RetrieveSessions(MobileServiceClient mobileServiceClient, DateTime sessionDate)
  2. {
  3.     var sessionTable = mobileServiceClient.GetTable<SwimSession>();
  4.     var begin = sessionDate.Date;
  5.     var end = begin.AddDays(1);
  6.     var query = sessionTable.Where(s => s.Date >= begin && s.Date < end);
  7.     var sessions = await query.ToListAsync();
  9.     // Now retrieve the sets
  10.     var setsTable = mobileServiceClient.GetTable<SwimSet>();
  11.     foreach (var session in sessions)
  12.     {
  13.         var sets = await setsTable.Where(set => set.SwimSessionId == session.Id).ToListAsync();
  14.         session.Sets = sets;
  15.     }
  17.     return sessions;
  18. }

So far so good. What about delete? Same thing – delete sets (which we can do easily since each set has its own id), delete session. Update is a little trickier. The sets are an ordered list, and when we’re inserting them, they’ll remain in order based on their ids. But if we want to insert a set in the middle of a session, once we retrieve that it would become out of order. There are a couple of ways to solve this:

  • Instead of just adding a new element, the code would first delete all “old” sets, then add the new ones back. That would make the sets ordered by virtue of their ids
  • Add an order property to the SwimSet class, and when querying, use an orderby clause for that property

Both have pros and cons. In the first one, we don’t need to change our model, but an update now involves sending 2x as many requests as as before (“n” delete calls, followed by “n + 1” insert calls). In the second we can avoid the high number of calls, but we’re adding some property to the model which is not natural, adding an unnecessary coupling and a duplication of information (the sets are already stored in a list, their order information already exists in the list).

So this is one way of implementing the 1:n relationship on the client. The main advantage of this is that nothing needs to be done in the server, and if you don’t want to get involved with JavaScript / Node.js code, then it’s simpler. There are, however, some problems with this approach. All of the code I showed above assumes that everything works fine. But what if some operation fails? For example, during insert, if adding the session itself fails then we can just bubble the error up; but if the error happens after the session was inserted, and while one of the sets was being inserted, we need somehow to “rollback” the insertion of the session and any previous sets. Not impossible, but more work that needs to be done. Another big issue with the client approach is that it takes a lot of networking calls for each operation. Depending on the bandwidth available for the client (which may not be much, since we’re talking about devices on 3G or slower networks), this can make the app slower. One could try to send all the set operations in parallel, but that would make error recover harder. Let’s see how the other option compares then.

Relationship on the server

The main advantage of implementing the relationship on the server is to reduce the number of calls made by the client. It’s not only a matter of performance – after all, as we’ll see shortly, the server will need to make the calls itself – but since the networking connection between the Azure Mobile Service and the backend database is (likely) more reliable than the connection between the mobile client and Azure (especially if the mobile service and the database are located in the same data center). That reduces the risk of the errors in the middle of the insert operations, and depending on the application, can be ignored. The main drawback is that we need to add more logic on the server side to deal with the data. That’s what I plan on showing in this part.

First of all, we can take all of the DB-specific properties from the SwimSet class, which makes for a much cleaner model. We also need to decorate the list of sets with our DataMemberJsonConverter attribute again. Unlike other examples, this time I used the DataContractJsonSerializer to convert between the type and the System.Json classes – not as efficient as handwriting JSON code, but simpler to implement – just to show yet another way of accomplishing the same thing.

  1. public class SwimSession
  2. {
  3.     public int Id { get; set; }
  5.     public DateTime Date { get; set; }
  7.     [DataMemberJsonConverter(ConverterType = typeof(TimeSpanConverter))]
  8.     public TimeSpan Duration { get; set; }
  10.     [DataMemberJsonConverter(ConverterType = typeof(SwimSetListConverter))]
  11.     public List<SwimSet> Sets { get; set; }
  12. }
  14. public class SwimSet
  15. {
  16.     publicStyle Style { get; set; }
  17.     publicint Distance { get; set; }
  18.     publicTimeSpan Duration { get; set; }
  19. }
  21. public enum Style
  22. {
  23.     Butterfly,
  24.     Backstroke,
  25.     Breaststroke,
  26.     Freestyle,
  27.     Medley,
  28. }
  30. public class SwimSetListConverter : IDataMemberJsonConverter
  31. {
  32.     public object ConvertFromJson(IJsonValue value)
  33.     {
  34.         List<SwimSet> result = null;
  35.         if (value != null)
  36.         {
  37.             DataContractJsonSerializer dcjs = newDataContractJsonSerializer(typeof(List<SwimSet>));
  38.             MemoryStream ms = newMemoryStream(Encoding.UTF8.GetBytes(value.Stringify()));
  39.             result = (List<SwimSet>)dcjs.ReadObject(ms);
  40.         }
  42.         return result;
  43.     }
  45.     public IJsonValue ConvertToJson(object instance)
  46.     {
  47.         DataContractJsonSerializer dcjs = newDataContractJsonSerializer(typeof(List<SwimSet>));
  48.         MemoryStream ms = newMemoryStream();
  49.         dcjs.WriteObject(ms, instance);
  50.         byte[] bytes = ms.ToArray();
  51.         returnJsonValue.Parse(Encoding.UTF8.GetString(bytes, 0, bytes.Length));
  52.     }
  53. }

Now the client is ready, we need to move on to the server. Just like what we did for the client side alternative, we have to do one insertion (for the session), followed by many insertions on the sets table. And this is where I think the managed code shines (especially with the async / await keywords). Running a simple loop of asynchronous operations in JavaScript is harder – what I have here is one possible implementation, others may exist as well (such as the one shown in Josh Twist’s post about complex objects). I decided to go with a sequential insert (instead of a parallel one as it was the case in Josh’s post) to mimic what I did on the client section, each one has its pros and cons. Concurrent inserts is usually faster, but it requires a separate variable for storing the result of each insertion – if there is an error after all the operations are done, we need to go through that list and revert the successful ones; in the sequential mode we just need to go back using the set index (another approach, which would work for both, would be to use mssql object to run an ‘DELETE FROM SwimSet WHERE SwimSessionId=?’ query, but that would tie the script to the SQL database – usually not a big issue, but something to be considered as well).

  1. function insert(item, user, request) {
  2.     var sets;
  3.     if (item.Sets) {
  4.         sets = item.Sets;
  5.         delete item.Sets;
  6.     }
  8.     request.execute({
  9.         success: function () {
  10.             item.Sets = [];
  11.             if (sets) {
  12.                 var i = 0;
  13.                 var insertNext = function () {
  14.                     if (i < sets.length) {
  15.                         var set = sets[i];
  16.                         set.SwimSessionId =;
  17.                         set.SetOrder = i;
  18.                         tables.getTable('SwimSet').insert(set, {
  19.                             success: function () {
  20.                                 item.Sets.push(set);
  21.                                 i++;
  22.                                 insertNext();
  23.                             }
  24.                         });
  25.                     } else {
  26.                         request.respond();
  27.                     }
  28.                 };
  30.                 insertNext();
  31.             }
  32.         }
  33.     });
  34. }

Notice that we added two new properties to the set being inserted: the foreign key (SwimSessionId) and an order property (SetOrder) – and we could do that without “polluting” the model on the client with information related to the database.

Now for reading, we run a similar code in the server: first, retrieve the session object(s), by calling request.execute, and once the results for that are available, query the sets table to pull the sets for the associated data. Notice that we could simply assign the results to the session.Sets property directly, but since the model in the client doesn’t have properties such as id, SwimSessionId or SetOrder, I find it best not to reveal that kind of information to the callers (if someone uses the REST interface directly). It wouldn’t hurt, though, since extra properties would simply be ignored in the deserialization done in the data member JSON converter for the set list.

  1. function read(query, user, request) {
  2.     request.execute({
  3.         success: function (results) {
  4.             if (results.length === 0) {
  5.                 request.respond();
  6.                 return;
  7.             }
  9.             var setsTable = tables.getTable('SwimSet');
  10.             var queryResultCount = 0;
  11.             results.forEach(function (session) {
  12.                 setsTable.where({ SwimSessionId: })
  13.                          .orderBy('SetOrder')
  14.                          .read({
  15.                              success: function (setsForSession) {
  16.                                  session.Sets = [];
  17.                                  for (var i = 0; i < setsForSession.length; i++) {
  18.                                      var setForSession = setsForSession[i];
  19.                                      session.Sets.push({
  20.                                          Style: setForSession.Style,
  21.                                          Distance: setForSession.Distance,
  22.                                          Duration: setForSession.Duration
  23.                                      });
  24.                                  }
  26.                                  queryResultCount++;
  27.                                  if (queryResultCount === results.length) {
  28.                                      request.respond();
  29.                                  }
  30.                              }
  31.                          });
  32.             });
  33.         }
  34.     });
  35. }

Talking about the REST API, so far we’re only using the SwimSet table indirectly, via the scripts for the SwimSessoin table. If we continue doing so, we can secure that table, by going to the permissions tab in the portal and choosing “Only Scripts and Admins” for their permissions. Once we start adding features such as searching for searching for specific sets, then we can change the permissions back, but it’s always good to only allow users of a service what they need.


Ok, so insert and read are covered. Delete is trivial: first delete the associated sets, then delete the session, and this can be done by querying the sets table and then deleting them one by one, or simply using the the mysql object directly (it makes it a lot simpler in this case), as shown below.

  1. function del(id, user, request) {
  2.     mssql.query('DELETE FROM SwimSet WHERE SwimSessionId = ?', [id], {
  3.         success: function () {
  4.             request.execute();
  5.         }
  6.     });
  7. }

Update is trickier, since we don’t have the ids of the incoming sets, so the easiest way is to first delete the associated sets, then create them again (as shown below). Another alternative would be to retrieve the sets from the database, then compare them with the data received over the wire, and only update the differences. As usual, choose what the applications needs (no more complexity than necessary is always a good practice to abide).

  1. function update(item, user, request) {
  2.     var sets;
  3.     if (item.Sets) {
  4.         sets = item.Sets;
  5.         delete item.Sets;
  6.     }
  8.     request.execute({
  9.         success: function () {
  10.             mssql.query('DELETE FROM SwimSet WHERE id = ?', [], {
  11.                 success: function () {
  12.                     if (sets) {
  13.                         var setsTable = tables.getTable('SwimSet');
  14.                         var count = 0;
  15.                         item.Sets = [];
  16.                         sets.forEach(function (set) {
  17.                             var index = sets.indexOf(set);
  18.                             item.Sets[index] = set;
  19.                             setsTable.insert(set, {
  20.                                 success: function () {
  21.                                     count++;
  22.                                     if (count === sets.length) {
  23.                                         request.respond();
  24.                                     }
  25.                                 }
  26.                             });
  27.                         });
  28.                     }
  29.                 }
  30.             });
  31.         }
  32.     });
  33. }

And that’s it. In order to make it into a more robust production app, we’d need to add error handling as well, but that would make the scripts grow longer than I’d like to have in this blog post. Hopefully this will help you if you ever need to implement a 1:n relationship on Azure Mobile Services.

Coming up

As requested in a comment in the original blog post of this series, I’ll write a little about integrating authentication in scripts.

Comments (1)
  1. Following up on a comment made in one of my posts, this one will talk about some ways to ensure that

Comments are closed.

Skip to main content