How to change a clustered index in Entity Framewor

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

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 EFCORE.

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);

Answer 2

You need to remove the existing clustered index from your current PK 'ID' which is created by default for any "KEY" property in code first. It can be done using fluent API:

.Primarykey(x=>x.ID,clustered:false)

Once existing clustered index is removed from ID, your migration to add the clustered index on UserID should run smoothly.

Answer 3

After the migration file is created, modify the generated code, disabling the clustered index for the primary key by setting the clustered property to false.

Being that Azure does not allow a table without a clustered index, and there is no utility in SQL Server to 'change' a clustered index on a table, it is necessary create a new table with the clustered index and migrate the existing data to it. The code below renames the original table, migrates the data to the new table that was created with the new clustered index and drops the original table.

        RenameTable("dbo.UserProfiles", "UserProfiles_PreMigrate");

        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");

        Sql(@"
            INSERT [dbo].[UserProfiles]
            (ID,
             UserID,
             FieldID,
             Value)
            SELECT
             ID,
             UserID,
             FieldID,
             Value
            FROM dbo.UserProfiles_PreMigrate

        ");

        DropTable("UserProfiles_PreMigrate");

Any existing table constraints will be lost in this operation, so it will be necessary to recreate and indexes,foreign keys, etc on the table.

Answer 4

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.