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

Question

Using the first Entity Framework 6.1 code model, the best way is to change the cluster index in the table from the default identifier to another set of columns. Azure does not 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; } } 

In the UserProfiles table, the ID already a primary key and a clustered index. Adding

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

for UserID creates this migration:

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

Migration creates the following error:

It is not possible to create more than one clustered index in the table "dbo.UserProfiles". Drop the existing clustered index 'PK_dbo.UserProfiles' before creating another.

Answer 1

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

After your changes, you should have something like this in the 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 the OnModelCreating method using the Fluent API, for example Manish Kumar, but in the migration file. File created using the Add-Migration command.

Existing database

As you say in the comments, your database already exists. After executing the Add-Migration command, you will have this line in 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 above code, I assumed that the created clustered index in your database is called PK_dbo.UserProfiles . If not, put the correct name on this place.

Answer 2

You need to remove the existing clustered index from your current PK identifier, which is created by default for any "KEY" property in the code. This can be done using the free API:

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

After the existing cluster index is removed from the identifier, your migration to add a clustered index to UserID should be performed smoothly.

Answer 3

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

Since Azure does not allow a table without a clustered index, and there is no utility in SQL Server to “change” a clustered index in a table, you must create a new table with a clustered index and transfer the existing data to it. The code below renames the original table, transfers the data to the new table created with the new clustered index, and deletes 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"); 

In this operation, any existing table restrictions will be lost, so the index will need to be recreated in the table as well, foreign keys, etc.