I was caught out recently after I published an incorrect database migration into my cloud base Azure Mobile Service (I created a second controller based on the RealEstateProperty entity instead of the PropertyType entity). The upshot is that I only noticed this when all the properties of the entities I was synchronizing down from the cloud came back with null for most of their properties. Initially I thought my issue was with the migration I had performed on the database, so I thought I’d roll back to a previous version. My most recent migration was “201502260615263_Added proeprty type entity” and I wanted to roll it back to the previous migration, “201501061158375_AddedInspections”. To do this you can simply call the update-database method in the Package Manager Console:
update-database –TargetMigration “201501061158375_AddedInspections”
However, I wanted to invoke this only the database for the Mobile Service running in the cloud. To do this I need to add the –ConnectionString and –ConnectionProviderName attributes. The latter is easy as it needs to be the static value “System.Data.SqlClient” but the former requires two steps:
– In the Azure Management Portal go to the SQL Databases tab and then select the database that correlates to the Mobile Service. With the database selected, click “Manage” from the toolbar – this will prompt to add a firewall rule allowing access from your computer (this only happens the first time or again if your ip address changes). You need to add this firewall rule as Visual Studio will be attaching directly to the database to run the code-first migration on the database.
– From the Dashboard pane of the SQL Server database, select Connection Strings from the right link menu, and copy the contents of the ADO.NET connection string.
Now I can add the connection string to the update-database method:
update-database –TargetMigration “201501061158375_AddedInspections” –ConnectionString “Server=tcp:p7zzqmjcmf.database.windows.net,1433;Database=realestateinspector;User ID={my username};Password={your_password_here};Trusted_Connection=False;Encrypt=True;Connection Timeout=30;” –ConnectionProviderName “System.Data.SqlClient”
I checked that this had removed the PropertyType table (which was part of the migration I just reversed) and then removed the old migration file, “201502260615263_Added proeprty type entity.cs”, and then regenerated the new migration by calling add-migration again:
add-migration ‘Added proeprty type entity’
Given that the Mobile Service itself hadn’t changed at that point I figured that I’d simply call update-database without the TargetMigration parameter but with the ConnectionString that points to my actual Mobile Service. This seemed to go ok but then when I ran my Mobile Service and attempted to synchronize my PropertyType entities – this caused an exception because I’d discovered the root of my issue, which was I had two controllers both referencing the RealEstateProperty entity. I fixed that and republished my Mobile Service. Now synchronization worked, but mainly because there were no entities in the PropertyType table in the database, so I then attempted to add a PropertyType using the direct access (rather than synchronizing entities) in the MobileServiceClient (using GetTable instead of GetSyncTable) – this caused some weird exception as it seemed to require that the CreatedAt property be set. I’ve never had to do this on previous inserts, so I sensed something was wrong. Using the Visual Studio 2015 CTP I connected directly to the SQL Server database and sure enough on my PropertyType table there were no triggers for insert/update. Usually this is where the CreatedAt column is updated.
So, feeling a little puzzled I decided to undo my migration on my Mobile Service database once more. But this time, instead of attempting to change any of the migration scripts, all I did was republish my Mobile Service. Now when I attempted to add a PropertyType it worked, no problems. Checking with Visual Studio 2015, the trigger on the PropertyType table had been successfully created. At this point I’m not sure what exactly happens when the Mobile Service runs but it seems to do more than just applying the code-first migrations. It definitely seems to me that updating the cloud database using the package manager console seemed to skip the validation step that Mobile Services does in order to add the appropriate triggers, and thus should be avoided.