indexing - 使用带有 GUID 主键的 Entity Framework Core 5.0.4 创建非聚集索引

Question

我有这个实体类:

public class StatusCode
{
    public const String ClassName       = nameof(StatusCode);
    [Key]
    public Guid         UniqueID        { get; set; } // Primary Key
    public Char         Level           { get; set; } // R=Root, C=Code Group, I=Item
    public Guid         CodeGroup       { get; set; } // F Key - Which group of codes this code belongs to (self reference to this table)
    public String       Code            { get; set; } // Public short form code, normal public use in tabular reports
    public String       FullName        { get; set; } // Full public name, normal public stand alone display
    public String       Description     { get; set; } // Description displayed to explain this code to the public, typically a full sentance
    public String       PublicRemarks   { get; set; } // Longer public remark about this code, typically a few paragraphs
    public String       PrivateRemarks  { get; set; } // Internal use only remark, for display to administrators about the usage of this code
    public Boolean      AvailableYesNo  { get; set; } // Flag to show if this code is available for usage on new records
    public DateTime     AvailableFrom   { get; set; } // Date time that this code is available for first use
    public DateTime     AvailableTo     { get; set; } // Date time that this code is no longer available for use
}

通过这次迁移:

protected override void Up(MigrationBuilder migrationBuilder)
{
      String lLogHdr = $"{ClassName}.{HCG_Misc.CallingMethodName()} ";
      Console.WriteLine( $"{lLogHdr} - Start" );
      migrationBuilder.CreateTable(
          name: "StatusCode",
          columns: table => new
          {
            UniqueID       = table.Column<Guid    >( type: "uniqueidentifier", nullable: false ),
            Level          = table.Column<string  >( type: "nvarchar(1)"     , nullable: false ),
            CodeGroup      = table.Column<Guid    >( type: "uniqueidentifier", nullable: false ),
            Code           = table.Column<string  >( type: "nvarchar(450)"   , nullable: true  ),
            FullName       = table.Column<string  >( type: "nvarchar(max)"   , nullable: true  ),
            Description    = table.Column<string  >( type: "nvarchar(max)"   , nullable: true  ),
            PublicRemarks  = table.Column<string  >( type: "nvarchar(max)"   , nullable: true  ),
            PrivateRemarks = table.Column<string  >( type: "nvarchar(max)"   , nullable: true  ),
            AvailableYesNo = table.Column<bool    >( type: "bit"             , nullable: false ),
            AvailableFrom  = table.Column<DateTime>( type: "datetime2"       , nullable: false ),
            AvailableTo    = table.Column<DateTime>( type: "datetime2"       , nullable: false )
          },
          constraints: table => { table.PrimaryKey("PK_StatusCode_UniqueID", x => x.UniqueID);
          });

      migrationBuilder.CreateIndex( name: "Index_StatusCode_Code"     , table: "StatusCode", column: "Code"      );
      migrationBuilder.CreateIndex( name: "Index_StatusCode_CodeGroup", table: "StatusCode", column: "CodeGroup" );
      Console.WriteLine( $"{lLogHdr} - Complete" );
}

当我运行时

PM> Update-Database

我得到一个带有聚簇主键索引的数据库表，这对于 GUID 索引来说太疯狂了。我需要做什么才能获得非聚集索引？

Answer 1

EF Core 中的原理是每个数据库提供者添加其特定的配置扩展方法。你没有说你的目标是什么数据库，所以假设它是 SqlServer，你需要的配置方法称为 IsClustered并扩展 key 配置 (HasKey)。

所以需要添加以下内容

modelBuilder.Entity<StatusCode>()
    .HasKey(e => e.UniqueID)
    .IsClustered(false);

并重新生成迁移。现在它应该包含这样的东西

table.PrimaryKey("PK_StatusCode_UniqueID", x => x.UniqueID)
    .Annotation("SqlServer:Clustered", false);

并且在应用时将创建非集群 PK。