如何在 Entity Framework 中增加 WITH (NOLOCK) 和 Parameter Sniffing 的處理

TLDR

• 可透過實作 DbCommandInterceptor 攔截並動態修改 SQL 指令，以注入 WITH (NOLOCK) 或 OPTION (OPTIMIZE FOR UNKNOWN)。
• WITH (NOLOCK) 適用於讀取資料以避免鎖定阻塞，但不建議用於需要精確資料的異動情境。
• 透過 OPTION (OPTIMIZE FOR UNKNOWN) 可有效緩解 SQL Server 的 Parameter Sniffing 效能問題。
• 攔截器應排除 INSERT、UPDATE、DELETE 等異動語法，並針對 TOP(1) 或 TOP(2) 等精確查詢進行過濾，避免誤用提示。
• 實作攔截器後，需透過 DbContextOptionsBuilder 將其註冊至 EF Core。

WARNING

此文章的實作方式在某些現代情境下可能被視為 Anti-Pattern。關於更推薦的替代方案（如 RCSI 架構與 TagWith 實作），請參考最新的完整探討： RCSI 簡介與改良版 Entity Framework 鎖定提示攔截器

使用 Interceptor 攔截 SQL 指令

在 Entity Framework 中，若需針對所有查詢自動加入 SQL 提示（Hint），最有效的方式是使用 DbCommandInterceptor。此機制允許開發者在 EF 執行資料庫操作前，攔截並修改生成的 SQL 字串。

什麼情況下會遇到這個問題

當專案使用 Entity Framework 進行開發，且需要針對大量讀取操作優化效能（如避免鎖定阻塞），或解決 SQL Server 因參數嗅探（Parameter Sniffing）導致的執行計畫不佳問題時，手動修改每一行 SQL 不切實際，此時便適合使用攔截器。

實作攔截器邏輯

透過繼承 DbCommandInterceptor 並覆寫 ReaderExecuting 與 ScalarExecuting 等方法，可以對 SQL 指令進行動態注入。

public class FixDbCommandInterceptor : DbCommandInterceptor {
    private static readonly RegexOptions regexOptions = RegexOptions.Multiline | RegexOptions.IgnoreCase;
    private static readonly Regex cudRegex = new(@"\b(INSERT|UPDATE|DELETE)\b", regexOptions);
    private static readonly Regex tableAliasRegex = new(
        @"(?<tableAlias>(FROM|JOIN)\s+\[\w+\]\s+AS\s+\[\w+\])",
        regexOptions
    );

    public override InterceptionResult<DbDataReader> ReaderExecuting(
        DbCommand command, CommandEventData eventData,
        InterceptionResult<DbDataReader> result) {
        FixCommand(command);
        return base.ReaderExecuting(command, eventData, result);
    }

    public override async ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(
        DbCommand command, CommandEventData eventData,
        InterceptionResult<DbDataReader> result,
        CancellationToken cancellationToken = default
    ) {
        FixCommand(command);
        return await base.ReaderExecutingAsync(command, eventData, result, cancellationToken);
    }

    private static void FixCommand(IDbCommand command) {
        string commandText = command.CommandText;

        // 排除異動語法
        if (cudRegex.IsMatch(commandText)) {
            return;
        }

        // 排除精確查詢情境，避免誤加 NOLOCK
        if (!commandText.Contains("TOP(1)") && !commandText.Contains("TOP(2)")) {
            commandText = tableAliasRegex.Replace(commandText, "${tableAlias} WITH (NOLOCK)");
        }

        // 注入 Parameter Sniffing 處理提示
        commandText = commandText.TrimEnd(';') + " OPTION (OPTIMIZE FOR UNKNOWN);";

        command.CommandText = commandText;
    }
}

註冊與應用

實作完成後，必須將攔截器註冊至 DbContext 中，才能生效。

註冊方式

• 在 DbContext 內部註冊：

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder.AddInterceptors(new FixDbCommandInterceptor());

• 在 DI 容器中註冊：

services.AddDbContext<TestDbContext>(options => {
    options
        .UseSqlServer(DbConnectionString)
        .AddInterceptors(new FixDbCommandInterceptor());
});

執行結果驗證

透過上述攔截器，當執行 ToList() 等查詢時，SQL 語法會自動被注入提示；而針對 Find() 或 SingleOrDefault() 等精確查詢，則會自動過濾掉 NOLOCK 提示，僅保留 OPTIMIZE FOR UNKNOWN。

-- ToList() 執行結果
SELECT [t].[Id], [t].[TestBit], [t].[TestDateTime], [t].[TestGuid], [t].[TestInt], [s].[Id], [s].[TestId]
FROM [Test] AS [t] WITH (NOLOCK)
LEFT JOIN [SubTest] AS [s] WITH (NOLOCK) ON [t].[Id] = [s].[TestId]
ORDER BY [t].[Id] OPTION (OPTIMIZE FOR UNKNOWN);

TIP

上述實作針對 NOLOCK 的處理邏輯較為基礎，若專案中存在複雜的子查詢或特殊 SQL 結構，建議進一步完善 Regex 規則以確保語法正確性。

異動歷程

• 2024-07-18 初版文件建立。