Azure SQL : Will Applying Index Advisor Changes Impact EF Migrations?

Question

I am using EF 6 Code First for my MVC website and Azure SQL backend. The Azure Portal SQL page has a number of Index Recommendations that can be applied with a click. However, I am not sure of the implications on my EF data model. I know that if I were to add or remove tables, fields, etc from the database directly, then EF will complain that my model and the DB are out of sync and things go bad. What about indexes? If I let Azure automatically add a recommended index, will EF even know about it? Will it cause problems?

Answer 1

In my experience, yes the Index Recommendations may cause problems with Entity Framework migrations. They won't affect Entity Framework itself, it will happily connect to the database even though it knows nothing about the indexes, but you may hit problems when it comes time to apply new migrations.

As an example, you use the Azure portal to apply a new recommended index to one of your tables. At a later stage you refactor this table and create a migration which removes a column. This migration could fail when you try to apply it, because the index (that EF does not know about) is dependent on the column.

This scenario is perhaps unlikely but it could trip you up after you've forgotten about applying the recommendation. It's even more of an issue if you let Azure automatically apply these recommendations for you. Worst of all, you probably won't hit these issues until you try and deploy to production - you may not see them in staging/test environments as the usage patterns will be different and hence the index recommendations themselves may be different.

Answer 2

What about indexes? If I let Azure automatically add a recommended index, will EF even know about it? Will it cause problems?...

EF need not know about it and it wont cause any problems,its the query optimizer which uses those indexes if they are usefull.All index recommendations should be taken with a pinch of salt and should be applied only after carefull evaluation.