EF Core 7: How to add WITH(NOLOCK) for SQL Server

Question

With EF Core 7, the solution I used before doesn't seem to work for .NET 7 projects. See Apply WITH NOLOCK to every table in Entity Framework Core 3.x.

How do I implement WITH(NOLOCK) using a solution that is officially supported by the EF Core team: Interceptors

I tried to fix the error but since WithNoLockQuerySqlGenerator is using a hack to an internal EF Core API, I don't expect it to work for EF Core 7.

Sample error:

Error CS7036
There is no argument given that corresponds to the required parameter 'typeMappingSource' of 'SqlServerQuerySqlGeneratorFactory.SqlServerQuerySqlGeneratorFactory(QuerySqlGeneratorDependencies, IRelationalTypeMappingSource)'

Answer 1

With the use of Regular Expressions, append 'WITH(NOLOCK)' via DbCommandInterceptor

Step 1 - Inherit from DbCommandInterceptor

• WithNoLockSqlInterceptor.cs

using Microsoft.EntityFrameworkCore.Diagnostics;
using System.Data.Common;
using System.Text.RegularExpressions;

namespace Sample.Data;

public class WithNoLockSqlInterceptor : DbCommandInterceptor
{
    public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command
        , CommandEventData eventData
        , InterceptionResult<DbDataReader> result)
    {
        DoReaderExecuting(command, eventData);

        return result;
    }

    public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(DbCommand command
        , CommandEventData eventData
        , InterceptionResult<DbDataReader> result
        , CancellationToken cancellationToken = new())
    {
        DoReaderExecuting(command, eventData);

        return new ValueTask<InterceptionResult<DbDataReader>>(result);
    }

    private static void DoReaderExecuting(DbCommand command, CommandEventData eventData)
    {
        var commandText = command.CommandText;

        commandText = GetSqlWithNoLock(commandText);
        command.CommandText = commandText;
    }

    private static string GetSqlWithNoLock(string commandText)
    {
        const string regExPattern = @"((FROM|JOIN).* AS \[*\w+\b\]*)";
        const string newValueWithNoLock = "$1 WITH(NOLOCK)";

        if (!Regex.IsMatch(commandText, regExPattern, RegexOptions.IgnoreCase))
        {
            return commandText;
        }

        // APPEND WITH (NOLOCK)
        var sql = Regex.Replace(commandText, regExPattern, newValueWithNoLock, RegexOptions.IgnoreCase);

        return sql;
    }
}

Regular expression explained:

1. ((FROM|JOIN).* AS \[*\w+\b\]*) <= Capture all SQL that starts with FROM or JOIN and ends with AS [TableName]
2. $1 WITH(NOLOCK) <= Using the captured group, append WITH(NOLOCK)

Step 2 - Register WithNoLockSqlInterceptor

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;

namespace Sample.Api.Extensions;

public static class OrmStartupExtensions
{
    private static readonly WithNoLockSqlInterceptor WithNoLockSqlInterceptor = new();

    /// <summary>
    ///     Initialize Ef Core db contexts.
    /// </summary>
    public static IServiceCollection ConfigureEfCore(this IServiceCollection services, IConfiguration configuration)
    {
        var connectionString = configuration.GetConnectionString(AppConstants.CoopDbConnectionStringKey);

        return services
            .AddDbContext<SampleDb>((serviceProvider, options) =>
            {
                options
                    .AllowLoggingIfDebug(serviceProvider) //// This is for troubleshooting purposes only
                    .ConfigureWarnings(w =>
                    {
                        w.Ignore(CoreEventId.RowLimitingOperationWithoutOrderByWarning);
                        w.Ignore(CoreEventId.DistinctAfterOrderByWithoutRowLimitingOperatorWarning);
                    })
                    .EnableDetailedErrors()
                    .UseSqlServer(connectionString, o =>
                    {
                        o.UseQuerySplittingBehavior(QuerySplittingBehavior.SingleQuery);
                    })
                    .AddInterceptors(WithNoLockSqlInterceptor); //<= Register the interceptor here.
                ;

            }, ServiceLifetime.Transient, ServiceLifetime.Transient);
    }

    /// <summary>
    /// This is for debugging purposes only and should NEVER enable in Production.
    /// </summary>
    public static DbContextOptionsBuilder AllowLoggingIfDebug(this DbContextOptionsBuilder options, IServiceProvider serviceProvider)
    {
#if DEBUG
        var logger = serviceProvider.GetRequiredService<ILogger<Startup>>();

        options
            .LogTo(message => logger.LogTrace(message), new[] { RelationalEventId.CommandExecuted })
            .EnableSensitiveDataLogging();
#endif
        return options;
    }
}