Ef-Core - What regex can I use to replace table names with nolock ones in Db Interceptor

Question

I've been trying to port our EF6 project to EF-Core-2.0.

In EF6, we were using DbNolock interceptor for adding With (NOLOCK) hint which queries we want. You can find my ex-running Db interceptor code below.

   public class DbNoLockInterceptor : DbCommandInterceptor
    {
    private static readonly Regex TableAliasRegex = new Regex(@"((?<!\){1,5})AS \[Extent\d+\](?! WITH \(NOLOCK\)))", RegexOptions.Multiline | RegexOptions.IgnoreCase);

    public override void ScalarExecuting(DbCommand command,
        DbCommandInterceptionContext<object> interceptionContext)
    {
        command.CommandText =
            TableAliasRegex.Replace(command.CommandText, mt => mt.Groups[0].Value + " WITH (NOLOCK) ");
    }

    public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        command.CommandText = TableAliasRegex.Replace(command.CommandText,  mt => mt.Groups[0].Value + " WITH (NOLOCK) ");
    }
}

In Ef-Core, we can make interception nearly same way. But because of changing naming convention of tables, I couldn't write a Regex for the new one. You can find the new Ef-Core version below:

public class DbNoLockListener
{
    private static readonly Regex TableAliasRegex = new Regex(@"((?<!\){1,5})AS \[Extent\d+\](?! WITH \(NOLOCK\)))", RegexOptions.Multiline | RegexOptions.IgnoreCase);
    [DiagnosticName("Microsoft.EntityFrameworkCore.Database.Command.CommandExecuting")]
    public void OnCommandExecuting(DbCommand command, DbCommandMethod executeMethod, Guid commandId, Guid connectionId, bool async, DateTimeOffset startTime)
    {
        command.CommandText =
                        TableAliasRegex.Replace(command.CommandText, mt => mt.Groups[0].Value + " WITH (NOLOCK) ");
    }
}

Ef6 Generated SQL:

SELECT
    [Extent1].[Id] AS [Extent1Id], 
    [Extent2].[Id] AS [Extent2Id]
    FROM [Advert].[Advert]  AS [Extent1]
    INNER JOIN [Membership].[Members] AS [Extent2] ON [Extent1].[MemberId] = [Extent2].[MemberId]

Ef-Core Genereated SQL:

SELECT 
     [t].[Id]
    ,[t.Member].[Id]
FROM [Advert].[Advert] AS [t]
INNER JOIN [Membership].[Members] AS [t.Member] ON [t].[MemberId] = [t.Member].[MemberId]

You can also take a look this github issue for more detail.

I want to replace AS [t] with AS [t] WITH (NOLOCK) and AS [t.Member] with AS [t.Member] WITH (NOLOCK)

Which pattern can I use to do the same in Ef-Core?

why on earth are you still using commands with EF Core? if you are why even bother upgrading - i just don't see the point.. — jazb, Oct 31 '18 at 07:41
@JohnB could you explain what you mean as its unclear to me. The above functionality is not provided out the box... so I'm not understanding what you are implying. — Seabizkit, Oct 31 '18 at 07:50
What is the regex issue here? What string do you want to match and what string should not match, and what outcome do you expect? Note you can greatly simplify replacement if you use `"$& WITH (NOLOCK) "` instead of `mt => mt.Groups[0].Value + " WITH (NOLOCK) "` — Wiktor Stribiżew, Oct 31 '18 at 07:54
The equivalent of the interceptor can be done by hooking into the DiagnosticSource infrastructure — jazb, Oct 31 '18 at 07:57
Sorry for not making it clear. I have added generated SQLs for both. — ibrahimozgon, Oct 31 '18 at 08:01
Try `Regex.Replace(s, @"\sAS\s\[t(?:\.Member)?](?!\s*WITH\s*$NOLOCK$)", "$& WITH (NOLOCK)")` — Wiktor Stribiżew, Oct 31 '18 at 08:21
All that said, `NOLOCK` is a very bad idea, unless you don't care about unreliable data. — Gert Arnold, Dec 10 '22 at 18:18

