给 EF Core 查询增加 With NoLock

àì夳堔傛蜴生んèń 2022-11-17 05:29 181阅读 0赞

# 给 EF Core 查询增加 With NoLock #

## Intro ##

EF Core 在 3.x 版本中增加了 `Interceptor`，使得我们可以在发生低级别数据库操作时作为 EF Core 正常运行的一部分自动调用它们。 例如，打开连接、提交事务或执行命令时。

所以我们可以自定义一个 `Interceptor` 来记录执行的 sql 语句，也可以通过 `Interceptor` 来实现 sql 语句的执行前的修改或者更准确的记录 DbCommand 执行的耗时。

这里我们可以借助 `Interceptor` 实现对于查询语句的修改，自动给查询语句加 `(WITH NOLOCK)`，`WITH NOLOCK` 等效于 `READ UNCOMMITED`（读未提交）的事务级别，这样可能会造成一定的脏读，但是从效率上而言，是比较高效的，不会因为别的事务长时间未提交导致查询阻塞，所以对于大数据多事务的场景下，查询 SQL 加 `NOLOCK` 还是比较有意义的

## NoLockInterceptor ##

继承 `DbCommandInterceptor`，重写查询 sql 执行之前的操作，在执行查询 sql 之前增加 `WITH(NOLOCK)`，实现代码如下：

public class QueryWithNoLockDbCommandInterceptor : DbCommandInterceptor
    {
        private static readonly Regex TableAliasRegex =
            new Regex(@"(?<tableAlias>AS \[[a-zA-Z]\w*\](?! WITH $NOLOCK$))",
                RegexOptions.Multiline | RegexOptions.Compiled | RegexOptions.IgnoreCase);
    
        public override InterceptionResult<object> ScalarExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<object> result)
        {
            command.CommandText = TableAliasRegex.Replace(
                command.CommandText,
                "${tableAlias} WITH (NOLOCK)"
                );
            return base.ScalarExecuting(command, eventData, result);
        }
    
        public override Task<InterceptionResult<object>> ScalarExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<object> result,
            CancellationToken cancellationToken = new CancellationToken())
        {
            command.CommandText = TableAliasRegex.Replace(
                command.CommandText,
                "${tableAlias} WITH (NOLOCK)"
                );
            return base.ScalarExecutingAsync(command, eventData, result, cancellationToken);
        }
    
        public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
        {
            command.CommandText = TableAliasRegex.Replace(
                command.CommandText,
                "${tableAlias} WITH (NOLOCK)"
                );
            return result;
        }
    
        public override Task<InterceptionResult<DbDataReader>> ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result,
            CancellationToken cancellationToken = new CancellationToken())
        {
            command.CommandText = TableAliasRegex.Replace(
                command.CommandText,
                "${tableAlias} WITH (NOLOCK)"
                );
            return base.ReaderExecutingAsync(command, eventData, result, cancellationToken);
        }
    }

## Interceptor 的使用 ##

在注册 `DbContext` 服务的时候，可以配置 `Interceptor`，配置如下：

var services = new ServiceCollection();
    services.AddDbContext<TestDbContext>(options =>
    {
        options
            .UseLoggerFactory(loggerFactory)
            .UseSqlServer(DbConnectionString)
            .AddInterceptors(new QueryWithNoLockDbCommandInterceptor())
            ;
    });

## 使用效果 ##

通过 loggerFactory 记录的日志查看查询执行的 sql 语句

![489462-20200402234957480-1640323527.png][]

可以看到查询语句自动加上了 `WITH (NOLOCK)`

## Reference ##

*  [https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver15][https_docs.microsoft.com_en-us_sql_t-sql_queries_hints-transact-sql-table_view_sql-server-ver15]
 *  [https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.0/\#interception-of-database-operations][https_docs.microsoft.com_en-us_ef_core_what-is-new_ef-core-3.0_interception-of-database-operations]
 *  [https://docs.microsoft.com/zh-cn/ef/core/what-is-new/ef-core-3.0/\#interception-of-database-operations][https_docs.microsoft.com_zh-cn_ef_core_what-is-new_ef-core-3.0_interception-of-database-operations]
 *  [https://github.com/WeihanLi/WeihanLi.EntityFramework/blob/dev/src/WeihanLi.EntityFramework/Interceptors/QueryWithNoLockDbCommandInterceptor.cs][https_github.com_WeihanLi_WeihanLi.EntityFramework_blob_dev_src_WeihanLi.EntityFramework_Interceptors_QueryWithNoLockDbCommandInterceptor.cs]

[489462-20200402234957480-1640323527.png]: /images/20221022/12f9614a8d174e87b0ca74732a0a41d1.png
[https_docs.microsoft.com_en-us_sql_t-sql_queries_hints-transact-sql-table_view_sql-server-ver15]: https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver15
[https_docs.microsoft.com_en-us_ef_core_what-is-new_ef-core-3.0_interception-of-database-operations]: https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.0/#interception-of-database-operations
[https_docs.microsoft.com_zh-cn_ef_core_what-is-new_ef-core-3.0_interception-of-database-operations]: https://docs.microsoft.com/zh-cn/ef/core/what-is-new/ef-core-3.0/#interception-of-database-operations
[https_github.com_WeihanLi_WeihanLi.EntityFramework_blob_dev_src_WeihanLi.EntityFramework_Interceptors_QueryWithNoLockDbCommandInterceptor.cs]: https://github.com/WeihanLi/WeihanLi.EntityFramework/blob/dev/src/WeihanLi.EntityFramework/Interceptors/QueryWithNoLockDbCommandInterceptor.cs