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()) ; }); ``` #### 参考： - 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/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://www.cnblogs.com/weihanli/p/12623934.html