扩展ef支持with nolock。修改EF表的别名

可以使用继承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()
}