blocks|key|4599464|text|我同意codeworx的说法，在某种程度上说，未提交的读取可能是危险的。如果你能忍受肮脏的阅读，那就去做吧。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4599465|我找到了一种方法，可以在不更改当前查询中的任何内容的情况下使其工作。|4599466|您需要创建一个如下所示的DbCommandInterceptor：|4599467|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;
++++++++++++}
++++++++}
++++}

}|code-block|syntax|javascript|4599468|然后，在cctor+(dbcontext的静态构造器)处，只需将拦截器添加到实体框架集合的DbInfrastructure中。|4599469|DbInterception.Add(new+IsolationLevelInterceptor(System.Data.IsolationLevel.ReadUncommitted));|4599470|对于EF发送到存储的每个命令，这将包装具有该隔离级别的事务。|4599471|在我的例子中工作得很好，因为我们通过API写入数据，而这些数据不是基于数据库的读数。(数据可能会因为脏读而损坏)，因此工作正常。|4599472|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|X|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|Y|8|@]|9|@]|A|$]]|$1|M|3|N|5|H|7|Z|8|@]|9|@]|A|$I|J]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|11|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

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:
<pre><code>public class IsolationLevelInterceptor : DbCommandInterceptor
{
 private IsolationLevel _isolationLevel;

 public IsolationLevelInterceptor(IsolationLevel level)
 {
 _isolationLevel = level;
 }



 //[ThreadStatic]
 //private DbCommand _command;


 public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext&lt;DbDataReader&gt; interceptionContext)
 {
 SetTransaction(command);

 }

 public override void ScalarExecuting(DbCommand command, DbCommandInterceptionContext&lt;object&gt; interceptionContext)
 {
 SetTransaction(command);
 }

 public override void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext&lt;int&gt; 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;
 }
 }
 }

}
</code></pre>
then at the cctor (static contructor of your dbcontext) just add the interceptor to the DbInfrastructure of entity framework collections.
<pre><code>DbInterception.Add(new IsolationLevelInterceptor(System.Data.IsolationLevel.ReadUncommitted));
</code></pre>
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.

blocks|key|4599531|text|您可以使用不对每个查询使用事务作用域的变通方法。如果您运行以下代码，ef将对相同的Server进程ID使用相同的事务隔离级别。由于Server+Process+ID在同一请求中不会发生变化，因此每个请求只需调用一次即可。这也适用于EF+Core。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4599532|this.Database.ExecuteSqlCommand("SET+TRANSACTION+ISOLATION+LEVEL+READ+UNCOMMITTED;");|code-block|syntax|javascript|4599533|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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.

<pre><code>this.Database.ExecuteSqlCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;");
</code></pre>

blocks|key|93456|text|首先，请对gds03的答案投赞成票。因为没有它我不会走到这一步。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|93457|我为“关闭事务”做出了贡献，并为IDataReader/DbDataReader的情况选择了正确的时机。基本上，在IDataReader/DbDataReader的情况下，您不会关闭“ReaderExecuted”(异步)方法上的事务(重点是Executed的"ed“)，而是让它”落入“(覆盖)+DataReaderDisposing。|93458|但是如果你读了一些其他的答案(这里)(和评论)，我认为SetEndTransaction是..不会从连接池获得巫毒(如果您不可能使用(对我而言)read-uncommitted来关闭事务)。|93459|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();
++++++++++++}
++++++++}

++++}
}|code-block|syntax|javascript|93460|本文对“了解”所有的"ing“和"ed”方法很有帮助。|93461|https://lizzy-gallagher.github.io/query-interception-entity-framework/|offset|length|93462|请注意，我的答案是EntityFrameworkCore+(3.1.%2B)，但我认为它将“反向移植”到EF-for-DN-Framework。|93463|我的回答中更重要的部分是对IDataReader/DbDataReader方法methods...especially的“定时”。|93464|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|0|0|0|0|1Y|0|0|0|0^^$0|@$1|2|3|4|5|6|7|11|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|12|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|13|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|14|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|15|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|16|8|@]|9|@$O|17|P|18|1|19]]|A|$]]|$1|Q|3|R|5|6|7|1A|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|1B|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|1C|8|@]|9|@]|A|$]]]|V|$W|$5|X|Y|Z|A|$10|N]]]]