Ivan Stoev · Accepted Answer · 2022-12-10T14:41:03.620

This interception method doesn't look good to me. A better ways IMO is to hook into EF Core infrastructure to replace the IQuerySqlGenerator service implementation for SqlServer with custom implementation overriding the VisitTable method like this:

public override Expression VisitTable(TableExpression tableExpression)
{
    // base will append schema, table and alias
    var result = base.VisitTable(tableExpression);
    Sql.Append(" WITH (NOLOCK)");
    return result;
}

Hooking is a bit complicated because we need to create and replace the "factory" service in order to be able to replace the sql generator. The full code for all that, along with helper extension method is as follows:

EF Core 7.0:

using System.Linq.Expressions;
using Microsoft.EntityFrameworkCore.Query;
using Microsoft.EntityFrameworkCore.SqlServer.Query.Sql.Internal;
using Microsoft.EntityFrameworkCore.SqlServer.Query.Internal;
using Microsoft.EntityFrameworkCore.Query.SqlExpressions;
using Microsoft.EntityFrameworkCore.Storage;

namespace Microsoft.EntityFrameworkCore
{
    public static partial class CustomDbContextOptionsBuilderExtensions
    {
        public static DbContextOptionsBuilder UseCustomSqlServerQuerySqlGenerator(this DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.ReplaceService<IQuerySqlGeneratorFactory, CustomSqlServerQuerySqlGeneratorFactory>();
            return optionsBuilder;
        }
    }
}

namespace Microsoft.EntityFrameworkCore.SqlServer.Query.Sql.Internal
{
    class CustomSqlServerQuerySqlGeneratorFactory : IQuerySqlGeneratorFactory
    {
        public CustomSqlServerQuerySqlGeneratorFactory(QuerySqlGeneratorDependencies dependencies, IRelationalTypeMappingSource typeMappingSource)
            => (Dependencies, TypeMappingSource) = (dependencies, typeMappingSource);
        public QuerySqlGeneratorDependencies Dependencies { get; }
        public IRelationalTypeMappingSource TypeMappingSource { get; }
        public QuerySqlGenerator Create() => new CustomSqlServerQuerySqlGenerator(Dependencies, TypeMappingSource);
    }

    public class CustomSqlServerQuerySqlGenerator : SqlServerQuerySqlGenerator
    {
        public CustomSqlServerQuerySqlGenerator(QuerySqlGeneratorDependencies dependencies, IRelationalTypeMappingSource typeMappingSource)
            : base(dependencies, typeMappingSource) { }
        protected override Expression VisitTable(TableExpression tableExpression)
        {
            // base will append schema, table and alias
            var result = base.VisitTable(tableExpression);
            Sql.Append(" WITH (NOLOCK)");
            return result;
        }
    }
}

EF Core 3.x:

using System.Linq.Expressions;
using Microsoft.EntityFrameworkCore.Query;
using Microsoft.EntityFrameworkCore.SqlServer.Query.Sql.Internal;
using Microsoft.EntityFrameworkCore.SqlServer.Query.Internal;
using Microsoft.EntityFrameworkCore.Query.SqlExpressions;

namespace Microsoft.EntityFrameworkCore
{
    public static class CustomDbContextOptionsBuilderExtensions
    {
        public static DbContextOptionsBuilder UseCustomSqlServerQuerySqlGenerator(this DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.ReplaceService<IQuerySqlGeneratorFactory, CustomSqlServerQuerySqlGeneratorFactory>();
            return optionsBuilder;
        }
    }
}

