How to initializing the code-first Entity Framework database in an Azure Mobile Services project

Question

I am utterly confused by the use of Entity Frameworks code-first migrations within Azure Mobile Services.

I'm following the article How to make data model changes to a .NET backend mobile service with additional help from Stack Overflow answers, and have also read Code First Migrations and Code First Migrations in Team Environments and watched Migrations - Under the Hood.

In Visual Studio 2015 I make a new Azure Mobile Service, and then enable code-first migrations by going to the nuget Package Manager Console (PMC) and running Enable-Migrations. I then build and run the project. Then to make the database I create an initial migration with the PMC command Add-Migration Initial and apply it with the PMC command Update-Database -Verbose -TargetMigration Initial.

This fails with the error message

Cannot create more than one clustered index on table 'MobileService1.TodoItems'. Drop the existing clustered index 'PK_MobileService1.TodoItems' before creating another.

As I used the Verbose flag I can see the auto-generated SQL and indeed plugging that into a query and running it against a freshly minted database produces the same error because the primary key already provides a clustered index.

CREATE TABLE [MobileService1].[TodoItems] (
    [Id] [nvarchar](128) NOT NULL,
    [Text] [nvarchar](max),
    [Complete] [bit] NOT NULL,
    [Version] rowversion NOT NULL,
    [CreatedAt] [datetimeoffset](7) NOT NULL,
    [UpdatedAt] [datetimeoffset](7),
    [Deleted] [bit] NOT NULL,
    CONSTRAINT [PK_MobileService1.TodoItems] PRIMARY KEY ([Id])
)
CREATE CLUSTERED INDEX [IX_CreatedAt] ON [MobileService1].[TodoItems]([CreatedAt])

However the article does warn me to make a change: replace Database.SetInitializer(new MobileServiceInitializer()); in MobileService1.WebApiConfig.Register with

var migrator = new System.Data.Entity.Migrations.DbMigrator(new Migrations.Configuration());
migrator.Update();

But after making that change I get exactly the same error when I run Update-Database -Verbose -TargetMigration Initial at the PMC.

Another suggestion, from Dominique Alexandre's comment on his question Running Azure Mobile Server project locally is to instead replace Database.SetInitializer(new MobileServiceInitializer()); in MobileService1.WebApiConfig.Register with

Database.SetInitializer(new MigrateDatabaseToLatestVersion<MobileServiceContext, Migrations.Configuration>());

But again I get exactly the same error.

What should I be using? Is there a simple way through Entity Frameworks code-first migrations as used in Azure Mobile Services?

Answer 1

The specific error that you're seeing is due to the fact that EF assumes primary keys are also clustered indexes and there's no way to signal that this isn't the case. Things work when you perform automatic migrations because when the app starts, Mobile Services/Apps registers a custom SqlGenerator that removes the clustered index (along with a few other important things). This custom SqlGenerator isn't used by migrations by default.

However, you can tell your migration to use this same SqlGenerator by specifying it in the Migrations\Configuration.cs file:

// Mobile Services namespace:
// using Microsoft.WindowsAzure.Mobile.Service.Tables

// Mobile Apps namespace:
// using Microsoft.Azure.Mobile.Server.Tables;

---<snip>---

    public Configuration()
    {
        AutomaticMigrationsEnabled = false;
        SetSqlGenerator("System.Data.SqlClient", new EntityTableSqlGenerator());
    }

Give that a try. The resulting db should include the CreatedAt trigger and other SQL-specific settings that Mobile Services/Apps expects. Let me know if you bump into issues doing this and I can look further.

Answer 2

After trying every solution I can find, , I found the magic is located in these lines (from the Quickstart project)

protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Add(
               new AttributeToColumnAnnotationConvention<TableColumnAttribute, string>(
                   "ServiceTableColumn", (property, attributes) => attributes.Single().ColumnType.ToString()));
            base.OnModelCreating(modelBuilder);
        }

(add to your context class)

This solved the clustered error problem. (I'm working with Azure Mobile Apps not Mobile Services)