First off, please upvote gds03's answer. Because I would not have gotten this far without it.
I have contributed to &quot;closing out the transaction&quot; and getting the timing right for IDataReader/DbDataReader situations too.
Basically, with IDataReader/DbDataReader situations, you do NOT close the transaction on the &quot;ReaderExecuted&quot;(Async) methods (emphasis on the &quot;ed&quot; of Executed), but let it &quot;fall through&quot; 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).
<pre><code>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&lt;DbDataReader&gt; ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult&lt;DbDataReader&gt; result)
 {
 SetStartTransaction(command);
 InterceptionResult&lt;DbDataReader&gt; 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&lt;InterceptionResult&lt;DbDataReader&gt;&gt; ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult&lt;DbDataReader&gt; result, CancellationToken cancellationToken = default)
 {
 SetStartTransaction(command);
 ValueTask&lt;InterceptionResult&lt;DbDataReader&gt;&gt; returnItem = base.ReaderExecutingAsync(command, eventData, result, cancellationToken);
 return returnItem;
 }


 public override ValueTask&lt;DbDataReader&gt; ReaderExecutedAsync(DbCommand command, CommandExecutedEventData eventData, DbDataReader result, CancellationToken cancellationToken = default)
 {
 ValueTask&lt;DbDataReader&gt; 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&lt;object&gt; ScalarExecuting(DbCommand command, CommandEventData eventData, InterceptionResult&lt;object&gt; interceptionContext)
 {
 SetStartTransaction(command);
 InterceptionResult&lt;object&gt; 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&lt;InterceptionResult&lt;object&gt;&gt; ScalarExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult&lt;object&gt; result, CancellationToken cancellationToken = default)
 {
 SetStartTransaction(command);
 ValueTask&lt;InterceptionResult&lt;object&gt;&gt; returnItem = base.ScalarExecutingAsync(command, eventData, result, cancellationToken);
 return returnItem;
 }


 public override ValueTask&lt;object&gt; ScalarExecutedAsync(DbCommand command, CommandExecutedEventData eventData, object result, CancellationToken cancellationToken = default)
 {
 SetEndTransaction(command);
 ValueTask&lt;object&gt; returnItem = base.ScalarExecutedAsync(command, eventData, result, cancellationToken);
 return returnItem;
 }


 /* start maybe not needed on queries that only do &quot;reading&quot;, but listed here anyways */

 public override InterceptionResult&lt;int&gt; NonQueryExecuting(DbCommand command, CommandEventData eventData, InterceptionResult&lt;int&gt; result)
 {
 SetStartTransaction(command);
 InterceptionResult&lt;int&gt; 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&lt;InterceptionResult&lt;int&gt;&gt; NonQueryExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult&lt;int&gt; result, CancellationToken cancellationToken = default)
 {
 SetStartTransaction(command);
 ValueTask&lt;InterceptionResult&lt;int&gt;&gt; returnItem = base.NonQueryExecutingAsync(command, eventData, result, cancellationToken);
 return returnItem;
 }

 public override ValueTask&lt;int&gt; NonQueryExecutedAsync(DbCommand command, CommandExecutedEventData eventData, int result, CancellationToken cancellationToken = default)
 {
 ValueTask&lt;int&gt; returnValue = base.NonQueryExecutedAsync(command, eventData, result, cancellationToken);
 SetEndTransaction(command);
 return returnValue;
 }

 /* end maybe not needed on queries that only do &quot;reading&quot;, 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();
 }
 }

 }
}
</code></pre>
This article was helpful in &quot;seeing&quot; all the &quot;ing&quot; and &quot;ed&quot; methods.
<a href="https://lizzy-gallagher.github.io/query-interception-entity-framework/" rel="nofollow noreferrer">https://lizzy-gallagher.github.io/query-interception-entity-framework/</a>
Please note my answer is EntityFrameworkCore (3.1.+), but I think it will &quot;back port&quot; to EF-for-DN-Framework.
The more important parts of my answer is the &quot;timing&quot; of certain methods...especially the IDataReader/DbDataReader methods.

I am using Entity Framework 6 in an MVC 5 project. As you're aware of, <code>SELECT</code> queries in SQL Server perform faster and more efficient if we use <code>WITH (NOLOCK)</code> 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?

Get Entity Framework 6 use NOLOCK in its underneath SELECT statements

SQL Server

数据库

我在一个MVC5项目中使用Entity Framework6。如您所知，如果我们在SQL Server中使用WITH (NOLOCK)，那么SELECT查询的执行速度会更快、效率更高。我检查了Entity Framework6生成的一些SQL SELECT语句，发现它们都不包含NOLOCK。我不想在fetch操作中使用...

问Get Entity Framework 6在其底层SELECT语句中使用NOLOCK
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Get Entity Framework 6在其底层SELECT语句中使用NOLOCKEN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Get Entity Framework 6在其底层SELECT语句中使用NOLOCK
EN