... you see everything as a nail. I'm usually not the one to cite clichés, but for this one I make an exception. It is very, very true, especially in the software engineering business. If all you know is foreach, you don't use SelectMany. If you only know the one overload of Select, you'll revert back to for loops if you need the index in the projection expression. If you don't know row-level security, you'll implement it using other methods... and I could go on.

Well, I guess I fell into that same category. It was a while back that this question caught my eye on stackoverflow. The gist of the question is: how can you prevent EF CF from creating non-clustered indices on foreign keys?

Well, I answered it using a custom SQL generator and a custom C# migration scaffolder. I even blogged about it. The answer on SO is pretty detailed, my answer was accepted, I even got two upvotes. And I stand by that answer — all in all, it was awesome. Very :)

Here's the problem: it just felt a little hacky. But I'm really into custom migration scaffolders and SQL generators. Mostly because they are very fun to write and use. But, if I'm being honest, there is just not many problems out there in the industry that should be solved with that. And when I saw that question, my mind went right there; wow, this is it!

Well, it wasn't. I should have been more careful and thought more about the problem before submitting my answer.

So the question is about changing the generated database. How does EF generates the database? How does it know the PK, or the SQL types of fields? Conventions. Conventions drive the process of generating the database. So if I want to change how the DB is generated, maybe I should create a new convention? Don't think so; it's not that I want to have something that's not there. I don't want to have something, that's already there. OK, so maybe I should remove a convention?

And if I had cared enough to check the list of default conventions, I would have found ForeignKeyIndexConvention. And sure enough, if you simply remove that convention in the OnModelCreating overload from the modelbuilder, no indices are generated on the FK:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
  base.OnModelCreating(modelBuilder);
  modelBuilder.Conventions.Remove<ForeignKeyIndexConvention>();
}

Lesson learned :)