Scenario

Let’s say you have a project using EF Core, that’s making a bunch of SQL SELECT queries, for statistics purposes. The tables that you’re selecting from may have a lot of contention, and since you may not need perfectly accurate results 100% of the time, sometimes you’d use something like “SELECT * FROM YOUR_TABLE WITH (NOLOCK)”, which will change the isolation level to read uncommited. This isolation level allows you to read those records even if there are uncommitted transactions on that table.

Solution

Having faced this issue multiple times the solution that works the best for me is using the IQuerySqlGeneratorFactory contract from EntityFrameworkCore.

The idea here is that we’ll be replacing the default implementation of IQuerySqlGeneratorFactory with our own. This makes it so all queries pass through it, allowing us to add the desired WITH (NOLOCK) in each SELECT statement.

Let’s start by prefacing this works only on SQL Server (that I know of), so keep this in mind. Also, there is no guarantee it will work forever as EF was not made to be “hacked” like this.

The first thing we do is create a query generator that’ll add the WITH (NOLOCK) part to our queries. To achieve this, we inherit from SqlServerQuerySqlGenerator, as such:

public class WithNoLockQueryGenerator : SqlServerQuerySqlGenerator {
    public WithNoLockQueryGenerator(QuerySqlGeneratorDependencies dependencies) : base(dependencies) { }

    protected override Expression VisitTable(TableExpression tableExpression) {
        var expression = base.VisitTable(tableExpression);
        Sql.Append(@" WITH(NOLOCK)");
        return expression;
    }
}

Looking at the code above, we just need to override the VisitTable(...) method, which will return an Expression.

A TableExpression represents a table or a view, where the Expression type is the basis for all types that represent expression tree nodes.

Let’s not get into details here, basically, at this stage you’re halfway there. You should just focus on the Sql.Append(@" WITH(NOLOCK)") part, which will append what we want to the current SQL Command Builder in context.

With this, we can now create our IQuerySqlGeneratorFactory implementation, as such:

public class WithNoLockQueryGeneratorFactory : SqlServerQuerySqlGeneratorFactory {
    private readonly QuerySqlGeneratorDependencies _dependencies;
    public WithNoLockQueryGeneratorFactory(QuerySqlGeneratorDependencies dependencies) : base(dependencies) {
        _dependencies = dependencies;
    }

    public override QuerySqlGenerator Create() {
        return new WithNoLockQueryGenerator(_dependencies);
    }
}

The relevant part here is the Create() method, which returns a new instance of our newly created SQL Query Generator (WithNoLockQueryGenerator)

Now, there’s only one thing left to do, which is specify that you want to replace the default SQL Query Generator Factory with our own. For this, all you need is:

services.AddDbContext<DentalContext>(x => {
    x.ReplaceService<IQuerySqlGeneratorFactory, WithNoLockQueryGeneratorFactory>();
    x.UseSqlServer(configuration["SQL_CON_STR"]);
});

Now, all your SELECT statements will get the WITH (NOLOCK) treatment.

WARNING!

One more thing, your compiler will give you a warning that you should not be hacking your way into isolation levels like this, because there’s no guarantee you’ll be using a database that even supports this concept. If you’re like me and want to make it stop, you can disable the warning with #pragma warning disable EF1001

The full code for the factory is here:

#pragma warning disable EF1001
public class WithNoLockQueryGenerator : SqlServerQuerySqlGenerator {
    public WithNoLockQueryGenerator(QuerySqlGeneratorDependencies dependencies) : base(dependencies) { }

    protected override Expression VisitTable(TableExpression tableExpression) {
        var expression = base.VisitTable(tableExpression);
        Sql.Append(@" WITH(NOLOCK)");
        return expression;
    }
}

public class WithNoLockQueryGeneratorFactory : SqlServerQuerySqlGeneratorFactory {
    private readonly QuerySqlGeneratorDependencies _dependencies;
    public WithNoLockQueryGeneratorFactory(QuerySqlGeneratorDependencies dependencies) : base(dependencies) {
        _dependencies = dependencies;
    }

    public override QuerySqlGenerator Create() {
        return new WithNoLockQueryGenerator(_dependencies);
    }
}

This is it, again, be careful as it may stop working at any given time, however, it’s the only proven way to do it that I’ve found. If you know of any other, please do let me know :)