How to change a clustered index in Entity Framework 6.1 Code First model and apply it to an Azure database

Question

Using the Entity Framework 6.1 code first model, what is the best way to go about changing the clustered index on a table from the default ID to another set of columns. Azure doesn't allow a table without a clustered index.

  public partial class UserProfile 
  {
    public override Guid ID { get; set; }

    [Index( "CI_UserProfiles_UserID", IsClustered = true)]
    public Guid UserID { get; set; }

    [Required]
    public Guid FieldID { get; set; }

    [Required]
    [StringLength(400)]
    public string Value { get; set; }
 }

On the table UserProfiles, ID is already the primary key and clustered index. Adding

[Index( "CI_UserProfiles_UserID", IsClustered = true)] 

to UserID creates this migration:

CreateIndex("dbo.UserProfiles", "UserID", clustered: true, name: "IX_UserProfiles_UserID");

Executing the migration generates the following error:

Cannot create more than one clustered index on table 'dbo.UserProfiles'. Drop the existing clustered index 'PK_dbo.UserProfiles' before creating another.

Answer 1

To solve your problem, after you generate your migration file, you must modify the generated code by disabling clustered index for your primary key by assigning false as a value of clustered parameter of PrimaryKey.

After your modifications you must have something like this into your migration file:

CreateTable(
    "dbo.UserProfiles",
    c => new
        {
            Id = c.Guid(nullable: false),
            UserID = c.Guid(nullable: false),
            FieldID = c.Guid(nullable: false),
            Value = c.String(nullable: false, maxLength: 400),
        })
    .PrimaryKey(t => t.Id, clustered: false)
    .Index(t => t.UserID, clustered: true, name: "CI_UserProfiles_UserID");

This is not done in OnModelCreating method by using Fluent API like Manish Kumar said, but in migration file. The file that is created when you use Add-Migration command.

Existing Database

As you say in comments, your database already exist. After executing Add-Migration command, you will have this line on your DbMigration file in your Up() method:

public override void Up()
{
    CreateIndex("dbo.UserProfiles", "UserID", clustered: true, name: "CI_UserProfiles_UserID");
}

You must modify the Up() method to have this code:

public override void Up()
{
    this.Sql("ALTER TABLE dbo.UserProfiles DROP CONSTRAINT \"PK_dbo.UserProfiles\"");
    this.Sql("ALTER TABLE dbo.UserProfiles ADD CONSTRAINT \"PK_dbo.UserProfiles\" PRIMARY KEY NONCLUSTERED (Id);");
    this.CreateIndex("dbo.UserProfiles", "UserID", clustered: true, name: "CI_UserProfiles_UserID");
}

In the code above I assumed that the created clustered index is named PK_dbo.UserProfiles in your database. If not then put at this place the correct name.

Answer 2

This is truly an area where EntityFramwork (Core) had to advance and it still is hard.

So, I could not use IsClustered(false) for my GUID / string Primary keys, for the simple reason, the project having DbContexts was DB - agnostic. So you needed to Add EntityFrameworkCore.SqlServer and IsClustered is available then, and only.

So, my solution was simple. Add no nuget package but this attribute. This ONLY works on EF Core.

I have tested this on SQL. Though, not sure if the other providers would allow this string not having any meaning. (e.g. SQLite does not know clustered indexes)

 p.HasKey(k => k.Id).HasAnnotation("SqlServer:Clustered", false);