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?

Answer 1

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