How to initialize the first Entity Framework database in an Azure Mobile Services project

Question

I am completely confused about using the primary Entity Frameworks migration in Azure Mobile Services.

I follow the article How to make data model changes to the .NET backend mobile service with additional help from Stack Overflow answers, and also read the First steps of code migration and First code changes in command environments and looked at Migrations - under the hood .

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

This fails with error message

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

Since I used the Verbose flag, I can see the automatically generated SQL and really connect this to the query and run it against the newly minted database, it gives 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 warns me of changes: replace Database.SetInitializer(new MobileServiceInitializer()); on MobileService1.WebApiConfig.Register on

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

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

Another suggestion from Dominique Alexandre to comment on his question: Run the Azure Mobile Server project locally instead replace Database.SetInitializer(new MobileServiceInitializer()); on MobileService1.WebApiConfig.Register on

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

But again, I get exactly the same error.

What should i use? An easy way to transfer Entity Frameworks code names used in Azure Mobile Services?

Answer 1

The specific error you see is related to the fact that EF assumes that primary keys are also clustered indexes, and there is no way to report that this is not the case. Everything works when you perform automatic migration, because when the application starts, Mobile Services / Apps registers a custom SqlGenerator that removes the clustered index (along with a few other important things). This custom SqlGenerator is not used by migrations by default.

However, you can tell your migration to use the 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 it a try. As a result, db should include the CreatedAt trigger and other SQL-specific settings that Mobile Services / Apps expects. Let me know if you run into problems and I can look further.

Answer 2

After every solution I can find, I found that the magic is 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 cluster error issue. (I work with Azure Mobile Apps, not mobile services)

Answer 3

I just tested this and I also get this error when using Update-Database in PMC.

I use Azure Mobile Apps, not services, but this is the same Entity-Data.

Starting from an empty table, only the Initial Migration file, Configuration.cs and the MigrateDatabaseToLatestVersion bit works for me. Then you can simply start the application with F5 instead of Updating the database .

He must create your circuit with your seed.