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

Selfpropelled

IMO this is why DB First was invented. – Apprehend

@Tridus, afaik Model First was "first" :) and of course with model first it would be piece of cake, but Code First has advantages which i find "nicer/cleaner", although it's now very far from perfect as seen also on this example here. I would call it lack of supplied customization so far. – Selfpropelled

It's cleaner except when it's not. :) For stuff like this DB First is really nice because you simply do whatever you need to do on the database side, then tell EF "hey generate some POCOs out of that". There's really no reason EF or your code should need to know anything about what type of indexes are being used on the server side. – Apprehend

You can vote for this feature here: connect.microsoft.com/VisualStudio/feedback/details/624575/… – Inunction

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.

Shovelnose

@LadislawMmka. "The initilizer will make your solution dependent on the concrete database server product." - this is only the case if you write it for a specific product. There's no reason why this needs to be restricted to a particular provider. I think what you really mean is "this will most likely...". – Loats

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)

Taxpayer

This is the easiest way to achieve a nonclustered index in the current version. It does require the use of code migrations. – Johnathan

PrimaryKey method doesn't even exist in EF 6.1 – Friel

@triynko You'll see it being called chained after the CreateTable method in the code first migration class that is generated for a table creation. The method is in the TableBuilder class in namespace System.Data.Entity.Migrations.Builders. – Luciana