EF Core 7如何通过官方Interceptors实现SQL Server的WITH(NOLOCK)

解决CS7036错误的核心原因 #

EF Core 7中SqlServerQuerySqlGeneratorFactory的构造函数签名发生变化，新增了IRelationalTypeMappingSource参数，旧版本代码未传入该参数导致编译错误。以下是两种官方支持的实现方案：

方案一：全局替换QuerySqlGenerator（全局生效） #

通过替换EF Core的SQL生成器，给所有查询的表自动添加WITH(NOLOCK)提示。

1. 自定义SqlServerQuerySqlGeneratorFactory

using Microsoft.EntityFrameworkCore.Query.SqlExpressions;
using Microsoft.EntityFrameworkCore.SqlServer.Query.Internal;
using Microsoft.EntityFrameworkCore.Storage;

public class NoLockSqlServerQuerySqlGeneratorFactory : SqlServerQuerySqlGeneratorFactory
{
    private readonly QuerySqlGeneratorDependencies _dependencies;
    private readonly IRelationalTypeMappingSource _typeMappingSource;

    public NoLockSqlServerQuerySqlGeneratorFactory(
        QuerySqlGeneratorDependencies dependencies,
        IRelationalTypeMappingSource typeMappingSource)
        : base(dependencies, typeMappingSource)
    {
        _dependencies = dependencies;
        _typeMappingSource = typeMappingSource;
    }

    public override QuerySqlGenerator Create()
    {
        return new NoLockSqlServerQuerySqlGenerator(_dependencies, _typeMappingSource);
    }
}

2. 自定义SqlServerQuerySqlGenerator

using Microsoft.EntityFrameworkCore.Query;
using Microsoft.EntityFrameworkCore.Query.SqlExpressions;
using Microsoft.EntityFrameworkCore.SqlServer.Query.Internal;
using Microsoft.EntityFrameworkCore.Storage;

public class NoLockSqlServerQuerySqlGenerator : SqlServerQuerySqlGenerator
{
    public NoLockSqlServerQuerySqlGenerator(
        QuerySqlGeneratorDependencies dependencies,
        IRelationalTypeMappingSource typeMappingSource)
        : base(dependencies, typeMappingSource)
    {
    }

    protected override Expression VisitTable(TableExpression tableExpression)
    {
        var result = base.VisitTable(tableExpression);
        Append(" WITH (NOLOCK)");
        return result;
    }
}

3. 在DbContext中注册替换服务

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseSqlServer("你的数据库连接字符串")
        .ReplaceService<IQuerySqlGeneratorFactory, NoLockSqlServerQuerySqlGeneratorFactory>();
}

方案二：使用CommandInterceptor（灵活控制） #

通过拦截生成的SQL命令，按需给表添加WITH(NOLOCK)提示，适合需要针对特定查询生效的场景。

1. 实现DbCommandInterceptor

using Microsoft.EntityFrameworkCore.Diagnostics;
using System.Data.Common;
using System.Text.RegularExpressions;

public class NoLockCommandInterceptor : DbCommandInterceptor
{
    public override InterceptionResult<DbDataReader> ReaderExecuting(
        DbCommand command,
        CommandEventData eventData,
        InterceptionResult<DbDataReader> result)
    {
        // 给SQL中的表添加NOLOCK提示（正则匹配简单场景，复杂SQL需调整正则）
        command.CommandText = AddNoLockHints(command.CommandText);
        return result;
    }

    private string AddNoLockHints(string sql)
    {
        var regex = new Regex(@"FROM\s+\[([^\]]+)\](\s+\[[^\]]+\])?", RegexOptions.IgnoreCase);
        return regex.Replace(sql, match => $"{match.Groups[0].Value} WITH (NOLOCK)");
    }
}

2. 在DbContext中注册Interceptor

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseSqlServer("你的数据库连接字符串")
        .AddInterceptors(new NoLockCommandInterceptor());
}

内容的提问来源于stack exchange，提问作者Edd