Avoid Nightmares Using EF First Migration in Azure Mobile Services

Scope

The purpose of this article is to show how to enable, add and use Entity Framework Code First Migrations in Azure Mobile Services to create and update the database based in the model.

Introduction

Microsoft Azure Mobile Services is an Azure service offering designed to make it easy to create highly-functional mobile apps using Azure. Mobile Services brings together a set of Azure services that enable backend capabilities for your apps.

In Azure Mobile Services, there are multiple ways to create the database, it can be created manually in SQL Server Management Studio or in Visual Studio, or can be created using Code First Migrations or using IDatabaseInitializer. By default, Azure Mobile Service uses database initializers, like as can be seen in the following WebApiConfig class from the project created:

namespace AzureMobileService
{
    public static class WebApiConfig
    {
        public static void Register()
        {
            // Use this class to set configuration options for your mobile service
            ConfigOptions options = new ConfigOptions();

            // Use this class to set WebAPI configuration options
            HttpConfiguration config = ServiceConfig.Initialize(new ConfigBuilder(options));

            // To display errors in the browser during development, uncomment the following
            // line. Comment it out again when you deploy your service for production use.
            // config.IncludeErrorDetailPolicy = IncludeErrorDetailPolicy.Always;

            Database.SetInitializer(new MobileServiceInitializer());
        }
    }

    public class MobileServiceInitializer : DropCreateDatabaseIfModelChanges<MobileServiceContext>
    {
        protected override void Seed(MobileServiceContext context)
        {
            List<TodoItem> todoItems = new List<TodoItem>
            {
                new TodoItem { Id = Guid.NewGuid().ToString(), Text = "First item", Complete = false },
                new TodoItem { Id = Guid.NewGuid().ToString(), Text = "Second item", Complete = false },
            };

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

            base.Seed(context);
        }
    }
}

Like we can see, the MobileServiceInitializer implements the DropCreateDatabaseIfModelChanges, it is an implementation of IDatabaseInitializer that will DELETE, recreate and optionally re-seed the database only if the model has changed, since the database was created. If the model doesn't change we can use it, but “Are we sure that the model will not change in future?” this way maybe this solution is not a best solution to create the database.

Note: There are others IDatabaseInitializer that can be used, but the problem is the same, data will be lost if the model changes.

In this article we will use Entity Framework - Code First Migrations, that means we create the model first and then create the database based in the model structure, this process happens when we do a deploy and we will see common issues related with migrations in Azure Mobile Services.

Description

Create a .Net Backend as in the following:


Figure 1 Creating an Azure Mobile Service

Then create a class diagram as in Figure 2, to understand the project.

Note: Read more about creating the class diagram in the article Class diagram: an easy way to understand code.


Figure 2 The class diagram

This way, we can conclude, by default an Azure Mobile Service has at least the following classes:

 

• TodoItem: define the model and at the same time the DTO
  
  
• MobileServiceContext: implement the DbContext from the Entity Framework
  
  
• TodoItemController: define the service to expose CRUD operation to the TodoItem

And the model defined by TodoItem is the DTO provided by the service defined in the TodoItemController.

Notes:

1. We could create another model but it is not important for the article, this way we will use the model provided by default.
   
   
2. TodoItem implements the EntityData from the Azure Mobile Service SDK and each DTO should implement this class because it is necessary to use the offline feature provided by Azure Mobile Services and to create a TableController we need it.

Enable Migrations

At this moment we understand the model and the DbContext, but before starting with the migrations process we need to define the connection string to a New database in WebConfig. Now we need to enable the project to have migrations, for that open the “Package Manager Console” as in the following:


Figure 3 Opening Package Manager Console

The following window will be opened:


Figure 4 Package Manager Console

To enable migration it is necessary to run “Enable-Migrations” in the “Package Manager Console” and the “Default project” should be selected based on the project that contains the DbContext:


Figure 5 Enable Migrations

This operation will result in a new folder, called Migrations with a new class, in the project, like we can see in the Figure 6.


Figure 6 Migrations folder

The class added, the Configuration, is defined by:

