When Entity Framework creates a database according to your Code First model, it applies some conventions that makes this process more intelligent. For example, it can recognize primary key, set a relationships between entities (1:1, 1:many, many:many), and so on. The full list is available on MSDN article System.Data.Entity.ModelConfiguration.Conventions Namespace. Currently (January 2016) there’s 46 conventions.
In my opinion Entity Framework makes some unnecessary work with all these conventions. So it would better to turn off some of them, and now I’ll explain why I think so.
EF creates indexes for every foreign key field. For example, if we have a Client and Phone entities and the last has a FK to Client, we’ll get an additional index on Phones table.
Is it good in all cases? I don’t think so. It depends on data in these tables and how you query them. Maybe this index would never be used by SQL Server, or you would have too much indexes that can negatively impact your OLTP performance.
To understand the problem better you can watch video recorded by Kimberly L. Tripp about SQL Server index internals, indexing strategies and analysis available in TechNet SQL Server Video Archive.
EF creates foreign keys and automatically sets cascade delete. Just imagine if you have a Product entity that has a foreign key to Category entity, and someone accidentally delete a category record. SQL Server immediately deletes all product records belonging to that category. That’s a rather dangerous option, and I suppose it could be turned on with great precaution and only in those cases where it really needed.
My opinion is to turn them off, and if you really need them, let’s revive them. To do this you need to find your database context class, it’s derived from DbContext class, and place some lines of code there;
- Add a reference to Conventions namespace
- Override OnModelCreating method
protected override void OnModelCreating(DbModelBuilder modelBuilder)