How to create a clustered index when using code-fi

问题:

I have a MessageModel with a timestamp attribute of when the message was created. I would like to make a clustered index on this long attribute. I hope to achieve a query speed-up when doing a query to get all messages newer than a certain timestamp. The timestamp value is never changed after creation. I currently have a regular index on a int Id attribute.

How can I add a clustered index on a models attribute using Entity Framework code-first migration in ASP.NET MVC 4.5?

回答1:

UPDATE: EntityFramework 6.1 introduced the [Index] attribute which can be used to specify an index on an arbitrary field. Example:

public class MessageModel 
{
    [Index(IsClustered = true, IsUnique = false)]
    public long Timestamp { get; set; }
}

For older versions of Entity Framework, I will leave my original answer below:

Unfortunately EF doesn't really provide much support for indexes. The best way to approach this depends on the type of migrations you're doing. If you're not using automatic migrations and you want to add an index to a NEW table (i.e. one you have a migration for), then you can modify the generated CreateTable statement in the migration to add indexes. This technique is used in one of the Entity Framework tutorials (search for Index to find it)

If you are using automatic migrations (or the table already exists) then you can still use old-style SQL to create your index - generate a migration, then modify your code to include a plain old CREATE INDEX statement.