在 EF Core Model 定義 GUID 非叢集 Primary Key

我個人偏愛用 GUID 當 Primary Key，但為避免索引破碎引發效能災難，我慣用的解法是另設自動跳號欄位當叢集索引(Clustered Index)。(延伸閱讀：GUID Primary Key 資料庫避雷守則)

因此，一個理想的 CREATE TABLE 範例會像這樣：

CREATE TABLE [dbo].[MiniFlow](
     [SeqNo] [int] IDENTITY(1,1) NOT NULL,
     [FlowId] [uniqueidentifier] NOT NULL,
     [FormCode] [varchar](4) NOT NULL,
     [FormNo] [varchar](16) NOT NULL,
     [Subject] [nvarchar](256) NOT NULL,
    CONSTRAINT [PK_MiniFlow] PRIMARY KEY NONCLUSTERED
    (
         [FlowId] ASC
    )
)
GO
CREATE CLUSTERED INDEX [IX_MiniFlow] ON [dbo].[MiniFlow]
(
     [SeqNo] ASC
)
GO

有幾個重點：

1. PK 之外增設 SeqNo INT，以 IDENTITY(1,1) 設定自動跳號
2. FlowId 為 GUID 是 MiniFlow 資料表的 Primary Key，但設定時加註 NONCLUSTERED 指定為非叢集索引
3. 利用 CREATE CLUSTERED INDEX 將 SeqNo 建為叢集索引

進入 EF Core 時代，要如何定義 Model 讓系統產生上述資料表結構？

經過研究，EF Core 新版本支援透過 [Index] (EFCore 5.0+ 支援 及 [PrimaryKey] (EFCore 7.0+ 支援) ，但若要指定叢集索引，仍需使用 Fluent API HasKey()、IsClustered()... 等。(註：EFCore 7+ 支援 IsDescending())

依據 Schema 要求調整 Model 並寫一小段程式測試：

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

var cs = "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=MiniFlowDb;Integrated Security=True;";
var options = new DbContextOptionsBuilder<MyDbContext>()
    .UseSqlServer(cs)
    .LogTo(Console.WriteLine, LogLevel.Information)
    .Options;
var dbctx = new MyDbContext(options);
dbctx.Database.EnsureDeleted();
dbctx.Database.EnsureCreated();
Console.ReadLine();


public class MyDbContext : DbContext
{
    public DbSet<MiniFlow> MiniFlows { get; set; }

    public MyDbContext(DbContextOptions<MyDbContext> options) : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<MiniFlow>()
            .HasKey(o => o.FlowId)
            .HasName("PK_MiniFlow").IsClustered(false);
        modelBuilder.Entity<MiniFlow>()
            .HasIndex(o => o.SeqNo)
            // EF7+ supports .IsDescending()
            .HasName("IX_MiniFlow").IsClustered(true);
    }
}

[Table("MiniFlow")]
public class MiniFlow
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Column(Order=0)] // 指定順序，否則 FlowId PK 會排在第一欄
    public int SeqNo { get; set; }
    [Column(Order=1)]
    public Guid FlowId { get; set; } = Guid.NewGuid();
    [Required]
    [MaxLength(4)]
    public string FormCode { get; set; }
    [Required]
    [MaxLength(16)]
    public string FormNo { get; set; }
    [Required]
    [MaxLength(256)]
    public string Subject { get; set; }
}

實測產生的 CREATE TABLE Script 如下，算是完整還原原本的 Schema 要求：

CREATE TABLE [MiniFlow] (
  [SeqNo] int NOT NULL IDENTITY,
  [FlowId] uniqueidentifier NOT NULL,
  [FormCode] nvarchar(4) NOT NULL,
  [FormNo] nvarchar(16) NOT NULL,
  [Subject] nvarchar(256) NOT NULL,
  CONSTRAINT [PK_MiniFlow] PRIMARY KEY NONCLUSTERED ([FlowId])
);
CREATE CLUSTERED INDEX [IX_MiniFlow] ON [MiniFlow] ([SeqNo]);

如此，使用 Code First 也能達成期望的自動跳號叢集索引 + GUID Primary Key 設定了。

小心得是 EF Core 的發展速度比預期慢一些，像是 [PrimaryKey]、IsDescending() 到 7.0 才支援，開發時升到最新版本會較好用，必要時要有自己找 Workaround 的心理準備。

Tutorial of creating identity clustered index + GUID primary schema with EF Core model.