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?
2 Answers
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.
Related