namespace Microsoft.EntityFrameworkCore.SqlServer.Query.Sql.Internal
{
    class CustomSqlServerQuerySqlGeneratorFactory : IQuerySqlGeneratorFactory
    {
        public CustomSqlServerQuerySqlGeneratorFactory(QuerySqlGeneratorDependencies dependencies)
            => Dependencies = dependencies;
        public QuerySqlGeneratorDependencies Dependencies { get; }
        public QuerySqlGenerator Create() => new CustomSqlServerQuerySqlGenerator(Dependencies);
    }

    public class CustomSqlServerQuerySqlGenerator : SqlServerQuerySqlGenerator
    {
        public CustomSqlServerQuerySqlGenerator(QuerySqlGeneratorDependencies dependencies)
            : base(dependencies) { }
        protected override Expression VisitTable(TableExpression tableExpression)
        {
            // base will append schema, table and alias
            var result = base.VisitTable(tableExpression);
            Sql.Append(" WITH (NOLOCK)");
            return result;
        }
    }
}

EF Core 2.x:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using Microsoft.EntityFrameworkCore.Query.Expressions;
using Microsoft.EntityFrameworkCore.Query.Sql;
using Microsoft.EntityFrameworkCore.Storage;
using Microsoft.EntityFrameworkCore.SqlServer.Infrastructure.Internal;
using Microsoft.EntityFrameworkCore.SqlServer.Query.Sql.Internal;

namespace Microsoft.EntityFrameworkCore
{
    public static class CustomDbContextOptionsBuilderExtensions
    {
        public static DbContextOptionsBuilder UseCustomSqlServerQuerySqlGenerator(this DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.ReplaceService<IQuerySqlGeneratorFactory, CustomSqlServerQuerySqlGeneratorFactory>();
            return optionsBuilder;
        }
    }
}

namespace Microsoft.EntityFrameworkCore.SqlServer.Query.Sql.Internal
{
    class CustomSqlServerQuerySqlGeneratorFactory : SqlServerQuerySqlGeneratorFactory
    {
        private readonly ISqlServerOptions sqlServerOptions;
        public CustomSqlServerQuerySqlGeneratorFactory(QuerySqlGeneratorDependencies dependencies, ISqlServerOptions sqlServerOptions)
            : base(dependencies, sqlServerOptions) => this.sqlServerOptions = sqlServerOptions;
        public override IQuerySqlGenerator CreateDefault(SelectExpression selectExpression) =>
            new CustomSqlServerQuerySqlGenerator(Dependencies, selectExpression, sqlServerOptions.RowNumberPagingEnabled);
    }

    public class CustomSqlServerQuerySqlGenerator : SqlServerQuerySqlGenerator
    {
        public CustomSqlServerQuerySqlGenerator(QuerySqlGeneratorDependencies dependencies, SelectExpression selectExpression, bool rowNumberPagingEnabled)
            : base(dependencies, selectExpression, rowNumberPagingEnabled) { }
        public override Expression VisitTable(TableExpression tableExpression)
        {
            // base will append schema, table and alias
            var result = base.VisitTable(tableExpression);
            Sql.Append(" WITH (NOLOCK)");
            return result;
        }
    }
}

Quite a bit code for adding just one meaningful line, but the benefit it that it does it the way EF Core would probably do it in case there is such query option.

Anyway, with the above code all you need is to activate it from your context OnConfiguring override:

optionsBuilder.UseCustomSqlServerQuerySqlGenerator();

