### 可以使用继承EF的DbCommandInterceptor，然后重写里边的方法，修改生成的sql语句 代码如下 ``` public class QueryWithNoLockDbCommandInterceptor : DbCommandInterceptor { // 可以把 from [XX] as [y] 替换为 from [XX] as [y] WITH (NOLOCK)。也就是需要在后面加上WITH (NOLOCK) 其中[XX]与[y]可以是任意英文与数字的组合名称 // string pattern = @"FROM\s+\[[a-zA-Z0-9_]+\]\s+AS\s+\[[a-zA-Z0-9]+\]"; // 除了支持把 from [XX] as [y] 替换为 from [XX] as [y] WITH (NOLOCK)还能支持把JOIN [XX] as [y] 替换为 from [XX] as [y] WITH (NOLOCK) string pattern = @"(?:FROM|JOIN)\s+\[[a-zA-Z0-9_]+\]\s+AS\s+\[[a-zA-Z0-9]+\]"; public override InterceptionResult<object> ScalarExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<object> result) { //command.CommandText = TableAliasRegex.Replace( // command.CommandText, // "$& WITH (NOLOCK)" // ); //string pattern = @"FROM\s+\[[a-zA-Z0-9]+\]\s+AS\s+\[[a-zA-Z0-9]+\]"; string replacement = "$& WITH (NOLOCK)"; string output = Regex.Replace(command.CommandText, pattern, replacement); command.CommandText = output; //Console.WriteLine("ScalarExecuting替换后的sql:" + output); 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, // "$& WITH (NOLOCK)" // ); //string pattern = @"FROM\s+\[[a-zA-Z0-9]+\]\s+AS\s+\[[a-zA-Z0-9]+\]"; string replacement = "$& WITH (NOLOCK)"; string output = Regex.Replace(command.CommandText, pattern, replacement); command.CommandText = output; //Console.WriteLine("ScalarExecutingAsync替换后的sql:" + output); 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, // "$& WITH (NOLOCK)" // ); //string pattern = @"FROM\s+\[[a-zA-Z0-9]+\]\s+AS\s+\[[a-zA-Z0-9]+\]"; string replacement = "$& WITH (NOLOCK)"; string output = Regex.Replace(command.CommandText, pattern, replacement); command.CommandText = output; //Console.WriteLine("ReaderExecuting替换后的sql:" + output); 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, "$& WITH (NOLOCK)" );*/ //string pattern = @"FROM\s+\[[a-zA-Z0-9]+\]\s+AS\s+\[[a-zA-Z0-9]+\]"; string replacement = "$& WITH (NOLOCK)"; string output = Regex.Replace(command.CommandText, pattern, replacement); command.CommandText = output; //Console.WriteLine("ReaderExecutingAsync替换后的sql:" + output); return base.ReaderExecutingAsync(command, eventData, result, cancellationToken); } } ``` 然后在初始化ef的时候添加即可。这样就可以自动添加with nolock了。 ``` var connection = Configuration.GetConnectionString("BloggingDatabase"); services.AddDbContext<CNBLOGContext>(options => options.UseSqlServer(connection) .AddInterceptors(new QueryWithNoLockDbCommandInterceptor()) ); ``` ### 修改EF表的别名 在EF 中，可以通过重写 sqlGenerator 类中的方法来修改表别名。下面是一个示例: ``` public class CustomSqlGenerator : SqlServerQuerySqlGenerator { // 重写 VisitTable 方法以添加表别名和 WITH (NOLOCK) 提示 protected override Expression VisitTable(TableExpression tableExpression) { var sql = base.VisitTable(tableExpression); if (!string.IsNullOrEmpty(tableExpression.Alias)) { sql = new StringFragment($"{sql} AS {tableExpression.Alias}"); } return new StringFragment($"{sql} WITH (NOLOCK)"); } } ``` 在这个示例中，我们创健了一个自定义的SqlGenerator 类，并重写了其中的 VisitTable方法，在这方法中，我们首先调用类的 Visitable 方法生成 SOL，然后判断当前表达式是否有别名，如果有，则添加别名; 最后添加“WITH(NOLOCK)”提示并返回结果。 要使用自走义的 sglGenerator ，需要在 DbContext 中进行配置 ``` protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.ReplaceService<ISglGenerationHelper, SqlServerSqlGenerationHelper>(); optionsBuilder,ReplaceService<IQuerySqlGeneratorFactory, CustomOuerySqlGeneratorFactory>(); } private class CustomQuerySglGeneratorFactory : QuerySglGeneratorFactoryBase { public CustomQuerySqlGeneratorFactory(QuerysqlGeneratorDependencies dependencies): base(dependencies) { } public override IQuerySglGenerator Create() } ```