internal sealed class Configuration : DbMigrationsConfiguration<Models.MobileServiceContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = false;
    }

    protected override void Seed(AzureMobileService.Models.MobileServiceContext context)
    {
        //  This method will be called after migrating to the latest version.

        //  You can use the DbSet<T>.AddOrUpdate() helper extension method
        //  to avoid creating duplicate seed data. E.g.
        //
        //    context.People.AddOrUpdate(
        //      p => p.FullName,
        //      new Person { FullName = "Andrew Peters" },
        //      new Person { FullName = "Brice Lambson" },
        //      new Person { FullName = "Rowan Miller" }
        //    );
        //
    }
}

And this class will be responsible for creating, updating or deleting the database depending on the migration provided and it is possible to populate the database with the initial data using the seed method. The seed method can be used to create fake data or add initial data to the database and all developers should be aware that the method can run more than one time, with it it is necessary to verifiy that the data is not cloned.

Let's define some data in the Seed method, as in the following:

internal sealed class Configuration : DbMigrationsConfiguration<Models.MobileServiceContext>
{
    /// <summary>
    /// Initializes a new instance of the <see cref="Configuration"/> class.
    /// </summary>
    public Configuration()
    {
        AutomaticMigrationsEnabled = false;
    }

    /// <summary>
    /// Seeds the specified context.
    /// </summary>
    /// <param name="context">The context.</param>
    protected override void Seed(Models.MobileServiceContext context)
    {
        context.TodoItems.AddOrUpdate(
              p => p.Id,
              new TodoItem { Id = Guid.NewGuid().ToString(), Text = "Clean the car." },
              new TodoItem { Id = Guid.NewGuid().ToString(), Text = "Read a book" }
            );
    }
}

Add Migration

Before adding the migration all developers should be aware that in the MobileServiceContext class we have the method.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    string schema = ServiceSettingsDictionary.GetSchemaName();
    if (!string.IsNullOrEmpty(schema))
    {
        modelBuilder.HasDefaultSchema(schema);
    }

    modelBuilder.Conventions.Add(
        new AttributeToColumnAnnotationConvention<TableColumnAttribute, string>(
            "ServiceTableColumn", (property, attributes) => attributes.Single().ColumnType.ToString()));
}

And in this method will be defined the name of the schema used by the database and the name of the schema is defined in the WebConfig file, as in the following:

<appSettings>
<add key="MS_MobileServiceName" value="migrations" />

Where the value “migrations” is the name of the Azure Mobile Service, if the schema name and the Azure Mobile Service's name does not match the migration, it will fail because of the default user, used by the Azure Mobile Service created, do not have permissions to change the schema name or is possible to get an error saying that the user do not have permission to access the master database.

We can then add the migration and for that we need to run “Add-Migration Initial” in “Package Manager Console”, this command will use the model defined in the project and will compare it with the database, to create the migration.


Figure 7 Adding migration Initial

The result will be something as in the following:


Figure 8 The migration

The Initial class will define the migration and the Up method to create the database and the table “AzureMobileService.TodoItems” and the Down method will do the reverse. The Initial class will be defined by:

public partial class Initial : DbMigration
{
    public override void Up()
    {
        CreateTable(
            "migrations.TodoItems",
            c => new
                {
                    Id = c.String(nullable: false, maxLength: 128,
                        annotations: new Dictionary<string, AnnotationValues>
                        {
                            {
                                "ServiceTableColumn",
                                new AnnotationValues(oldValue: null, newValue: "Id")
                            },
                        }),
                    Text = c.String(),
                    Complete = c.Boolean(nullable: false),
                    Version = c.Binary(nullable: false, fixedLength: true, timestamp: true, storeType: "rowversion",
                        annotations: new Dictionary<string, AnnotationValues>
                        {
                            {
                                "ServiceTableColumn",
                                new AnnotationValues(oldValue: null, newValue: "Version")
                            },
                        }),
                    CreatedAt = c.DateTimeOffset(nullable: false, precision: 7,
                        annotations: new Dictionary<string, AnnotationValues>
                        {
                            {
                                "ServiceTableColumn",
                                new AnnotationValues(oldValue: null, newValue: "CreatedAt")
                            },
                        }),
                    UpdatedAt = c.DateTimeOffset(precision: 7,
                        annotations: new Dictionary<string, AnnotationValues>
                        {
                            {
                                "ServiceTableColumn",
                                new AnnotationValues(oldValue: null, newValue: "UpdatedAt")
                            },
                        }),
                    Deleted = c.Boolean(nullable: false,
                        annotations: new Dictionary<string, AnnotationValues>
                        {
                            {
                                "ServiceTableColumn",
                                new AnnotationValues(oldValue: null, newValue: "Deleted")
                            },
                        }),
                })
            .PrimaryKey(t => t.Id)
            .Index(t => t.CreatedAt, clustered: true);

    }