Not having any experience with EF Core, generally speaking you should use your own namespace, and not that of Microsoft. — Tsahi Asher, Oct 31 '18 at 13:07
@TsahiAsher Why not use Microsoft or System namespaces when extending them? And especially with EF Core, all database providers extend the `Microsoft.EntityFrameworkCore` namespace with custom extension methods like `UseSqlServer` , `UseMySql` and many other extension methods. The whole EF Core fluent API architecture is based on custom extension methods. The idea is that having `using Microsoft.EntityFrameworkCore;` in your code is enough to get access to all of them. — Ivan Stoev, Oct 31 '18 at 13:44
Well, this is a diversion from the [Framework Design Guidelines](https://learn.microsoft.com/en-us/dotnet/standard/design-guidelines/names-of-namespaces). — Tsahi Asher, Oct 31 '18 at 14:03
@TsahiAsher Well, they are just guidelines (recommendations) rather than mandatory rules :) Especially when there are obvious benefits and many MS teams are not following them (extend `System*` namespaces in unrelated packages, including EF Core). — Ivan Stoev, Oct 31 '18 at 14:09
Hi @IvanStoev, this is exactly what I want. I've run some tests and they are working like a charm! Thank you. — ibrahimozgon, Oct 31 '18 at 15:20
Hi @ibrahimozgon, you are welcome, glad being helpful :) Happy coding. — Ivan Stoev, Oct 31 '18 at 19:03
This is applicable to `SELECT` statements. Is there any way to hook on to `UPDATE` statements as well? — Twisted Whisper, Jun 29 '20 at 05:03
@TwistedWhisper Nope. Insert, Update and Delete commands use different service. But it makes no sense to use it for `NOLOCK` hint because it is not allowed for these commands anyway. — Ivan Stoev, Jun 29 '20 at 08:18
Actually I wanted to implement a TABLOCK hint `UPDATE [TableName] WITH (TABLOCK) SET .......`. Just wondering how would I go about it. — Twisted Whisper, Jun 29 '20 at 08:22
@TwistedWhisper You have to replace `ISqlServerUpdateSqlGenerator` service with custom class inheriting their `SqlServerUpdateSqlGenerator` class and overriding `AppendUpdateCommandHeader` method. You can find the source code in EF Core GitHub repository. — Ivan Stoev, Jun 29 '20 at 08:40
@IvanStoev Thanks a lot for pointing me in the right direction. I couldn't find the information anywhere online. — Twisted Whisper, Jun 29 '20 at 08:48

score 0 · Answer 2 · answered Oct 31 '18 at 08:00

The equivalent of the interceptor can be done by hooking into the DiagnosticSource infrastructure.

First create an interceptor:

public class NoLockInterceptor : IObserver<KeyValuePair<string, object>>
{
    public void OnCompleted()
    {
    }

    public void OnError(Exception error)
    {
    }

    public void OnNext(KeyValuePair<string, object> value)
    {
        if (value.Key == RelationalEventId.CommandExecuting.Name)
        {
            var command = ((CommandEventData)value.Value).Command;

            // Do command.CommandText manipulation here
        }
    }
}

Next, create a global listener for EF diagnostics. Something like:

public class EfGlobalListener : IObserver<DiagnosticListener>
{
    private readonly NoLockInterceptor _noLockInterceptor = new NoLockInterceptor();

    public void OnCompleted()
    {
    }

    public void OnError(Exception error)
    {
    }

    public void OnNext(DiagnosticListener listener)
    {    
        if (listener.Name == DbLoggerCategory.Name)
        {
            listener.Subscribe(_noLockInterceptor);
        }
    }
}

And register this as part of application startup

DiagnosticListener.AllListeners.Subscribe(new EfGlobalListener());

Thanks, John. I've already done this part by different way. My exact question is here: **// Do command.CommandText manipulation here** How can I manipulate sql with nolock? — ibrahimozgon, Oct 31 '18 at 08:04
and i'm saying the above approach may be lower cost to maintain in the long runas you don't have to deal with brittle regex code — jazb, Oct 31 '18 at 08:08
@JohnB Ok, how can we do it without brittle Regex code? Your code doesn't include a solution for it. Am I missing something? — ibrahimozgon, Oct 31 '18 at 08:14
Hi, @WiktorStribiżew Why is far from clear? I simply want to change all **AS [t]* *values to **AS [t] WITH (NOLOCK)** — ibrahimozgon, Oct 31 '18 at 08:17

Ef-Core - What regex can I use to replace table names with nolock ones in Db Interceptor

2 Answers2

Linked