EF CodeFirst create non-clustered primary key index

Question

I'm using EF 4.1 CodeFirst to create my DB. It seems that EF is creating all primary keys with clustered index, which is not optimal for us in one case(possibly more cases). Is there a way to tell EF to generate this table with primary key as a non-clustered index ?

Of course we could do it manually using custom script after database is already created, but we have a lot of foreign keys pointing to this table, it would be quite a non-optimal solution if there is a better, more straight forward way to do the same already during DB creation. Any help appreciated

Answer 1

No there is no way to control SQL code first generates - it is code first and its current philosophy (the bad one actually) is: you know nothing about the database and you don't bother.

The only way to change this behavior is to write custom database initializer, use custom database queries to search for created index, remove that index and create a new one. Some references:

• How to stop EF4.1 Code-First to create Culstered index for the entity PK
• Custom initializer with adding an index
• Example of more advanced initializer

The initilizer will make your solution dependent on the concrete database server product.

Answer 2

Yes there is a way you can specify not to create Clustered Indexes. If you use code migrations, a *.cs file is generated in order to populate your DataBase. In this file you can specify apart from the Primary key for each table, if it needs to be created with clustered indexes.

  .PrimaryKey(t => t.MID, null, true)