How to speed up with nolock in AspNetZero #9930

0

zony created 5 years ago

Support Team

Hi kansoftware, In EF Core, you can use DbCommandInterceptor to add the NOLOCK feature. QueryWithNoLockDbCommandInterceptor:

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);
    }
}

In your EF Core module, You can register a custom interceptor. example:

public override void PreInitialize()
{
    if (!SkipDbContextRegistration)
    {
        Configuration.Modules.AbpEfCore().AddDbContext<QADemoDbContext>(options =>
        {
            if (options.ExistingConnection != null)
            {
                QADemoDbContextConfigurer.Configure(options.DbContextOptions, options.ExistingConnection);
            }
            else
            {
                QADemoDbContextConfigurer.Configure(options.DbContextOptions, options.ConnectionString);
            }

            // Register your interceptor.
            options.DbContextOptions.AddInterceptors(new QueryWithNoLockDbCommandInterceptor());
        });
    }
}

0

kansoftware created 5 years ago

Thanks

I just want to know how to use it with entity . Can we use this with multiple joins in linq lambda query also?

0

zony created 5 years ago

Support Team

Hi kansoftware, There is currently no LINQ Lambda form of NOLOCK extension method. If you want to achieve the same effect as NOLOCK, you can also control the transaction isolation level.

3 Answer(s)