預設,SQL Server 會幫我們把 PK 設為 Clustered,當 PK 是 GUID type(uniqueIdentifier) 時,有一些注意事項
- 使用具有順序性的 GUID,避免索引破碎;我習慣自己產生,不使用 SQL 提供的Store Procedure 的 NEWSEQUENCEID 和 NEWID
- 設為 Non-Cluster
原本以為用 Code First 要把 PK 設為 Non-Cluster 很簡單,沒想到還是卡關
如果你跟我一樣,使用 Code First,PK 是 GUID,請看我的解法...
開始之前請讀
James Fu 有天在 FB 上跟我們討論 PK 使用 GUID 的問題,接著他也把案例分享在 Blog 上
https://dotblogs.com.tw/jamesfu/2016/01/18/guid_1
https://dotblogs.com.tw/jamesfu/2016/01/20/guid_2
緊接著,黑大也發了一篇
http://blog.darkthread.net/post-2016-01-29-guid-as-pk-on-db.aspx
開發環境
- Windows 10 Enterprise x64 CHT
- VS 2015 Update 2 Eng
- Entity Framework 6.1.3
開始演練
用 Code First 建立 Index 很簡單,只要在欄位上用 IndexAttribute
PK 把 IsClustered = false,應該就能把 PK 設為 NonCluster
[Table("Department")]
public class Department
{
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
[Key]
public Guid Id { get; set; }
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
[Index(IsClustered = true)]
public int SequentialNo { get; set; }
}
調用端清掉資料庫,然後重新建立資料庫
[TestMethod]
public void DropCreateHrDatabaseAlways_Test()
{
Database.SetInitializer(new DropCreateDatabaseAlways<MyDbContext>());
using (var dbContext = TestUtility.CreateMyDbContext())
{
dbContext.Database.Delete();
dbContext.Departments.Load();
}
}
事情沒那麼簡單,他跳出例外了,因為有重複的 Cluster
這代表 Id 欄位 [Index(IsClustered = false)] 失效,如下圖:
google 找了一些解法,經實驗最後採用下篇解法:
https://entityframework.codeplex.com/workitem/2163
只需要加入以下類別,就能讓 DropCreateHrDatabaseAlways_Test() 正常運行,以下片段程式碼來自上述連結,請依需求自行修改
public class NonClusteredPrimaryKeyCSharpMigrationCodeGenerator : CSharpMigrationCodeGenerator
{
protected override void Generate(System.Data.Entity.Migrations.Model.AddPrimaryKeyOperation addPrimaryKeyOperation, System.Data.Entity.Migrations.Utilities.IndentedTextWriter writer)
{
addPrimaryKeyOperation.IsClustered = false;
base.Generate(addPrimaryKeyOperation, writer);
}
protected override void GenerateInline(System.Data.Entity.Migrations.Model.AddPrimaryKeyOperation addPrimaryKeyOperation, System.Data.Entity.Migrations.Utilities.IndentedTextWriter writer)
{
addPrimaryKeyOperation.IsClustered = false;
base.GenerateInline(addPrimaryKeyOperation, writer);
}
protected override void Generate(System.Data.Entity.Migrations.Model.CreateTableOperation createTableOperation, System.Data.Entity.Migrations.Utilities.IndentedTextWriter writer)
{
createTableOperation.PrimaryKey.IsClustered = false;
base.Generate(createTableOperation, writer);
}
protected override void Generate(System.Data.Entity.Migrations.Model.MoveTableOperation moveTableOperation, System.Data.Entity.Migrations.Utilities.IndentedTextWriter writer)
{
moveTableOperation.CreateTableOperation.PrimaryKey.IsClustered = false;
base.Generate(moveTableOperation, writer);
}
}
public class NonClusteredPrimaryKeySqlMigrationSqlGenerator : SqlServerMigrationSqlGenerator
{
protected override void Generate(System.Data.Entity.Migrations.Model.AddPrimaryKeyOperation addPrimaryKeyOperation)
{
addPrimaryKeyOperation.IsClustered = false;
base.Generate(addPrimaryKeyOperation);
}
protected override void Generate(System.Data.Entity.Migrations.Model.CreateTableOperation createTableOperation)
{
createTableOperation.PrimaryKey.IsClustered = false;
base.Generate(createTableOperation);
}
protected override void Generate(System.Data.Entity.Migrations.Model.MoveTableOperation moveTableOperation)
{
moveTableOperation.CreateTableOperation.PrimaryKey.IsClustered = false;
base.Generate(moveTableOperation);
}
}
internal sealed class Configuration : DbMigrationsConfiguration<DocumentContext>
{
public Configuration()
{
AutomaticMigrationsEnabled = true;
CodeGenerator = new NonClusteredPrimaryKeyCSharpMigrationCodeGenerator();
SetSqlGenerator("System.Data.SqlClient", new NonClusteredPrimaryKeySqlMigrationSqlGenerator());
}
protected override void Seed(DocumentContext context)
{
}
}
完成之後,PK 就變成 Nonclustered了,如下圖:
SequentialNo 順利設為 Clustered,如下圖:
若有謬誤,煩請告知,新手發帖請多包涵
Microsoft MVP Award 2010~2017 C# 第四季
Microsoft MVP Award 2018~2022 .NET