我個人偏愛用 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.


Comments

# by Jeffrey

to Jacky, 必須嚴防 INSERT 程式誤用 Guid.NewGuid(),若未遵守可能導致問題(大部人看到 Schema 是 Uniqueidentifier,直覺都會認為塞 Guid 沒錯),從防禦性設計觀點來看有點脆弱。我個人的話,應該會傾向用自動跳號當成叢集索引 + Guid Primary Key 的方式克服缺陷,讓系統設計意圖更明顯,不易被誤用。 https://blog.darkthread.net/blog/sequentialguidvaluegenerator/

# by Wing

我是建議用 Fluent API 設定 Entity 不要定義 SeqNo 而是用 Fluent API 定義 SeqNo 的陰影屬性,達到將 SeqNo 在實際運作視同不存在 builder.Property<long>("SeqNo") .IsRequired() .ValueGeneratedOnAdd() .HasComment("流水號") .HasColumnOrder(0);

Post a comment