如何在Entity Framework 6.1 Code First模型中更改聚簇索引并将其应用于Azure数据库 [英] How to change a clustered index in Entity Framework 6.1 Code First model and apply it to an Azure database

问题描述

使用Entity Framework 6.1代码优先模型，将表上的聚簇索引从默认ID更改为另一组列的最佳方法是什么. Azure不允许没有聚簇索引的表.

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

在表UserProfiles上，ID已经是主键和聚集索引.添加

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

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

到UserID会创建此迁移:

to UserID creates this migration:

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

执行迁移会产生以下错误:

Executing the migration generates the following error:

不能在表dbo.UserProfiles上创建多个聚集索引.删除现有的聚集索引 "PK_dbo.UserProfiles"，然后再创建另一个.

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

推荐答案

要解决您的问题，在生成迁移文件后，必须通过将false分配为PrimaryKey的clustered参数值.

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

这不是通过使用Manish Kumar所说的Fluent API在OnModelCreating方法中完成的，而是在迁移文件中完成的.使用Add-Migration命令时创建的文件.

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.

现有数据库

正如您在评论中所说，您的数据库已经存在.执行Add-Migration命令后，您将在Up()方法中的DbMigration文件上显示以下行:

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

您必须修改Up()方法以具有以下代码:

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

在上面的代码中，我假设创建的聚集索引在您的数据库中名为 PK_dbo.UserProfiles .如果没有，请在此处输入正确的名称.

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.

这篇关于如何在Entity Framework 6.1 Code First模型中更改聚簇索引并将其应用于Azure数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！