    public override void Down()
    {
        DropIndex("migrations.TodoItems", new[] { "CreatedAt" });
        DropTable("migrations.TodoItems",
            removedColumnAnnotations: new Dictionary<string, IDictionary<string, object>>
            {
                {
                    "CreatedAt",
                    new Dictionary<string, object>
                    {
                        { "ServiceTableColumn", "CreatedAt" },
                    }
                },
                {
                    "Deleted",
                    new Dictionary<string, object>
                    {
                        { "ServiceTableColumn", "Deleted" },
                    }
                },
                {
                    "Id",
                    new Dictionary<string, object>
                    {
                        { "ServiceTableColumn", "Id" },
                    }
                },
                {
                    "UpdatedAt",
                    new Dictionary<string, object>
                    {
                        { "ServiceTableColumn", "UpdatedAt" },
                    }
                },
                {
                    "Version",
                    new Dictionary<string, object>
                    {
                        { "ServiceTableColumn", "Version" },
                    }
                },
            });
    }
}

Run the migration

Now that we have the migration defined we can run the project but the database will not be created, because we did not define how the project will run the migration or we do not update the database using “Update-Database” or “Update-Database -script” as we can see in Figure 9.


Figure 9 Creating the SQL script

This operation will create a SQL script that can be used to create or even update the database if the developer would like.

In Azure Mobile Services, we use.

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

To run the migration that will be defined in WebApiConfig.

To avoid the issue about “Not found bootstrapper”, we need to change the WebApiConfig to:

