[Entity Framework 6] Code First - PK Column set Non-Clustered

預設,SQL Server 會幫我們把 PK 設為 Clustered,當 PK 是 GUID type(uniqueIdentifier) 時,有一些注意事項

  1. 使用具有順序性的 GUID,避免索引破碎;我習慣自己產生,不使用 SQL 提供的Store Procedure 的 NEWSEQUENCEID 和 NEWID
  2. 設為 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)
    {
    }
}
以上解法在開發階段可以盡情使用,但在正式環境,還是要回歸正確的 Migration,正式環境 AutomaticMigrationsEnabled 應該設為 false,交給人工處理

 

完成之後,PK 就變成 Nonclustered了,如下圖:

SequentialNo 順利設為 Clustered,如下圖:

若有謬誤,煩請告知,新手發帖請多包涵


Microsoft MVP Award 2010~2017 C# 第四季
Microsoft MVP Award 2018~2022 .NET

Image result for microsoft+mvp+logo