Intro
EF Core 在 3.x 版本中增加了 Interceptor, 使得我们可以在发生低级别数据库操作时作为 EF Core 正常运行的一部分自动调用它们. 例如, 打开连接, 提交事务或执行命令时.
所以我们可以自定义一个 Interceptor 来记录执行的 sql 语句, 也可以通过 Interceptor 来实现 sql 语句的执行.
这里我们可以借助 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 语句
可以看到查询语句自动加上了 WITH (NOLOCK)
Reference
来源: https://www.cnblogs.com/weihanli/p/12623934.html