Updating the Data Model with Azure Mobile Service

Whilst template and samples are great, often one of the hardest things is adapting the code to your particular scenario. One such example is with Azure Mobile Service when you decide that you want to change your data model for the first time. Firstly, the initial template that you download and publish to get an initial service up and running will create a ToDoItems table. Unless you happen to be building a ToDo task system then it is unlikely that you’ll want this table. So, how do we get rid of this table out of our Azure Mobile Service (and of course the SQL Server database that backs it)?

At this point a word of caution – this table was created by the Mobile Service, so don’t go messing with the table directly. For example you could use SQL Management Studio to connect to the SQL Azure instance and just delete the table but I think you’ll find that you run into some issues when you next run or attempt to deploy new versions of your Mobile Service. In fact, the Mobile Service template already provides a facility to do that. In fact, with any change to the Entity Framework data model the entire database structure will be dropped and recreated. This makes it really easy to remove the ToDoItems table, simply by commenting out the following line in the RealEstateInspectorContext:

//public DbSet<TodoItem> TodoItems { get; set; }

All I need to do now is publish and invoke the Mobile Service – I can do this by either running the Windows/Windows Phone applications wired up in the previous post or I can invoke it using Fiddler, as I did in an earlier post. It’s important to invoke the Mobile Service (even though it will fail with a 500 internal error because that’s what triggers the database initializer to kick in and restructure the database).

The next thing we need to change is that we don’t want to be dropping and recreating the database each time the data structure changes – this is a sure fire way in production to delete all that great app data! In the WebApiConfig.cs file there is a line which specifies the initializer for the database. In the template I downloaded this was already set as follows:

Database.SetInitializer(new realestateinspectorInitializer());

The realestateinspectorInitializer inherits from the generic class, ClearDatabaseSchemaIfModelChanges, which as you can imagine from the name is designed to clear up and recreate the database schema if the model changes (for example the above where we dropped the ToDoItems table). I’m going to change this to prevent the database being recreated; I’m going to start to apply migrations as I deploy new versions of my Mobile Service.

Note: In order to create the migrations that need to be applied you can use the tooling provided by the Entity Framework NuGet package via the package manager console. However, before you do this, make sure you run your Mobile Service locally with the TodoItems table comments out (don’t forget to invoke the service – just change the url in Fiddler to use localhost:XXXX where XXXX is the port number your service is running at). This will ensure you have a clean database to work from.

The first thing to do is to invoke Enable-Migrations from the package manager console (full details of the commands are available online – http://msdn.microsoft.com/en-au/data/jj591621.aspx). This will create a Migrations folder with a Configuration class and an initial migration, which assuming you have removed the TodoItems table will contain two empty methods, Up and Down.

Note: Make sure the Mobile Service project is the startup project for the solution when attempting to use the package manager console for generating migrations.

To use the Configuration class and the associated migrations I need to replace the following line:

Database.SetInitializer(new realestateinspectorInitializer());


var migrator = new DbMigrator(new Configuration());

At this point you can run your Mobile Service but you will still see a 500 error since there is still no TodoItems table. In fact at this point we’re going to introduce a RealEstateProperty entity that we’ll be working with:

public class RealEstateProperty : EntityData
    public string Address { get; set; }

I need to add this to the realestateinspectorContext as follows:

public DbSet<RealEstateProperty> RealEstateProperties { get; set; }

At this point if I attempt to run the Mobile Service I’ll get an AutomaticMigrationsDisabledException and if I look further at the description it indicates that there are pending model changes that haven’t been applied to the database via a migration. Essentially it’s detected that my model doesn’t align with the database schema.

Rather than hand craft the migration, which would be time consuming and rather mundane, I can again return to the package manager console and issue the Add-Migration command:

Add-Migration AddingRealEstatePropertiesToSchema

The “AddingRealEstatePropertiesToSchema” is a friendly name for this migration and a date-time stamp will be appended to the front of the newly created migration file eg 201501051134554_AddingRealEstatePropertiesToSchema.cs. This command in itself doesn’t update the database schema, all it does is determine the migration that needs to take place. To update the local database I can either call Update-Database, or simply run and invoke the Mobile Service. However, to do the latter I need to add a RealEstatePropertyController which will be the endpoint I’ll be invoking – the easiest way to do this is to simply rename the TodoItemController to RealEstatePropertyController, and change the generic parameter from TodoItem (this class can now be deleted) to RealEstateProperty.

When I run this I can now test the service by invoking a GET to http://localhost:51539/tables/realestateproperty (you may need to change the port number for your Mobile Service) which will of course return no data at this point but it doesn’t generate a 500 which means that it has run the migrations and that the model is aligned with the database schema. I can now publish this into my Azure Mobile Service and make a similar GET request to http://realestateinspector.azure-mobile.net/tables/realestateproperty. Don’t forget that you’ll need the X-ZUMO-APPLICATION header when calling a Mobile Service in Azure, whereas it is not required when running the Mobile Service locally.

The last thing I wanted to point out is that you can easily add data by issuing a POST command (don’t forget the Content-Type header)

POST: http://realestateinspector.azure-mobile.net/tables/realestateproperty
X-ZUMO-APPLICATION: wpxaIplpeXtkn————————-cg12
Content-Type: application/json

{‘Address’:’123 Sample Avenure, Random City’}

This should generate a 201 response if successful with a JSON body similar to the following (viewed using the JSON pane in Fiddler)


My service is starting to feel like it is more realistic now, although very simplistic. I’ll need to extend this model quite a bit more and then update the applications to access the data from it.

Leave a comment