public class WebApiConfig : IBootstrapper
{
    /// <summary>
    /// Defines the entry point for the application. It is the responsibility of this entry point
    /// to call <see cref="T:Microsoft.WindowsAzure.Mobile.Service.ServiceConfig" /> which will start the configuration of the application.
    /// </summary>
    public void Initialize()
    {
        // Use this class to set configuration options for your mobile service
        ConfigOptions options = new ConfigOptions();

        // Use this class to set WebAPI configuration options
        HttpConfiguration config = ServiceConfig.Initialize(new ConfigBuilder(options));

        // To display errors in the browser during development, uncomment the following
        // line. Comment it out again when you deploy your service for production use.
        config.IncludeErrorDetailPolicy = IncludeErrorDetailPolicy.Always;

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

And then we need to change the WebApiApplication, as in the following:

public class WebApiApplication : System.Web.HttpApplication
{
    /// <summary>
    /// The _web API configuration
    /// </summary>
    private readonly WebApiConfig _webApiConfig;

    /// <summary>
    /// Initializes a new instance of the <see cref="WebApiApplication"/> class.
    /// </summary>
    public WebApiApplication()
    {
        _webApiConfig = new WebApiConfig();
    }

    /// <summary>
    /// Application_s the start.
    /// </summary>
    protected void Application_Start()
    {
        _webApiConfig.Initialize();
    }
}

Each developer should be aware that when the Initialize method is run, each time the application starts, it will create or update the database based in the migrations defined by the current model.

Publishing the services:


Figure 10 publishing the service

It is possible to use the following publishing process using the “Web Publish Activity”, as we can see in Figure 11.


Figure 11 Web Publish Activity

Note: When we do the publish the Azure Mobile Service will replace some configuration from WebConfig, if you want to see the final WebConfig see this article.

The browser will be opened, as in the following:


Figure 12 Azure Mobile Service running

And we can request the TodoItems in “try this out”, like we can see in the Figure 13.


Figure 13 Try it out

Or in the database:


Figure 14 Data in database


Figure 15 Log

Handle errors

Changing the model

The backend can change and with it the model can change. If we change the model without creating the respective migration we will receive an error like this.

Error:

Boot strapping failed: executing 'WebApiConfig' caused an exception: 'Unable to update database to match the current model because there are pending changes and automatic migration is disabled. Either write the pending model changes to a code-based migration or enable automatic migration. Set DbMigrationsConfiguration.AutomaticMigrationsEnabled to true to enable automatic migration.”


Figure 16 Azure Mobile Service with errors

Let's change the model to something as in the following:

public class TodoItem : EntityData
{
    public string Text { get; set; }

    public string Name { get; set; }

    public bool Complete { get; set; }
}

Then we need to add a new migration, for that we need to run “Add-Migration SecondMigration” in “Package Manager Console”, as in the following:

PM> add-migration SecondMigration
Scaffolding migration 'SecondMigration'.

The Designer Code for this migration file includes a snapshot of your current Code First model. This snapshot is used to calculate the changes to your model when you scaffold the next migration. If you make additional changes to your model that you want to include in this migration, then you can re-scaffold it by running 'Add-Migration SecondMigration' again.
PM>

And the result will be:

public partial class SecondMigration : DbMigration
{
    public override void Up()
    {
        AddColumn("migrations.TodoItems", "Name", c => c.String());
        DropColumn("migrations.TodoItems", "DueDate");
    }

    public override void Down()
    {
        AddColumn("migrations.TodoItems", "DueDate", c => c.DateTime(nullable: false));
        DropColumn("migrations.TodoItems", "Name");
    }
}

If we change the seed method to:

protected override void Seed(Models.MobileServiceContext context)
{
    context.TodoItems.AddOrUpdate(
          p => p.Id,
          new TodoItem { Id = Guid.NewGuid().ToString(), Text = "Clean the car.", Name = "MyTask"},
          new TodoItem { Id = Guid.NewGuid().ToString(), Text = "Read a book", Name = "MyHobbies" }
        );
}

After that we can publish the Azure Mobile Service and the result will be:


Figure 17 Try this out

And:



Developers should be aware that the seed method added new items that has the same “Text” as the first items added but in this case the “Name” is filled. This was a sample from a common issue we can create when populating data during multiple migrations.

Note: If the connecting string, defined in WebConfig, is not using the correct reference, this process can fail with the following error.

PM> add-migration SecondMigration

Unable to generate an explicit migration because the following explicit migrations are pending: [201501162340213_Initial]. Apply the pending explicit migrations before attempting to generate a new explicit migration.
PM>

Deleting the Database

If during the process, we delete the database and then we try to run the services we will get:





This way, the default user, used by the Azure Mobile Service created, does not have permissions to access the master database.

The errors we got with the default user are related to the fact the user only has a few permissions for security reasons. Creating a database user will avoid this kind of issue.

Conclusion

In conclusion, we can conclude that EF CodeFirst Migration is a good way to manage the various changes made in the model and this way any time an Azure Mobile Service is deployed it will create or update the associated database without additional work, with initial data or not.

If we are using the default user to access the database:

 

- the user cannot access the master to re-create the database.

- the database schema must have the same name as the Azure Mobile Service.

If we create a database user to avoid the two last problems, you need to ignore the default connection string (MS_TableConnectionString) and you need to create your own connection string (for example: MyTableConnectionString where you need to define all required fields to the database and should be added the user created to avoid the issues).

Personal opinion + Disclaimer:

I have lost so much time from this problem and in the end I prefer to create and use my own database user to simplify the process and define my own key to the connection string (that can be defined in the configuration panel). In my experience the user is changed time to time and it happened to me more than one time. Never found it in the documentation.

Source Code

See the source code in Azure Mobile Services Sample.

See Also

• MSDN Documentation - Code First Migrations.