Get Entity Framework 6在其底层SELECT语句中使用NOLOCK
Get Entity Framework 6在其底层SELECT语句中使用NOLOCK
提问于 2014-07-11 03:39:41
我在一个MVC5项目中使用Entity Framework6。如您所知，如果我们在SQL Server中使用WITH (NOLOCK)，那么SELECT查询的执行速度会更快、效率更高。我检查了Entity Framework6生成的一些SQL SELECT语句，发现它们都不包含NOLOCK。
我不想在fetch操作中使用事务来读取未提交的事务。
如何强制EF 6在下面生成的SELECT语句中使用NOLOCK？
回答 3
Stack Overflow用户
发布于 2018-03-06 22:53:28
您可以使用不对每个查询使用事务作用域的变通方法。如果您运行以下代码，ef将对相同的Server进程ID使用相同的事务隔离级别。由于Server Process ID在同一请求中不会发生变化，因此每个请求只需调用一次即可。这也适用于EF Core。
this.Database.ExecuteSqlCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;");Stack Overflow用户
发布于 2016-08-05 18:30:14
我同意codeworx的说法，在某种程度上说，未提交的读取可能是危险的。如果你能忍受肮脏的阅读，那就去做吧。
我找到了一种方法，可以在不更改当前查询中的任何内容的情况下使其工作。
您需要创建一个如下所示的DbCommandInterceptor：
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;
}
}
}
}然后，在cctor (dbcontext的静态构造器)处，只需将拦截器添加到实体框架集合的DbInfrastructure中。
DbInterception.Add(new IsolationLevelInterceptor(System.Data.IsolationLevel.ReadUncommitted));对于EF发送到存储的每个命令，这将包装具有该隔离级别的事务。
在我的例子中工作得很好，因为我们通过API写入数据，而这些数据不是基于数据库的读数。(数据可能会因为脏读而损坏)，因此工作正常。
Stack Overflow用户
发布于 2021-03-25 03:05:20
首先，请对gds03的答案投赞成票。因为没有它我不会走到这一步。
我为“关闭事务”做出了贡献，并为IDataReader/DbDataReader的情况选择了正确的时机。基本上，在IDataReader/DbDataReader的情况下，您不会关闭“ReaderExecuted”(异步)方法上的事务(重点是Executed的"ed“)，而是让它”落入“(覆盖) DataReaderDisposing。
但是如果你读了一些其他的答案(这里)(和评论)，我认为SetEndTransaction是..不会从连接池获得巫毒(如果您不可能使用(对我而言)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();
}
}
}
}本文对“了解”所有的"ing“和"ed”方法很有帮助。
https://lizzy-gallagher.github.io/query-interception-entity-framework/
请注意，我的答案是EntityFrameworkCore (3.1.+)，但我认为它将“反向移植”到EF-for-DN-Framework。
我的回答中更重要的部分是对IDataReader/DbDataReader方法methods...especially的“定时”。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/24684914
复制相关文章
相似问题
腾讯云开发者
