During the last months I have worked with an API backend for a mobile application. Azure Mobile Service with a .Net backend is our infrastructure platform.  There was a lot of struggling to get Entity Framework Database Migration work the way I wanted it to work. But the solution was quite easy, but needed some few changes that I think shouldn’t be needed at all.

By default when creating an Azure Mobile Service Backend with Entity Framework, the Automatic migration is turned on. In my case I don’t want the model and the tool take control over my database schema and migration, so I turned off that feature. It was now the adventure started ;)

How Entity Framework Database Migration handles database schema

By default Entity Framework uses “dbo” as the default database schema. So the user that logins to the database when a Migration should be applied need permissions to that schema. When creating an Azure Mobile Service a random SQL Server user will be created and added to the associated database. That user doesn’t have permissions to the “dbo” schema, but to another schema that is based on the name of the Azure Mobile Service name. If you create an Azure Mobile Service with the name “mymobile-test”, the SQL Server user will have permissions to a schema with the name “mymobile_test”. That means that you need to change the Entity Framework default schema to “mymobile_test”. This can be done by overriding your DbContext’s OnModelCreating method, and use the DbModelBuilder’s (an argument to the OnModelCreating method) HasDefaultSchema method:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
   var schema = ServiceSettingsDictionary.GetSchemaName();

   if (!string.IsNullOrEmpty(schema)
      modelBuilder.HasDefaultSchema(schema); 
}

The code above will be part of a DbContext created by the Azure Mobile Service project template in Visual Studio. The ServiceSettingsDictionary.GetSchemaName will take the value of the appSettings “MS_MobileSeriveName” and use it as the default schema.

Note: If you name your Azure Mobile Service project in Visual Studio to a different name than your Azure Mobile Service in Azure, your local code will use the project name as your mobile service name, but in Azure the name you gave the Azure Mobile Service. So when you add a migration with the “Add-Migration” command, the Entity Framework will use your local service name as the default schema. It will give you permission denied when you publish your code to Azure and run the migrations.

I named the Azure Mobile Service in Azure with the name “mymobile-test”, but in Visual Studio I call my project “My.Mobile”. When I added a migration it added the My.Mobile as a schema to the migration file and also set the DefaultSchema in the migration’s .resx file to “My.Mobile”. The migration worked fine on my dev machine, but when my code was published to the Azure, I got permission denied against the schema “My.Mobile”. I removed the schema from my migration files but left the DefaultSchema in my migration’s .resx file. Now things started to work better, BUT! Because the default schema set to the DbContext is different from the one in the .resx file, the migrations will fail because Entity Framework notice a different between the schemas, it want me to run “Add-Migration” again to get the new model changes. In this case a schema change. The reason why I can’t use the “mymobile-test” as the schema name in my migrations files, is because I have two environment, test and production, they uses different names and the SQL server user for those have permissions to its own schemas. So that gives me more problem when it comes to migrations. The main problem is the migrations .resx file and its DefaultSchema key. If Entity Framework will just ignore it everything should work great by just setting the DbModelBuilder’s default schema to the name of the Azure Mobile Service.

How did I fix the problems with database migrations?

I removed the code that will set the DbModelBuilder’s default schema to the Azure Mobile Service name from the DbContext OnMdelCreating method:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
   //var schema = ServiceSettingsDictionary.GetSchemaName();

   //if (!string.IsNullOrEmpty(schema)
       //modelBuilder.HasDefaultSchema(schema);
}

 
Now Entity Framework will use “dbo” as default. But the Azure Mobile Service user against the SQL Server doesn’t have permissions to the “dbo” schema, so I just gave the user permission to it.

GRANT CONTROL ON SCHEMA::[dbo] TO [The Generated User Login]

I did the same thing to the production SQL Server for my Azure Mobile Service too.  I can now add migrations without worry about permission problems and schemas based on the Azure Mobile Service name.

My next blog post will be about how to disable Automatic Migrations and take control over the migrations.

Please feel free to follow me on twitter if you want to know when I publish more blog posts. @fredrikn