Mobile Services .NET Backend: Initializers and Model Updates

Artikkeli
03/28/2014

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:

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.
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.

Login to the Azure Management Portal, select your mobile service, click the Configure tab, and click the SQL Database link.

This takes you to the portal page for the database used by your mobile service.
Click the Manage button and then login to your SQL Database server.
In the SQL Database manager, click Design, click Tables, then for each table in your database click Drop table and then OK to confirm.

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.

Open the NuGet package manager, click Updates and nuget.org, make sure that Included Prerelease is set and update all three .NET Backend packages.

Once you are using the latest libraries, we can make the recent updates needed in the generated code.
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

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;
```
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.
Press F5 to start the mobile service project on the local computer. At this point, the database is in sync with the data model.
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.
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.
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

Comments

Anonymous
March 29, 2014
The comment has been removed
Anonymous
March 30, 2014
@kkap No, I haven't seen this behavior, but I have always deleted existing tables before starting to use migrations, so... Have you tried creating the initial migration using the -IgnoreChanges switch? Glenn.
Anonymous
April 05, 2014
thanks, figured it out, the error was because I had 'id' property defined in my model class. When upgrading to mobile services, I changed the inheritance of this model class to inherit from 'EntityData', which has an 'Id' property defined. This was causing that error when running the migration - Column names in each table must be unique. thank you, the service is running now.
Anonymous
April 09, 2014
@Glenn, how can I make the 'Id' and 'createdDate' fields of 'Microsoft.WindowsAzure.Mobile.Service.EntityData'( from which my model classes are derived), to be AutoGenerated and default SQL value 'GetDate()' respectively. otherwise when I do a 'Http Post' of a new record to my table, I need to provide these values in my json object. I tried below in migration, AlterColumn("dbo.Table1", "Id", c => c.String(defaultValueSql: "newid()")); AlterColumn("dbo.Table1", "CreatedAt", c => c.DateTime(defaultValueSql: "GETDATE()")); AlterColumn("dbo.Table1", "Deleted", c => c.Boolean(defaultValue: false)); but the migration update fails saying Object DF_<tablename>Id/DF<tablename>createdDate or constraint key PK<tablename>Id is dependent on these columns. I can try dropping the PK constraint and re-create it later, but I am not sure of that DF<tablename>_Id object. I believe this object may have been provided by Mobile.Service.EntityData for giving some default values to these columns, but then why it asks to provide values for these columns in the Http POST request body when I try to add a new record? any idea?
Anonymous
April 10, 2014
@kkap My first concern is why your table belongs to the dbo schema. The schema name should be the same as the mobile service name. Make sure that you are using the latest version of the DbContext from the portal quickstart (it has some recent fixes that prevent the dbo problem).
Anonymous
April 10, 2014
@glenn, sorry for the confusion the code in my previous post created, the schema name for the tables is in fact the name of the mobile service, not dbo. I just put dbo as I didn't want to put the actual name of my mobile service.
Anonymous
May 01, 2014
Hi Glenn, unfortunately this does not work for me at all. The only solution that is really working (found on SO) stackoverflow.com/.../error-while-enabling-code-first-migrations-on-mobile-services-database - works without any code-changes. I only updated the three packages.
Anonymous
August 05, 2014
Thanks a lot Glenn! Great article. Saved me a lot of time. I wish the demos refer to it, because in all demos I have seen so far, they move from local DB to azure and magic happens and it works :).
Anonymous
August 05, 2014
Thanks aelswify, Notice that the default initializers have changed somewhat now that .NET backend has reached the "general availability" milestone. This topic has the latest info: azure.microsoft.com/.../mobile-services-dotnet-backend-how-to-use-code-first-migrations

Jaa