Azure SQL: will a pointer apply index changes to EF migration?

Question

I am using EF 6 Code First for my MVC website and Azure SQL Firewall. The Azure Portal SQL page has a number of index recommendations that you can click to apply. However, I am not sure of the implications for my EF data model. I know that if I were to add or delete tables, fields, etc. From the database directly, then EF will complain that my model and the database are not synchronized, and everything is going badly. What about indexes? If I let Azure automatically add the recommended index, will EF know about it? Will this cause problems?

Answer 1

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

EF does not have to be aware of this, and it will not cause any problems, it is a query optimizer that uses these indexes if they are useful. All index recommendations should be taken with plenty of salt and should only be applied after a thorough evaluation.

Answer 2

In my experience, yes, index recommendations can cause Entity Framework migration issues. They will not affect the Entity Framework themselves, it will happily connect to the database, even if it does not know anything about indexes, but you may run into 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 reorganize this table and create a migration that deletes the column. Attempting to apply this migration may fail because the index (which does not know about EF) is column dependent.

This scenario is probably unlikely, but it can move you after you forget about applying the recommendation. This is an even bigger problem if you let Azure automatically apply these guidelines for you. Worst of all, you probably will not encounter these problems until you try to deploy them to production - you will not see them in intermediate / test environments, because the usage patterns will be different, and therefore the recommendations themselves on the index may differ .