Menu

No, I’m not going to fix the relationship with your significant other. One of the common questions I get asked is about relationships between tables. The basic advice is easy – don’t do it. The actual relationship advice is a little more complex than that and today I’m going to cover what is involved in setting up table relationships, what to do and what to avoid.

The Node.js / Easy Tables Version

This one is easy. The Node.js Server SDK for Azure Mobile Apps does not support relationships in any way, shape or form. If you want to use relationships, use an ASP.NET backend.

The ASP.NET Version

There are two relationships that are supported

A one-way 1:1 relationship

A one-way 1:Many relationship

One way relationships are where a table has a foreign key for another table, but the reverse is not true. Do not do the normal “two-way” relationships as you will run into circular serialization problems.

These are both configured with Entity Framework using foreign keys. You need to ensure at you set up your data transfer objects first. Let’s take an example. Let’s say I have my normal TodoItem table, but I want to add a tag to each record. The data transfer objects are configured thus:

The ForeignKey annotation is the key to this – it sets up the linkage between the two tables. Note that the TagId references the Id of the Tag table which is, of course, a GUID in string form when you are in an Azure Mobile Apps DTO. I still need to add a table controller for the Tag entity, and add a DbSet to the MobileServiceContext – so now I have two controllers.

The Client Side

On the client side, I need to be able to add a Tag to the TodoItem. The Tag must exist before the TodoItem exists. Let’s take a look at one of the TodoItem records when I request it through Postman – here is the response:

My first record has a linkage to a specific tag – this is shown via the tagId being filled in. My second record does not have a linkage to a specific tag. On the client, you will need to do the join of the tables yourself. The client does not know about relationships. Each table is independent. This means that your model (assuming a C#/.NET client, like UWP or Xamarin) should look like this:

In general, things will just work. However, that’s because you will naturally create a Tag before using it. You must ensure that you insert the tag before inserting the Todoitem that references it. Again, you do the join in code – don’t expect a Tag item to magically be populated in your TodoItem as a linked reference – it won’t happen.

1:Many Relationships

You can also do one-way 1:Many relationships with the ASP.NET Server SDK. An example of a one-way 1:Many relationship is that my TodoItem may have multiple tags. As long as I am not doing the reverse (give me all todo items that use this tag, which would indicate a two-way relationship), you should be ok. In this case, the Tags would be an array. Let’s take a look at the new models on the server side:

Note that there is no tag. OData requires you to send the $expand parameter to expand the collection – something like https://localhost:14683/tables/TodoItem?$expand=Tags. The Azure Mobile Apps Client SDK (currently) doesn’t support $expand, so I have to do something on the server to support this. The first thing is to provide a new annotation called the [ExpandProperty]:

You can specify this multiple times so that you can expand multiple properties. You generally only need to add this to the “Read” methods – GetAll and GetSingle. Now, when I do the same Postman query as before, I get the following:

The tags will be included in the todoitem. The data is denormalized. Say you have a tag called “Urgent” and it is used six times – there will be six copies of the “Urgent” tag – not one with references. While this is not a problem for most applications, if your data is large you actually want to deal with this a different way. This may include using a TagId list instead and then linking the models when you do a refresh, for example.

Wrap Up

I hope you take away from this a few points:

Relationships is strictly an ASP.NET function.

Relationships are hard and you must work at them to ensure the proper outcome.

1:1 and 1:Many relationships are doable if they are one-way (i.e. table1 refers to table2, but the reverse is not true)

You have to deal with expansion in the 1:Many case, and data is denormalized (which may not be desirable)

Avoid Many:Many relationships

You really need to understand how Entity Framework implements these relationships

The final thing to remember is that the client does not know about relationships. That is a SQL concept and SQL is spoken only on the server. Finally – my advice is to try and avoid relationships on your mobile backend – use projections mechanisms (such as SQL Views and/or automatic expansion with the ExpandProperty attribute) to denormalize the data prior to it getting to the client.

You can see the 1:Many code for the backend in the relationship-backend solution on my GitHub Repository.

6 thoughts

Relationships are not just a SQL concept they are a real life concept for nearly every type on entity. It’s what makes a relational db better than just a bunch of lists. Why not make the backend return true OData so we can do this stuff without limitations and workarounds?

I agree with you. I wanted to document what was possible with the current code, not what we could do. We can’t return true OData because of the additional work we do with offline sync – indeed, many of the problems with offline sync are caused by the OData specification. Rest assured – this is very much a top-of-mind thing going forward.