Mobile Services .NET Backend: Initializers and Model Updates

Note: the default initializers have changed somewhat now that .NET backend has reached the "general availability" milestone. You can find the latest info in How to make data model changes to a .NET backend mobile service.

In case you haven’t heard the news, Mobile Services now has a service backend that is implemented as a Web API project. This “.NET backend” relies heavily on the Code First features of Entity Framework, where you specify on the data model as a DbContext and Entity Framework will automagically generate the database for you. This make the .NET backend quickstart projects work super easy on first try, both on the local machine (using localdb) and after you publish to Azure (using SQL Database).

The Default Initializer

If you dig into a .NET backend project, you will find the static WebApiConfig class, which is called when the service is first initialized. In the Register method, the following method is called to create the database:

     Database.SetInitializer(new my_mobile_serviceInitializer());

And in the same code page you can see that the generated my_mobile_serviceInitializer class inherits from DropCreateDatabaseIfModelChanges

     public class my_mobile_serviceInitializer : 
        DropCreateDatabaseIfModelChanges<my_mobile_serviceContext>
    {
        protected override void Seed(my_mobile_serviceContext context)
        {
            List<TodoItem> todoItems = new List<TodoItem>
            {
                new TodoItem { Id = "1", Text = "First item", Complete = false },
                new TodoItem { Id = "2", Text = "Second item", Complete = false },
            };

            foreach (TodoItem todoItem in todoItems)
            {
                context.Set<TodoItem>().Add(todoItem);
            }
            base.Seed(context);
        }
    }

The first time the project runs, Code First (by default) creates the database automatically. With this initializer, EF also tries to drop and recreate the database whenever it detects a model change. The Seed method provides the set of default data in the new database (both local and in Azure).

Making Schema Changes

If the good news is how easy it is to get the service up and running with a database created for you automatically in Azure by Entity Framework. The bad news is that the mobile service runtime doesn’t have permissions to drop a SQL Database in Azure (as it does for the local DB). This means that the default initializer will continue to work great locally (dropping and recreating the DB and reseeding every time you mess with the data model) but things will break when you try to publish changes with an existing database schema. (But this is actually a good thing…just think of the havoc it would wreak if it did drop your DB in Azure).

This means, of course, that you need to disable the call to SetInitializer when publishing model changes to Azure. At this point, there are really two options for publishing data model changes up to Azure:

  1. Keep dropping tables using the Azure portal (or some other client that can connect using a firewall exception)—which is really a very simple way to go if you don't want to keep your data and aren’t annoyed by doing this. Perhaps limit how often you publish to Azure and do most of your development local using the default initializer.
  2. Enable Code First migrations, which, while a bit tricky, enables you to publish data model changes and keep your existing data.

The rest of this topic will deal with option #2 (and bonus—we also get to do #1 in the process).

Drop Existing Tables

Before you can get going with Code First Migrations, we need to drop any existing tables in Azure that belong to the mobile service’s “schema.” You need to do this if you have already published your mobile service to Azure (and accessed the data, that’s when Code First goes to work) before you configure Code First Migrations. The one exception to this is if the database schema already matches your current model, then you are good to go.

  1. Login to the Azure Management Portal, select your mobile service, click the Configure tab, and click the SQL Database link.
    navagate-to-sql-database
    This takes you to the portal page for the database used by your mobile service.
  2. Click the Manage button and then login to your SQL Database server.
    manage-sql-database
  3. In the SQL Database manager, click Design, click Tables, then for each table in your database click Drop table and then OK to confirm. 
    sql-database-drop-tables
    Now that the tables are gone, we can enable Code First Migrations.

Getting the Latest Fixes

Depending on when you created your .NET backend project, you may have to make some recent updates for everything to work correctly.

  1. Open the NuGet package manager, click Updates and nuget.org, make sure that Included Prerelease is set and update all three .NET Backend packages.
    update-nuget-packages
    Once you are using the latest libraries, we can make the recent updates needed in the generated code.

  2. In your project, open the generated data model code file (usually mobile_service_nameContext.cs), locate the OnModelCreatingoverride method and make sure that it contains this code:

         string schema = ServiceSettingsDictionary.GetSchemaName();
        if (!string.IsNullOrEmpty(schema))
        {
            modelBuilder.HasDefaultSchema(schema);
        }
    

    This makes sure that default schema is the mobile service name—otherwise you get tables created in the dbo scheme, and it’s a mess.   

Enable Code First Migrations

Now we have finally come to the fun Code First Migrations stuff….

    1. Make sure that your mobile service is the startup project, open the Package Manager Console, and run the following command:

       PM> Enable-Migrations
      
    2. With Migrations now turned on, run the following command to create an initial migration:

 PM> Add-Migration Initial
  1. Open the App_Start\WebApiConfig.cs file and add the following using statements, where todolistService is your project’s namespace:

     using System.Data.Entity.Migrations;
    using todolistService.Migrations;
    
  2. In this same code file, comment-out the call to the Database.SetInitializermethod and add the following code after it:

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

    This disables the default Code First database initializer that drops and recreates the database and replaces it with an explicit request to apply the latest migration. At this point, any data model changes will result in an InvalidOperationException when the data is accessed, unless a migration has been created for it. Going forward, your service must use Code First Migrations to migrate data model changes to the database.

  3. Press F5 to start the mobile service project on the local computer. At this point, the database is in sync with the data model.

  4. Now make a change to your data model, such as adding a new UserId property to the TodoItem type, rebuild the project, and then in the Package Manager, run the following command:

     PM> Add-Migration NewUserId
    

    This creates a new migration named NewUserId. A new code file, which implements this change, is added in the Migrations folder.

  5. Press F5 again to restart the mobile service project on the local computer.

    The migration is applied to the database and the database is again in sync with the data model. If you didn’t add seed data in the Seed override method in Configure.cs, then there will be no data to return.

  6. Republish the mobile service to Azure, then run the client app to access the data and verify that data loads and no error occur.

Conclusion

If you haven’t already figured it out—it’s much better to pretty-much nail down your data model before you publish your mobile service to Azure, and definitively before you fill the database with valuable data. That way, you can keep using the default initializer