在 EF Core Model 定義 GUID 非叢集 Primary Key
6 | 3,515 |
我個人偏愛用 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
有幾個重點:
- PK 之外增設 SeqNo INT,以 IDENTITY(1,1) 設定自動跳號
- FlowId 為 GUID 是 MiniFlow 資料表的 Primary Key,但設定時加註 NONCLUSTERED 指定為非叢集索引
- 利用 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 Jacky
可以考慮 Sequential Guid generator. https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.valuegeneration.sequentialguidvaluegenerator?view=efcore-3.1 https://docs.abp.io/en/abp/latest/Guid-Generation
# by Jeffrey
to Jacky, 必須嚴防 INSERT 程式誤用 Guid.NewGuid(),若未遵守可能導致問題(大部人看到 Schema 是 Uniqueidentifier,直覺都會認為塞 Guid 沒錯),從防禦性設計觀點來看有點脆弱。我個人的話,應該會傾向用自動跳號當成叢集索引 + Guid Primary Key 的方式克服缺陷,讓系統設計意圖更明顯,不易被誤用。 https://blog.darkthread.net/blog/sequentialguidvaluegenerator/
# by LEMON_TEA
請問 Hilo 對這個場景會有任何幫助嗎? https://learn.microsoft.com/zh-tw/dotnet/api/microsoft.entityframeworkcore.sqlserverpropertybuilderextensions.usehilo?view=efcore-7.0
# by Jeffrey
to LEMON_TEA,幫補充範例 https://medium.com/ricos-note/use-hilo-algorithm-to-make-primary-key-929e3d19b2a 缺點:依賴 DB 產生,限定特定資料庫,無法在客戶端離線狀態生成
# by Lik
用了相同的方法,在update時候出現 An unhandled exception occurred while processing the request. SqlException: Cannot update identity column 'SeqNo'. 然後用了以下方法解決 builder.Property(x => x.SeqNo).Metadata.SetAfterSaveBehavior(Microsoft.EntityFrameworkCore.Metadata.PropertySaveBehavior.Ignore); 不知道是不是正確做法。 參考自https://stackoverflow.com/questions/39176018/cannot-update-identity-column-in-entity-framework-core
# by Wing
我是建議用 Fluent API 設定 Entity 不要定義 SeqNo 而是用 Fluent API 定義 SeqNo 的陰影屬性,達到將 SeqNo 在實際運作視同不存在 builder.Property<long>("SeqNo") .IsRequired() .ValueGeneratedOnAdd() .HasComment("流水號") .HasColumnOrder(0);