sql server - How to stop EF4.1 Code-First to create Culstered index for the entity PK

Question

With the following simple entity class, EF4.1 Code-First will create Clustered Index for the PK UserId column when intializing the database.

    public class User
    {
        [Key]
        public int UserId { get; set; }
        public int AppId  { get; set; }
        public string UserName { get; set; }
    }

For performance sake, my design goals is to keep the generated Users table physical Clustered according to the AppId coulmn not to the PK.

On my Initializer class I've tried to manually drop the autogenerated PK clustered index and create whatever clustered index I need, but no clue here to predict the autogenerated name PK__Users__25518C17 for the index!

I'm new to Code-First world, and really don't know if there's any workaround for my design goals.

Thanks in advance

Answer 1

You don't need to predict name of auto generated index. You just need to select name of the index. For SQL server you can use query like:

SELECT I.Name
FROM sys.indexes AS I
INNER JOIN sys.tables AS T ON
    I.object_Id = T.object_Id
WHERE I.is_primary_key = 1 
    AND T.Name = 'Users'

Once you get the name in your custom initializer you can alter old index and create a new one.