Get Entity Framework 6 use NOLOCK in its underneath SELECT statements
C#Sql.NetSql ServerEntity FrameworkC# Problem Overview
I am using Entity Framework 6 in an MVC 5 project. As you're aware of, SELECT queries in SQL Server perform faster and more efficient if we use WITH (NOLOCK) in them. I checked out a few SQL SELECT statements generated by Entity Framework 6 and realized that none of them contain NOLOCK.
I do not want to use transactions in my fetch operations to read from uncommitted transactions.
How can I enforce EF 6 to use NOLOCK in the underneath generated SELECT statements?
C# Solutions
Solution 1 - C#
First of all... You should NEVER EVER use NOLOCK for each and every SQL Statement. It could compromise the integrity of your data.
It’s like any other query hint a mechanism you should only use when you do something out of the ordinary.
There is no way to tell the EF Provider to render the NoLock hint. If you really need to read uncommitted data you have the following option.
-
Write your own EntityFramework Provider.
-
Use a Command Interceptor to modify the statement before it is executed. http://msdn.microsoft.com/en-us/data/dn469464.aspx
-
Use a TransactionScope with IsolationLevel.ReadUncommited.
I know you said you do not want to use Transactions but it's the only out-of-the box way to read uncommitted data. Also it does not produce much overhead as each statement in SQL Server “implicitly” runs in a transaction.
using (new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions
{
IsolationLevel = IsolationLevel.ReadUncommitted
}))
{
using (var db = new MyDbContext()) {
// query
}
}
EDIT: It's important to note also that NOLOCK for Updates and Deletes (selects remain intact) has been Deprecated by Microsoft as of SQL Server 2016 and and will be removed in 'a' future release.
Solution 2 - C#
You can use a workaround that don't use transaction scopes for each query. If you run the code below, ef will use the same transaction isolation level for the same Server Process ID. Since Server Process ID does not change in same request, only one call for each request is sufficient. This also works in EF Core.
this.Database.ExecuteSqlCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;");
Solution 3 - C#
I agree with what codeworx says in a way that Read Uncommitted can be dangerous. If you can live with dirty reads, go for it.
I found a way to make this work without changing anything in the current queries.
You need to create a DbCommandInterceptor like this:
public class IsolationLevelInterceptor : DbCommandInterceptor
{
private IsolationLevel _isolationLevel;
public IsolationLevelInterceptor(IsolationLevel level)
{
_isolationLevel = level;
}
//[ThreadStatic]
//private DbCommand _command;
public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
{
SetTransaction(command);
}
public override void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
{
SetTransaction(command);
}
public override void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
{
SetTransaction(command);
}
private void SetTransaction(DbCommand command)
{
if (command != null)
{
if (command.Transaction == null)
{
var t = command.Connection.BeginTransaction(_isolationLevel);
command.Transaction = t;
//_command = command;
}
}
}
}
then at the cctor (static contructor of your dbcontext) just add the interceptor to the DbInfrastructure of entity framework collections.
DbInterception.Add(new IsolationLevelInterceptor(System.Data.IsolationLevel.ReadUncommitted));
this will for each command that EF sends to the store, wraps over a transaction with that Isolation Level.
In my case worked well, since we write data through an API where that data is not based on the readings from the database. (data can be corrupted because of the dirty reads) and so worked fine.
Solution 4 - C#
In our project we use combination of the second and third solutions, suggested by @Cem Mutlu and @anotherNeo.
Experiment with Sql Profiler showed that we have to use pair of commands:
- READ UNCOMMITTED
- READ COMMITTED
because NET reuse connections via SqlConnectionPool
internal class NoLockInterceptor : DbCommandInterceptor
{
public static readonly string SET_READ_UNCOMMITED = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED";
public static readonly string SET_READ_COMMITED = "SET TRANSACTION ISOLATION LEVEL READ COMMITTED";
public override void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
{
if (!interceptionContext.DbContexts.OfType<IFortisDataStoreNoLockContext>().Any())
{
return;
}
ExecutingBase(command);
}
public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
{
if (!interceptionContext.DbContexts.OfType<IFortisDataStoreNoLockContext>().Any())
{
return;
}
ExecutingBase(command);
}
private static void ExecutingBase(DbCommand command)
{
var text = command.CommandText;
command.CommandText = $"{SET_READ_UNCOMMITED} {Environment.NewLine} {text} {Environment.NewLine} {SET_READ_COMMITED}";
}
}
Solution 5 - C#
First off, please upvote gds03's answer. Because I would not have gotten this far without it.
I have contributed to "closing out the transaction" and getting the timing right for IDataReader/DbDataReader situations too. Basically, with IDataReader/DbDataReader situations, you do NOT close the transaction on the "ReaderExecuted"(Async) methods (emphasis on the "ed" of Executed), but let it "fall through" to (override of) DataReaderDisposing.
But if you read some of the other answers (here) (and comments), I think SetEndTransaction is an important part of .. not getting voodoo from the connection-pool (if you don't probably close out the transaction with the (for me) read-uncommitted).
using System.Data;
using System.Data.Common;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore.Diagnostics;
namespace My.Interceptors
{
public class IsolationLevelInterceptor : DbCommandInterceptor
{
private IsolationLevel _isolationLevel;
public IsolationLevelInterceptor(IsolationLevel level)
{
_isolationLevel = level;
}
//[ThreadStatic]
//private DbCommand _command;
public override InterceptionResult DataReaderDisposing(DbCommand command, DataReaderDisposingEventData eventData, InterceptionResult result)
{
InterceptionResult returnItem = base.DataReaderDisposing(command, eventData, result);
SetEndTransaction(command);
return returnItem;
}
public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
{
SetStartTransaction(command);
InterceptionResult<DbDataReader> returnItem = base.ReaderExecuting(command, eventData, result);
return returnItem;
}
public override DbDataReader ReaderExecuted(DbCommand command, CommandExecutedEventData eventData, DbDataReader result)
{
DbDataReader returnItem = base.ReaderExecuted(command, eventData, result);
//SetEndTransaction(command); // DO NOT DO THIS HERE .. datareader still open and working .. fall back on DataReaderDisposing... you don't really need this override, but left in to show the possible issue.
return returnItem;
}
public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result, CancellationToken cancellationToken = default)
{
SetStartTransaction(command);
ValueTask<InterceptionResult<DbDataReader>> returnItem = base.ReaderExecutingAsync(command, eventData, result, cancellationToken);
return returnItem;
}
public override ValueTask<DbDataReader> ReaderExecutedAsync(DbCommand command, CommandExecutedEventData eventData, DbDataReader result, CancellationToken cancellationToken = default)
{
ValueTask<DbDataReader> returnItem = base.ReaderExecutedAsync(command, eventData, result, cancellationToken);
//SetEndTransaction(command); // DO NOT DO THIS HERE .. datareader still open and working .. fall back on DataReaderDisposing... you don't really need this override, but left in to show the possible issue.
return returnItem;
}
public override InterceptionResult<object> ScalarExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<object> interceptionContext)
{
SetStartTransaction(command);
InterceptionResult<object> returnItem = base.ScalarExecuting(command, eventData, interceptionContext);
return returnItem;
}
public override object ScalarExecuted(DbCommand command, CommandExecutedEventData eventData, object result)
{
SetEndTransaction(command);
object returnItem = base.ScalarExecuted(command, eventData, result);
return returnItem;
}
public override ValueTask<InterceptionResult<object>> ScalarExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<object> result, CancellationToken cancellationToken = default)
{
SetStartTransaction(command);
ValueTask<InterceptionResult<object>> returnItem = base.ScalarExecutingAsync(command, eventData, result, cancellationToken);
return returnItem;
}
public override ValueTask<object> ScalarExecutedAsync(DbCommand command, CommandExecutedEventData eventData, object result, CancellationToken cancellationToken = default)
{
SetEndTransaction(command);
ValueTask<object> returnItem = base.ScalarExecutedAsync(command, eventData, result, cancellationToken);
return returnItem;
}
/* start maybe not needed on queries that only do "reading", but listed here anyways */
public override InterceptionResult<int> NonQueryExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<int> result)
{
SetStartTransaction(command);
InterceptionResult<int> returnItem = base.NonQueryExecuting(command, eventData, result);
return returnItem;
}
public override int NonQueryExecuted(DbCommand command, CommandExecutedEventData eventData, int result)
{
int returnValue = base.NonQueryExecuted(command, eventData, result);
SetEndTransaction(command);
return returnValue;
}
public override ValueTask<InterceptionResult<int>> NonQueryExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<int> result, CancellationToken cancellationToken = default)
{
SetStartTransaction(command);
ValueTask<InterceptionResult<int>> returnItem = base.NonQueryExecutingAsync(command, eventData, result, cancellationToken);
return returnItem;
}
public override ValueTask<int> NonQueryExecutedAsync(DbCommand command, CommandExecutedEventData eventData, int result, CancellationToken cancellationToken = default)
{
ValueTask<int> returnValue = base.NonQueryExecutedAsync(command, eventData, result, cancellationToken);
SetEndTransaction(command);
return returnValue;
}
/* end maybe not needed on queries that only do "reading", but listed here anyways */
private void SetStartTransaction(DbCommand command)
{
if (command != null)
{
if (command.Transaction == null)
{
DbTransaction t = command.Connection.BeginTransaction(_isolationLevel);
command.Transaction = t;
//_command = command;
}
}
}
private void SetEndTransaction(DbCommand command)
{
if (command != null)
{
if (command.Transaction != null)
{
command.Transaction.Commit();
//_command = command;
}
command.Dispose();
}
}
}
}
This article was helpful in "seeing" all the "ing" and "ed" methods.
https://lizzy-gallagher.github.io/query-interception-entity-framework/
Please note my answer is EntityFrameworkCore (3.1.+), but I think it will "back port" to EF-for-DN-Framework.
The more important parts of my answer is the "timing" of certain methods...especially the IDataReader/DbDataReader methods.