The Know It. Prove It. Challenge: Join the 28 day challenge to rock your tech skills

Ready to accelerate your tech skills? Then accept the Know It. Prove It. challenge (KiPi), a month-long global learning challenge for IT pros, developers, and students!

During this month-long learning challenge, you’ll earn badges and points, compete against other learning challenges, and best of all, learn the real-world skills that can make an impact in your job and career. The challenge kicks off throughout February and it’s easy to get started!

  1. Choose your Know It. Prove It. learning challenge

To start the Know It. Prove It. challenge, select one of eight learning challenges right for you on knowitproveit.com:

Kipi

  1. Jam on your skills anywhere

Once you’ve chosen your learning challenge, you’ll have access to training modules to help you gain the skills you want to learn. Each one takes about an hour to complete and were created by technology experts to help you quickly build the understanding you need. Just log in from wherever you are and start jamming on your skills.

  1. Assemble your community band

During the Know It. Prove It. challenge, you’ll also have an opportunity to connect with others just like you who are looking to accelerate their tech skills and this starts by completing your profile. After that, you’ll be able to track your progress, share your experiences with other members in the KiPi community. You’ll also receive badges and points, compete against others, and get encouragement to help keep you moving forward!

No matter who you are – an IT pro, a developer, or a student – there’s a topic you can add to your skillset and the Know It. Prove It. challenge is just the way to get there.

To accept the Know It. Prove It. challenge, go to knowitproveit.com and make February the month you rock your tech skills!

Microsoft’s Windows AppStudio Ebook

Microsoft’s Windows AppStudio Ebook

(Download here)

Microsoft’s Windows AppStudio – Menu is the perfect guide to create and extend a Menu App from Microsoft’s Windows AppStudio.

You will learn how to:

• Create an application in Windows AppStudio
• Create a BackOffice App to manage the data
• Connect the Menu App to an Azure Mobile Service
• Add support to Push Notifications
• Integrate with Cortana

With lots of images, code snippets and source code on each important step, this eBook will provide all you need to create fantastic applications.

 

Avoid nightmares using EF First Migration in Azure Mobile Services

Scope

This article has the goal 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 different 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 we can see in the 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 for create the database.

Note: There are others IDatabaseInitializer that can be used, but the problem is same, data will 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 following:
Figure 1 Creating an Azure Mobile Service
Figure 1 Creating an Azure Mobile Service
After it, create a class diagram like in the Figure 2, to understand the project.

Note: Read more about creating class diagram in the article Class diagram: an easy way to understand code.
Figure 2 The class diagram
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 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.

Note:

  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 will be required to use the offline feature provided by Azure Mobile Services and to create a TableController we need it.

Enable Migrations

At this moment we know the model and the DbContext, but before start 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 it open the “Package Manager Console” as following:

Figure 3 Opening Package Manager Console
Figure 3 Opening Package Manager Console

The following window will be opened:
Figure 4 Package Manager Console
Figure 4 Package Manager Console

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

Figure 5 Enable Migrations

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
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 to create, update or delete the database depending on the migration provided and it is possible to populate the database with initial data using the seed method. The seed method can be used to create fake data or add initial data in the database, and all developers should aware that method can run more one time, with it is need to verified if the data is not cloned.

Let’s define some data in the Seed method, as 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 add 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 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.

After it, we can add the migration, and for it 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
Figure 7 Adding migration Initial
The result will be something as following:

Figure 8 The migration
Figure 8 The migration

The Initial class will define the migration and the Up method, will 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 defined how the project will run the migration or we do not update the database using “Update-Database” or “Update-Database -script” like we can see in the Figure 9

Figure 9 Creating the sql script
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, which 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 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 when the Initialize method 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
Figure 10 publishing the service

Is possible to following the publish process using the “Web Publish Activity”, as we can see in Figure 11

Figure 11  Web Publish Activity
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 following:

Figure 12 Azure Mobile Service running
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
Figure 13 Try it out

Or in the database

Figure 14 Data in database
Figure 14 Data in database

which logs will be

Figure 15 Log
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 create 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
Figure 16 Azure Mobile Service with errors

Let’s change the model for something as following

 

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

        public string Name { get; set; }

        public bool Complete { get; set; }
    }

After it we need to add a new migration, for it we need to run “Add-Migration SecondMigration” in “Package Manager Console”, as 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 it we can publish the Azure Mobile Service and the result will be

Figure 16 The data

Figure 16 The data

Developers should be aware that the seed method added new items which 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 populate data during different 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:

Figure
Figure
Figure 16 Logs

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

The errors we got with the default user are related with the fact the user only has few permissions for security reasons. Create 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 different 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 experience:

I lost so many time with this problem, and at 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 (which can be defined in configuration panel). In my experience the user changed time to time and it happened to me more than one time. Never found it in documentation….

Source Code

See the source code in Azure Mobile Services Sample.

See Also

MSDN Documentation – Code First Migrations