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.
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.
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.
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.
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.
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:
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).
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.
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.
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.
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).
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.
As requested in a comment in the original blog post of this series, I’ll write a little about integrating authentication in scripts.