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.
OneToManyCascadeDeleteConvention
ManyToManyCascadeDeleteConvention
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
|
1 |
using System.Data.Entity.ModelConfiguration.Conventions; |
- Override OnModelCreating method
|
1 2 3 4 5 6 |
protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Conventions.Remove<ForeignKeyIndexConvention>(); modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>(); modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>(); } |
Actually I’d remove a lot more, like:
– auto relationship (entity) discovery
– auto column (property) discovery
…
Indexes on foreign keys are used to enforce the foreign key constraints. If you remove a record in a table that is used in a foreign key constraint on another table, the server would have to do a table scan to find if any records there that use that key.
But when you have a very small table, there’s no good to have an index on that table. For example, you have a large table with persons, where you mark the world continent from the second table. Just 6 records. So you don’t need an index here.
I prefer to disable automatic index creation in EF, and create them manually when needed.