Using Entity Framework First Migration in Azure Mobile Services

Scope
The purpose of this article is to show how to use Entity Framework Code First Migrations Jump in Azure Mobile Services to create and update the database used 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 adding back-end capabilities to 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 Visual Studio, or from code using Code First Migrations Jump or IDatabaseInitializer Jump . 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
    {
        protected override void Seed(MobileServiceContext context)
        {
            List todoItems = new List
            {
                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().Add(todoItem);
            }
     
            base.Seed(context);
        }
    }
}

As we can see, the MobileServiceInitializer implements the DropCreateDatabaseIfModelChanges Jump
, it is an implementation of IDatabaseInitializer Jump 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 the best solution to create the database.
Note: There are other IDatabaseInitializers 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
    {
        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.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 verify that the data is not cloned.
    Let’s define some data in the Seed method, as in the following:

    
    internal sealed class Configuration : DbMigrationsConfiguration
    {
        /// 
        /// Initializes a new instance of the  class.
        /// 
        public Configuration()
        {
            AutomaticMigrationsEnabled = false;
        }
        
        /// 
        /// Seeds the specified context.
        /// 
        /// The context.
        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(
                "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:

    
        
        
    
    

    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
                            {
                                { 
                                    "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
                            {
                                { 
                                    "ServiceTableColumn",
                                    new AnnotationValues(oldValue: null, newValue: "Version")
                                },
                            }),
                        CreatedAt = c.DateTimeOffset(nullable: false, precision: 7,
                            annotations: new Dictionary
                            {
                                { 
                                    "ServiceTableColumn",
                                    new AnnotationValues(oldValue: null, newValue: "CreatedAt")
                                },
                            }),
                        UpdatedAt = c.DateTimeOffset(precision: 7,
                            annotations: new Dictionary
                            {
                                { 
                                    "ServiceTableColumn",
                                    new AnnotationValues(oldValue: null, newValue: "UpdatedAt")
                                },
                            }),
                        Deleted = c.Boolean(nullable: false,
                            annotations: new Dictionary
                            {
                                { 
                                    "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>
                {
                    {
                        "CreatedAt",
                        new Dictionary
                        {
                            { "ServiceTableColumn", "CreatedAt" },
                        }
                    },
                    {
                        "Deleted",
                        new Dictionary
                        {
                            { "ServiceTableColumn", "Deleted" },
                        }
                    },
                    {
                        "Id",
                        new Dictionary
                        {
                            { "ServiceTableColumn", "Id" },
                        }
                    },
                    {
                        "UpdatedAt",
                        new Dictionary
                        {
                            { "ServiceTableColumn", "UpdatedAt" },
                        }
                    },
                    {
                        "Version",
                        new Dictionary
                        {
                            { "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
    {
        /// 
        /// Defines the entry point for the application. It is the responsibility of this entry point
        /// to call  which will start the configuration of the application.
        /// 
        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
    {
        /// 
        /// The _web API configuration
        /// 
        private readonly WebApiConfig _webApiConfig;
        
        /// 
        /// Initializes a new instance of the  class.
        /// 
        public WebApiApplication()
        {
            _webApiConfig = new WebApiConfig();
        }
        
        /// 
        /// Application_s the start.
        /// 
        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
    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.

    Azure Mobile Services: how to update a .NET back end project

    Scope
    This article has the goal to show how to update a .NET back end project from Azure Mobile Services, this means we will update the project references using Nuget packages.

    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 back end capabilities for your apps.

    When a developer creates a .NET back end project from Azure Mobile Services, several NuGet packages will be installed, for example, if the project is created in Visual Studio 2013 – Update 4, like we can see in the Image 1:

    Image 1 Creating the Azure Mobile Service

    The default package files will be like as follows:

    
    
    
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
    
    

    But if we “Manage NuGet Packages” and select “Updates”, the windows will show that there are updates that we can do like we can see in the Image 2:

    Image 2 Manage NuGet Packages – Available Updates

    the first thing we can try to do is to click in the “Update All” button, that will make sense for the developers, but then we will get something as we can see in the Image 3:

    Image 3 Error updating all

    And you have a world of nightmares. Updating the NuGet packages from Azure Mobile Services start adds 36 NuGet packages to the project and is extremely challenging to understand the dependencies between the different NuGet packages. If we start updating one by one we will get errors as in Image 4 below. It is not practical to update this one by one – this article shows how to avoid the problem.

    Image 4 Azure Mobile Service with errors

    It means the reference added to the project is not the same version used by any NuGet and can be hard to understand which is the right version.

    Description

    To understand the NuGet packages and because our project is an Azure Mobile Service project, we can start by analyzing the packages.

    In the NuGet website, we found.

    Microsoft Azure Mobile Services .NET back end 1.0.405 

    Has the dependencies:

    Has the dependencies:

    Has the dependencies:

    This way, we can conclude the Microsoft Azure Mobile Services .NET Backend Entity Framework Extension 1.0.405   NuGet depends on the WindowsAzure.MobileServices.Backend.Tables   NuGet and this depend on the WindowsAzure.MobileServices.Backend   NuGet. This mean if we update the Microsoft Azure Mobile Services .NET Backend Entity Framework Extension   NuGet the references related with Azure Mobile Service will be updated.

    In the “Manage NuGet Package” click on update the Microsoft Azure Mobile Services .NET Backend Entity Framework Extension   NuGet, as we can see in the Image 5:


    Image 5 Updating Nuget
    And then, click “I Accept”

    Image 6 License

    We get

    Image 7 NuGet packages updated

    With this another question can be raised, “Did we update all NuGet packages we can?” and this question can be found by uninstalling the Microsoft Azure Mobile Services .NET Backend Entity Framework Extension Jump NuGet, as in the Image 8.


    Image 8 Uninstall Nuget

    Uninstall this NuGet will request a confirmation to uninstall all dependencies, as in the Image 9, where we will click in “Yes”.


    Image 9 Nuget packages to remove

    The result will be

    Image 10 The service running in localhost

    And the package file updated to

    
    
    
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
    
    
    

    Returning back to the “Updates”, we will see the NuGet packages, in the Image 11 that we can update, but for the reasons we saw, it is not possible to update all.


    Image 11 Manage NuGet Packages

    Conclusion
    In conclusion, each developer should be aware of the dependencies between NuGet packages and more than one package can depend from the same NuGet package but for different versions, this means is required to keep the versions used by each one.

    Azure Mobile Services: How to Create a Dev and Prod Environment

    Scope

    This article has the goal to show how to create a development and production environment for a .Net Backend from Azure Mobile Services.

    Introduction

    From the MSDN Documentation  ,

    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. Mobile Services provides the following backend capabilities in Azure to support your apps:

    • Client libraries support mobile app development on various devices, including Windows 8, Windows Phone 8, iPhone, and iPad.
    • Simple provisioning and management of tables for storing app data.
    • Integration with notification services to deliver push notifications to your app.
    • Integration with well-known identity providers for authentication.
    • Precise control for authorizing access to tables.
    • Supports scripts to inject business logic into data access operations.
    • Integration with other cloud services.
    • Supports the ability to scale a mobile service instance.
    • Service monitoring and logging.

    Azure Mobile Services is great for mobile application development with client libraries for both Apple and Microsoft mobile platforms.

    There are various scenarios where one requires multiple environments for an Azure Mobile Service.
    These include:

    • Breaking changes between published versions of an app.
    • Different configurations for test and production.

    Different environments are necessary in order to have different configurations for a given Azure Mobile Service. Azure Mobile Services allows testing in localhost, but do not provide a way to have different environments.  

    Description

    To create a development and production environment for a .Net Backend – Azure Mobile Services is required to create a Dev and Prod Backend and then is possible to create the Dev and Prod profiles based on transform files.

    Let’s see it in more detail.

    Creating the Azure Mobile Services

    In Azure Portal, create two .Net Backend – Azure Mobile Service, let’s call it as following

    • For the development environment:
      • MyAMSDev – the Azure Mobile Service name
      • MyAMSDev_db – the database name
    • For the production environment:
      • MyAMSProd – the Azure Mobile Service name
      • MyAMSProd_db – the database name

    In Azure Portal, the result will be azure portal

    And

    azure portal

    Is possible to use the same database, but it is necessary to use different schemas and if different databases are provided is easier to manage each environment (update or even delete). A Backend that uses Entity Framework migrations will recreate the service from the scraps and with it any time it can be deleted, deployed and recreated.

    To read more about the steps required to create an Azure Mobile Service, read the following article

    How to create the MyMenuApp Azure Mobile Service  .

    Creating a Transform File

    In Visual Studio, select the solution and open the context menu as following

    azure mobile service

    After it, click in Configuration Manager as following

    azure mobile service

    And then create a new configuration as following

    azure mobile service
    azure mobile service

    In this case, a Dev configuration is defined based on Debug configuration and to the Prod configuration should be used the Release configuration as following

    azure mobile service

    At this moment, is possible to define Debug, Dev, Prod and Release configuration

    azure mobile service

    For each configuration is possible to define the conditional compilation. For example, in Dev is possible to have

    azure mobile service

    In reality, it means, we can do

    #if Dev

    // for example can fill the database with fake data

    #else

    // in another environment different from Dev

    #endif

    To have advantages of this configuration using the Web.Config, let’s add the config transform as following

    azure mobile service

    Which the result will be

    azure mobile service

    Now is possible

    – To set in the Web.Config, the connection string to the local database, used by the localhost

     <connectionStrings>
        <add name="MS_TableConnectionString1" connectionString="Data Source=LT114925;Initial Catalog=MyAMSLocal;Integrated Security=True;MultipleActiveResultSets=true" providerName="System.Data.SqlClient" />
       </connectionStrings>

    – To set in the Web.Dev.Config, the connection string to the MyAMSDev_db database, used by the Dev environment (or even the localhost!)

    <connectionStrings>
          <add name="MS_TableConnectionString1"
    connectionString="<connection string from MyAMSDev_db>"
    xdt:Transform="SetAttributes" xdt:Locator="Match(name)"/>
        </connectionStrings>

    – To set in the Web.Prod.Config, the connection string to the MyAMSProd_db database, used by the Prod environment

       <connectionStrings>
          <add name="MS_TableConnectionString1"
    connectionString="<connection string from MyAMSProd_db>"
    xdt:Transform="SetAttributes" xdt:Locator="Match(name)"/>
        </connectionStrings>

    And in each deploy, the respective configs will be used.

    Let’s publish the service to the Azure.

    Select the project and using the context menu click in Publish, as following

    azure mobile service

    Then connect to Azure Mobile Service, using the Azure Account, and select the MyAMSDev as following

    azure mobile service

    In Settings, set the configuration and the file publish options (only!)

    azure mobile service

    And then publish it!

    Do the same process to Prod, as following

    azure mobile service

    And then publish it!

    Is possible to see the Web Publish Activity that shows the publish process

    Jump

    At this moment, both environments are deployed and running and are not required to define or change each environment in the future, only is needed to have attended each one is set before deploy.

    Notes

      1. In this case, was using the MS_TableConnectionString1 name for defining the connection string and the default value MS_TableConnectionString, defined in Azure Portal, will be ignored.
      A developer can to set different connect string or even change the name, but needs to be defined with the same name in all environments.
      1. With the Visual Studio 2013 – update 4 is possible to change the database in Settings separator but in this case, it will be ignored!

    For more information about the transform file, read the following article

    How to: Transform Web.config When Deploying a Web Application Project 

    Conclusion

    In conclusion, creating different environments for a .Net Backend – Azure Mobile Service is an easy task that is a plus in the development, which allow testing new features before it goes to production and it allows to make sure the tests done in localhost will keep working in the Azure Mobile Service without to affect the production environment.

    Microsoft’s Windows App Studio Beta : Connecting a Menu App to Azure Mobile Service

    Scope

    This article aims to connect the Menu App from Windows App Studio with Azure Mobile Service, using .Net back end.

    Introduction

    Windows App Studio   is a service that allows any person without programming skills to create applications for Windows Phone and Windows. In just 4 steps: have an idea, add content, choose the style and use the application. The service provides a set of templates to help the users in creating various applications, theses templates are great starting points for specific uses which are more frequently used.

    To understand all the steps for creating an app using App Studio   see the article Creating Windows Phone and Window 8.1 applications using Microsoft App Studio. This article explains the Contoso Template and the Web Template.

    Menu Template

    Menu Template is one template provide by App Studio  , and it is used for the following scenario:

    “Do you own a restaurant, or have a favorite place you go every week? Use this template to show your customers or your friends about the delicious meal they’ll find there. With easy-to-navigate sections and pictures for starters, main courses and desserts.”

    The template provides

    • A menu for
      • Starters

        Contains a list of all starters

      • Mains

      Contains a list of all mains

      • Desserts

        Contains a list of all desserts

      • Beverages

      Contains a list of all beverages

    • A list of special offers

    When the template is created, the default data uses static resources

    If we click in “Edit data”, we will see the following screen

    Windows App Studio   allows us to export and import data, but each time we need to edit data, we need to publish the new version in the Store unless we select dynamic resources. For help in this process we will create a .Net Backend in Azure Mobile Service that allows us to manage the data that will be consumed by the Menu App.

    See the default Menu App, for Windows Phone 8.1 and Windows 8.1 that was generated by 
    Step 1: Add default Menu App generated by AppStudio. 

    Understanding the source code

    The source code provided has a solution that contains the following projects:

    • AppStudio.Windows – project for Windows Store App
    • AppStudio.WindowsPhone – project for the Windows Phone App
    • AppStudio.Shared – shared project used for both target (it allow to reuse code)
    • AppStudio.Data – project that contains the model and data source.

    In AppStudio.Data project is possible to generate the class diagrams for understand the model and the data sources used in the application. This will be useful later.

    The Model
    The model contains all classes that represent the structure of the data used in the application.

    Each class has more properties (DefaultTitle, DefaultSummary, DefaultImageUrl and DefaultContent), that uses the properties showed in class diagram and these properties only helps the automation used by App Studio  , for this reason it was ignored.

    The data sources

    The data sources contains all classes that will provide the data that will fill the UI.

    Each data source has a LoadDataAsync method that is called by each target to fill the data in UI.

    Code

    See the changes made in default Menu App, here:Step 2: Add class diagrams and fixes issues related with nomenclature. Jump

    Note: It is possible to change any code in the solution, but it is not possible to upload those changes back to Windows App Studio  . Any change in AppStudio should be merged with changes made in Visual Studio.

    Requirements

    To use Azure Mobile Services it’s required to have an Azure Account and the Azure SDK should be installed.

    Azure Account

    Before starting the backend development it’s required to create an Azure account in the Azure Website  . For new users there is a trial version that can be used, see more about it here  .

    Azure SDK

    Get the Azure SDK and install it, from Downloads Jump .

    How to create the Azure Mobile Service in Azure Portal

    For see details about how to create an Azure Mobile Service in Azure Portal and how to create the Visual Studio 2013 project, see the article

    How to create the MyMenuApp Azure Mobile Service. 

    How to create the Azure Mobile Services

    In the topic, “Understanding the source code > the model” we saw the model used in the Menu App, and it is important for the services. Recap, the model has theses classes

    Each class represents specific information inside of the app and the service should allow us to store data for each class (this is the insert operation) and of course support for getting all items, get item by id, update an item and delete an item.Based in these classes, we will create the follow services

    • MainSchemaController – the service for managing the MainSchemas
    • SpecialOffersSchemaController – the service for managing the SpecialOffersSchemas
    • DessertsSchemaController – the service for managingthe DessertsSchemas
    • StartersSchemaController – the service for managing the StartersSchemas
    • BeveragesSchemaController – the service for managing the BeveragesSchemas

    Each service ends with the word “Controller” because this is a pattern from WebApi  , which is the base for Azure Mobile Services and starts with the name of the related object, normally called Dto  . Each developer should be aware of this, because these names will be used internally and if it does not match with the kind of pattern it can cause some problems in consuming the services, in serialization, or when client apps request specific queries.The data objects (Dtos) will be:

    • MainSchema
    • SpecialOffersSchema
    • DessertsSchema
    • StartersSchema
    • BeveragesSchema

    Each one will have the same structure as in the client app and they will inherit from EntityData which is the class from Azure Mobile Service that represents the system properties and are useful for offline mode.

    To simplify the sample, we will map the dtos to the database.

    Other solutions could be, to have the Dtos mapped with the model. This article, Build a service using an existing SQL database with the Mobile Services .NET backend  , shows an example that has Dtos and a model, which are mapped to each other and the model is related with the database.

    The first class created is a base class for the Dtos that contains shared properties.

    Represents a base class for all Dtos and it inherits from EntityData.

    
    	public class DtoBase : EntityData
    	{
    		   public string Title { get; set; }
    		   public string Subtitle { get; set; }
    		   public string Image { get; set; }
    		   public string Description { get; set; }
    	}
    
    

    The BeveragesSchema class

     
        
    	public class BeveragesSchema : DtoBase   {   }
    	
    

    The DessertsSchema class

    
    	public class DessertsSchema : DtoBase   {   }
    	
    

    The MainSchema class

    
    	public class MainSchema :  DtoBase   {   }
    	
    

    The StartersSchema class

    
    	public class StartersSchema : DtoBase   {   }
    	
    

    The SpecialOffersSchema class

    This class inherits from EntityData and not from the DtoBase because it does not share all properties like the others objects.

    
    	public class SpecialOffersSchema : EntityData
    	{
    		public string Dessert1 { get; set; }
    		public string Main1 { get; set; }
    		public string Starter1 { get; set; }
    		public string Title { get; set; }
    		public string Subtitle { get; set; }
    	 }
      
    

    Class Diagram

    Here are the class diagrams

    Note: All Dtos must be created inside the DataObject folder because when we create the table controller this object will be searched for there.

    Services

    Each service will be an Azure Mobile Service Table Controller
    http://www.saramgsilva.pt/wp-content/uploads/2014/09/090914_1243_MicrosoftsW13.png

    Each service should be defined as the following
    http://www.saramgsilva.pt/wp-content/uploads/2014/09/090914_1243_MicrosoftsW14.png

    The BeveragesSchemaController

    Which the output will be something like

    
    public class BeveragesSchemaController : TableController
    {
        protected override void Initialize(HttpControllerContext controllerContext)
        {
            base.Initialize(controllerContext);
            MobileServiceContext context = new MobileServiceContext();
            DomainManager = new EntityDomainManager(context, Request, Services);
        }
     
        //GET tables/BeveragesSchema1
        public IQueryable GetAllBeveragesSchema()
        {
            return Query();
        }
           
        // GET tables/BeveragesSchema1/48D68C86-6EA6-4C25-AA33-223FC9A27959
        public SingleResult GetBeveragesSchema(string id)
        {
            return Lookup(id);
        }
           
        //PATCH tables/BeveragesSchema1/48D68C86-6EA6-4C25-AA33-223FC9A27959
        public Task PatchBeveragesSchema(string id, Delta patch)
        {
            return UpdateAsync(id, patch);
        }
           
        // POST tables/BeveragesSchema1/48D68C86-6EA6-4C25-AA33-223FC9A27959
        public async Task PostBeveragesSchema(BeveragesSchema item)
        {
            BeveragesSchema current = await InsertAsync(item);
            return CreatedAtRoute("Tables", new { id = current.Id }, current);
        }
     
        //DELETEtables/BeveragesSchema1/48D68C86-6EA6-4C25-AA33-223FC9A27959
        public Task DeleteBeveragesSchema(string id)
        {
            return DeleteAsync(id);
        }
      
        }
    }
    
    

    For the other services, the process is the same, only change the dto selected and the name of the controller. Note: The service will be inside the Controllers folder.

    Class diagram

    After creating all service, we will have

    Validate data
    Each service should validate the input data, to make sure that the following operation will not fail because the input is wrong. For example, suppose that the input object wasn´t sent in the right way, for this reason the serialization fails, it can be a request with n null input, that is not correct and it will throw an NullReferenceException in our service. Other case is when a required property is not filled and the service try to save or edit it and then it throw an DbEntityValidationException

    Here is the code for insert a BeverageSchema that validate data

    
    		public async Task PostBeveragesSchema(BeveragesSchema item)
            {
              if (item == null)
              {
                  throw new ArgumentNullException("item");
              }
     
              if (string.IsNullOrEmpty(item.Title)
                  || string.IsNullOrEmpty(item.Subtitle)
                  || string.IsNullOrEmpty(item.Image)
                  || string.IsNullOrEmpty(item.Description))
              {
     
                  return BadRequest("There are properties that are not defined.");
     
              }
     
              var current = await InsertAsync(item);
              return CreatedAtRoute("Tables", new { id = current.Id }, current);
     
          }
    
    

    The same validation can be done in the others dtos.

    The DomainManager

    Each service has an Initialize method where it defined the DomainManager as following
    DomainManager =new EntityDomainManager(context, Request, Services);
    This property is an IDomainManager and is used internally for all operations in the database, in this case it will be an EntityDomainManager of Beverages Schema.

    The EntityDomainManager<T>

    This class is an implementation of IDomainManager that contains the implementation for all operations required for the database, using the MobileServiceContext.

    The MobileServiceContext

    This class represents the DbContext Jump for the services that allow us to do the CRUD operation in the database the easy way. This class will be used by EntityDomainManager. The MobileServiceContext was changed because when each service was created. It was also automatically added the following DataSets

    
    	public DbSet BeveragesSchemas { get; set; }
    	 
    	public DbSet DessertsSchemas { get; set; } public DbSet MainSchemas { get; set; }
    	 
    	public DbSet SpecialOffersSchemas { get; set; }
    	 
    	public DbSet StartersSchemas { get; set; }
    
    

    With this, we are mapping the Dto to the database. To understand more about this concept, which is related with Entity Framework, see the documentation about it,DbContext Class Jump .

    The Database

    There are different ways to create the database, it can be created manually in SQL Server Management Studio  in Visual Studio or can be created using Code First  .

    This article will use Code First, 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. Like was mentioned earlier, our model is defined by the Dtos class.

    When the project was created, we removed some code that was ignored, and with it we deleted the code related with the Database Initializer, that uses the classDropCreateDatabaseIfModelChanges   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.

    Are we sure that the model will not change in future?

    Maybe this is not the best solution for create the database. For create the database and prevent future changes in model (in our case the dtos), we will use Entity Framework Migrations  .

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

    Before we enable migration, we need to get the connection string to connect to the database and need to allow our IP to access the database.

    Defining the connection string

    The connection string must be defined in Web.config and can be a connection string from a local database, for local usage, or from the database created with the Azure Mobile Service.

    The connection string from MyMenuApp_db database can be find in the Portal  , by selecting the MyMenuApp Azure Mobile Service and then in Configure, as following

    And then scroll to

    In the project to go the Web.Config file and paste there the connection string

    Note: Later, in the deployment process, this value is overridden with the connection string defined in Azure Mobile Service.

    Defining the allowed IP for the database

    For localhost tests it is required to allow our IP to access to the database.we need to go to the Portal Jump , select the database MyMenuApp_db and then click in Dashboard, as following

    Scroll down until to find the following menu in the right

    And then allow the current IP

    Migration

    To have migration support we need to start with Enable-Migration. To do this, open the Tools menu in Visual Studio and then open the Package Manager Console

    Now, in the Package Manager Console enable migration and add migration as following

    The Migration folder was added to the project and it contains the configurations file and the migration V_0. This migration contains all information to create the database and the tables, it is used for revert the process if necessary.

    Seed

    In the Configuration file, it is possible to find the Seed method, that fills the database with some initial data or when there is any change in the model. it can be useful to update data in the database.

    In our case, we will use it to fill the database with the static data from the Menu App.

    Each data source in AppStudio.Data project, has the _data field that contains it. Here are the changed made

    Now we need to add the DbMigrator Jump  to the WebApiConfig.Register method, as following

    var migrator = newDbMigrator(newConfiguration());

    migrator.Update();

    s this that will create the database or will update the database following the current model.

    Be aware that the seed method is called each time this code runs!

    See more about it in this article How to make data model changes to a .NET backend mobile service  .

    Running the Services

    Now that the services are ready, we can run the services in localhost or in the server. For testing it could useful to use the localhost first.

    Localhost

    To run the services in localhost, press the key F5. The following page will be shown

    Click on “Try it out” to see the documentation page

    This documentation is generated automatically, it allows us to know what is available, and the type of requests and responses we can do. We can play a bit with this, by clicking on one option, let’s see one case

    Click on “try it out”, and the following window should appear

    This allows us to make a request to get all the special offers from the service. Attention, there are a limit of items we can get, the best practice is to request some items and will request more when they are needed (It is used for create pagination).Click in the “send” and we will get the following response

    This data is the data defined in Seed method.

    Server

    To deploy the services to the server, click on the project and open the context menu, then click on Publish as following

    The then select Microsoft Azure Mobile Services to associate the project to the MyMenuApp Azure Mobile Service.

    After it, click on the Publish button and then it is possible to follow the deployment in Web Publish Activity

    Our default browser will open a window with the service.

    If we click on “try it out”, it requires a key to access the documentation, that key is provided in the Portal  , by selecting the MyMenuApp Azure Mobile Service and then at the bottom has the option for it

    By clicking in the Manage Keys we will get

    How to see details about errors

    When we run the service, we get this windows

    That means there is a problem. To understand what happened we can use the Logs from the MyMenuApp for see the errors.

    Go to the Portal  , by selecting the MyMenuApp Azure Mobile Service and then click on Logs, as follows

    And then we get the list

    Select the error and click at the bottom on the “details” button, and we will see the error

    This error was made by adding an additional property in the model and wasn´t added the migration for update it in the database. See more about this subject in this articleTroubleshoot the Mobile Services .NET Backend Jump .

    How to do the remote debugging

    When we run the code in localhost we can debug Jump  the services, but in some cases it can be useful to debug the code that is running in the server, it is called remotedebugging. In the MyMenuApp Azure Mobile Service > Configure be sure you have this on

    Note: For how uses Visual Studio 2013 Update 2 or later it is turn on automatically In Visual Studio, open the View menu and the click in Server Explorer that will open the window as following

    Then expand the Mobile Service and select the MyMenuApp service and click in attach debugger

    The default browser will open the service and we shouldn´t close it until the debugging is done! Note: For it work we should deploy to the server using Debug Mode, in Release Mode it will not work. See more about it in Runtime Debugging Jump . Set the breakpoint and do the request, then the breakpoint will be hit

    Note: The services should be published as in debug mode.

    Code

    See the code created for AppStudion.Menu.Backend, here

    Step 4: Changes the BackEnd project: add the Dtos, add Services, changes the MobileServiceContext and add support for migrations. 

    Manage Images

    In Azure Mobile Service, is best practice is to use Azure Blob Storage   for saving the images used in the client apps. We only need to save, in the Azure SQL Database (MyMenuuApp_db), the image url.

    This article will not cover this topic. To see how to implement this feature see the following article Upload images to Azure Storage by using Mobile Services  . Which covers the topic for .Net Back End & Windows Store and .Net Back End & Windows Phone.

    How to consume the Azure Mobile Services in Menu App

    Now that we have the services running in the server, with the default data, is time to connect the Menu App with the MyMenuApp Azure Mobile services.

    There two ways to add the Azure Mobile Service Client: installing the Nuget Package or using Connected Service.

    Using Managed Nuget Pacakges

    Select the AppStudio.Data project, and then open the Manage Nuget Packages

    Install the Windows Azure Mobile Services Jump  nuget package like the following

    Is required to install this package in each target (Windows 8.1 and Windows Phone 8.1 apps).

    Using Connected Service

    Click in the target project, and with the mouse open the context menu, then click in “Add” and “Connect Service…”

    After it, login in using your Azure account and you will see your Azure Mobile Services as following

    By clicking in the MyMenuApp Azure Mobile Service we will add the Nuget package for Azure Mobile Service

    <package id="WindowsAzure.MobileServices" version="1.2.2" targetFramework="wpa81" />
    
    
    The MobileService<T>

    After it, create the MobileService<T> class in DataSource folder in the AppStudio.Data project, as following

    
    ///
    ///DefinestheMobileServicewrapper.
    ///
    ///ThetypeoftheDTO.
    publicsealedclassMobileService
    {
    	privatestaticMobileServiceClient_appStudioBackEnd;
    	  
    	///
    	///Getstheapplicationstudiobackend.
    	///
    	///Theapplicationstudiobackend.
    	privatestaticMobileServiceClientAppStudioBackEnd
    	{
    	get
    	{
    	return_appStudioBackEnd??(_appStudioBackEnd=newMobileServiceClient("",""));
    	}
    	}
    	  
    	///
    	///Initializesanewinstanceoftheclass.
    	///
    	publicMobileService()
    	{
    	Table=AppStudioBackEnd.GetTable();
    	}
    	  
    	///
    	///Getsorsetsthetable.
    	///
    	///
    	///Thetable.
    	///
    	publicIMobileServiceTableTable{get;set;}
    }
    
    
    Then for each data source do something like
    
    public async override Task> LoadDataAsync() 
    {
        var mobileService = new MobileService(); 
        return await mobileService.Table.ToListAsync(); 
    }
    
    
    After this, run the Menu App.

    Now the app has dynamic data that can be changed without changing the Menu App.

    How to create the Azure Mobile Service in Azure Portal

    Scope

    This article aims to show how to create an Azure Mobile Service in the portal and how to create the Visual Studio 2013 Project.

    This article complements the article

     Microsoft’s Windows App Studio Beta : Connecting a Menu App to Azure Mobile Service

    Creating the Azure Mobile Service

    Go to the Portal  , login and click on “+ New”, at the bottom

    Do the following steps

    The mobile service settings

    Then we will see the following screen that should be completed:

    Note: In this case we selected the free database with 20 MB, be sure that is enough. Each Azure account only can have one free database.

    The database settings

    In this screen, define all settings for the database

    At the end of this process we will have created the Azure Mobile Services and others related items.

    The MyMenuApp items created in Azure Account

    When the Azure Mobile Service is created, it will create in your account:

    • An Azure Mobile Service
    • The Notification Hub
    • The Database

    Here are the starting points for each.

    MyMenuApp Azure Mobile Service

    This is the start point for the Azure Mobile Service created.

    MyMenuAppHub-ns Notification Hub

    Each Azure Mobile Service automatically creates a Notification Hub.   This topic is outside the scope of this article.

    This is the start point for the Notification Hub related with Azure Mobile Service created.

    MyMenuuApp_db database

    This is the start point for the database related with the Azure Mobile Service created.

    By default this database is an Azure SQL Database, but the Azure Mobile Service supports MongoDb   database (NonSQL database), but this scenario is outside the scope of this article. For see more about this, see the following article

    Note: Azure SQL Database has differences from SQL Server Database, but in general the main features are supported.  

    How to create the Azure Mobile Service project for Visual Studio 2013

    There are two ways to have the Azure Mobile Service project:

    • Creating the project in Visual Studio, using Web or Could Template for Mobile Azure Service
    • Download the project from the Portal that is generated in MyMenuApp – Azure Mobile Service web page.

    Creating the project in Visual Studio

    Open the Visual Studio 2013, and create a new Web project, as seen below

    And then select the Azure Mobile Service template

    Here it is possible to define the project for “Host in the cloud”, but for now it will be ignored. Later the project will be associated with the MyMenuApp Azure Mobile Service.

    When the project is created it gives us some default classes for the To-do list sample, but it will be removed and ignored. More relative, in this article we will be creating similar classes for the Menu App.

    Here is the base project that we will use for the back end development

    At this moment, the project is completely empty, it only has

    • WebApiConfig – Defines some configuration for the WebApi
    • MobileServiceContext – Is the DbContext   for the services
    • Global.asax – This will called when the project is deployed
    • WebConfig – The configuration for the project will be in this file

    Code

    See the source code for the AppStudio.Menu.BackEnd, here

    Step 3: Add the base project for AppStudio.Menu.BackEnd. 

    Download the project from the Portal

    Go to the Portal Jump , and select the MyMenuApp mobile service

    And then click in “CREATE A NEW WINDOWS STORE APP” (could be other target), as following

    Because we have the default Menu App from App Studio  , it would be better to create the Azure Mobile Service using Visual Studio.

    Nuget Package​

    The Azure Mobile Service project, for .Net back end, by default is created using Entity Framework and the main Nuget package used in this kind of project is

    Microsoft Azure Mobile Services .NET Backend Entity Framework Extension 

    Note: If the project shows errors related with references it’s possible to remove this Nuget package and then install it again to restore references, in some cases it is required to close the project and open it again.

    Datatable design view for Azure SQL Database

    Scope

    This article has the goal to show a way to explore an Azure SQL Database, in especial the datatable design view.

    Introduction

    Microsoft SQL Server Management Studio   is a great tool for management our databases, but for how is using Azure SQL Database there is an “issue” we find in the first time we try to explorer the database. When we try to see a table in design mode we cannot do it, only is possible to use SQL script, what can be a slow process for how don´t have so much experience with SQL.

    Solution

    Visual Studio 2013 allow to explore SQL database and has feature for show de design view.

    Let’s see how it works for show an Azure SQL Database.

    First in Server explorer, we need to connect to Azure account and then selecting the SQL Database we should click in “Open SQL Server Object Explorer” like we can see in the follow image:

    open explorar

    After this, will be show a popup to connect to the database (you maybe need to set your IP in Azure Firewall)

    login

    Now we are connected to the Azure SQL Database:

    connected

    This sample uses a free database with two scheme (dbo and testforblob).

    Now we are available to see the data and see the table in design view, for it we should use the context menu:

    open design

    The result of “View Designer” is:

    design

    In this view we can edit the table definition using design view.

    And the result of “View Data” is

    data

    In this view we can change the data about orders that is a quickly way for it, without SQL.

    Conclusion

    In conclusion, Visual Studio 2013 has a great mode to explorer database and allow to have design view for Azure SQL Database what is very useful for management databases. And the other great thing is I not need to have Microsoft SQL Server Management Studio   installed in our machine (at least for simple and basic task!)

    See Also

    Microsoft SQL Server Data Tools