How to speed up with nolock in AspNetZero #9930

3 Answer(s)

• 

  0

  zony created 4 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());
          });
      }
  }
  

  Save Cancel
• 

  0

  kansoftware created 4 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?

  Save Cancel
• 

  0

  zony created 4 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.

  Save Cancel