C# 带有 NOLOCK 的实体框架
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/926656/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Entity Framework with NOLOCK
提问by OneSmartGuy
How can I use the NOLOCKfunction on Entity Framework? Is XML the only way to do this?
如何NOLOCK在实体框架上使用该功能？XML 是唯一的方法吗？
采纳答案by Doctor Jones
No, but you can start a transaction and set the isolation level to read uncommited. This essentially does the same as NOLOCK, but instead of doing it on a per table basis, it will do it for everything within the scope of the transaction.
不，但您可以启动一个事务并将隔离级别设置为 read uncommited。这本质上与 NOLOCK 的作用相同，但不是在每个表的基础上执行，而是对事务范围内的所有内容执行此操作。
If that sounds like what you want, here's how you could go about doing it...
如果这听起来像你想要的，这里是你如何去做......
//declare the transaction options
var transactionOptions = new System.Transactions.TransactionOptions();
//set it to read uncommited
transactionOptions.IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted;
//create the transaction scope, passing our options in
using (var transactionScope = new System.Transactions.TransactionScope(
System.Transactions.TransactionScopeOption.Required,
transactionOptions)
)
//declare our context
using (var context = new MyEntityConnection())
{
//any reads we do here will also read uncomitted data
//...
//...
//don't forget to complete the transaction scope
transactionScope.Complete();
}
回答by marc_s
No, not really - Entity Framework is basically a fairly strict layer above your actual database. Your queries are formulated in ESQL - Entity SQL - which is first of all targeted towards your entity model, and since EF supports multiple database backends, you can't really send "native" SQL directly to your backend.
不，不是真的 - 实体框架基本上是您实际数据库之上的一个相当严格的层。您的查询是在 ESQL - Entity SQL 中制定的 - 它首先针对您的实体模型，并且由于 EF 支持多个数据库后端，您无法真正将“本机”SQL 直接发送到您的后端。
The NOLOCK query hint is a SQL Server specific thing and won't work on any of the other supported databases (unless they've also implemented the same hint - which I strongly doubt).
NOLOCK 查询提示是 SQL Server 特定的东西，不适用于任何其他受支持的数据库（除非它们也实现了相同的提示 - 我强烈怀疑）。
Marc
马克
回答by Ryan Galloway
To get round this I create a view on the database and apply NOLOCK on the view's query. I then treat the view as a table within EF.
为了解决这个问题，我在数据库上创建了一个视图，并对视图的查询应用 NOLOCK。然后我将视图视为 EF 中的一个表。
回答by Frank.Germain
If you need something at large, the best way we found which less intrusive than actually starting a transactionscope each time, is to simply set the default transaction isolation level on your connection after you've created your object context by running this simple command:
如果您需要大量的东西，我们发现最好的方法是在您创建对象上下文后，通过运行以下简单命令，简单地在连接上设置默认事务隔离级别，这比每次实际启动事务范围的侵入性要小：
this.context.ExecuteStoreCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;");
http://msdn.microsoft.com/en-us/library/aa259216(v=sql.80).aspx
http://msdn.microsoft.com/en-us/library/aa259216(v=sql.80).aspx
With this technique, we were able to create a simple EF provider that creates the context for us and actually runs this command each time for all of our context so that we're always in "read uncommitted" by default.
使用这种技术，我们能够创建一个简单的 EF 提供程序，该提供程序为我们创建上下文，并且每次实际为我们的所有上下文运行此命令，以便我们在默认情况下始终处于“未提交读取”状态。
回答by Alexandre
Extension methods can make this easier
扩展方法可以使这更容易
public static List<T> ToListReadUncommitted<T>(this IQueryable<T> query)
{
using (var scope = new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions() {
IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
{
List<T> toReturn = query.ToList();
scope.Complete();
return toReturn;
}
}
public static int CountReadUncommitted<T>(this IQueryable<T> query)
{
using (var scope = new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions() {
IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
{
int toReturn = query.Count();
scope.Complete();
return toReturn;
}
}
回答by Yuriy Rozhovetskiy
Though I absolutely agreed that using Read Uncommitted transaction isolation level is the best choice, but some time you forced to use NOLOCK hint by request of manager or client and no reasons against this accepted.
虽然我完全同意使用 Read Uncommitted 事务隔离级别是最好的选择，但有时你被迫根据经理或客户的要求使用 NOLOCK 提示，并且没有理由反对这一点。
With Entity Framework 6 you can implement own DbCommandInterceptor like this:
使用实体框架 6，您可以像这样实现自己的 DbCommandInterceptor：
public class NoLockInterceptor : DbCommandInterceptor
{
private static readonly Regex _tableAliasRegex =
new Regex(@"(?<tableAlias>AS \[Extent\d+\](?! WITH \(NOLOCK\)))",
RegexOptions.Multiline | RegexOptions.IgnoreCase);
[ThreadStatic]
public static bool SuppressNoLock;
public override void ScalarExecuting(DbCommand command,
DbCommandInterceptionContext<object> interceptionContext)
{
if (!SuppressNoLock)
{
command.CommandText =
_tableAliasRegex.Replace(command.CommandText, "${tableAlias} WITH (NOLOCK)");
}
}
public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
{
if (!SuppressNoLock)
{
command.CommandText =
_tableAliasRegex.Replace(command.CommandText, "${tableAlias} WITH (NOLOCK)");
}
}
}
With this class in place, you can apply it on application start:
有了这个类，你可以在应用程序启动时应用它：
DbInterception.Add(new NoLockInterceptor());
And conditionally turn off adding of NOLOCKhint into queries for current thread:
并有条件地关闭将NOLOCK提示添加到当前线程的查询中：
NoLockInterceptor.SuppressNoLock = true;
回答by myuce
Enhancing on Doctor Jones's accepted answer and using PostSharp;
First "ReadUncommitedTransactionScopeAttribute"
首先是“ ReadUncommitedTransactionScopeAttribute”
[Serializable]
public class ReadUncommitedTransactionScopeAttribute : MethodInterceptionAspect
{
public override void OnInvoke(MethodInterceptionArgs args)
{
//declare the transaction options
var transactionOptions = new TransactionOptions();
//set it to read uncommited
transactionOptions.IsolationLevel = IsolationLevel.ReadUncommitted;
//create the transaction scope, passing our options in
using (var transactionScope = new TransactionScope(TransactionScopeOption.Required, transactionOptions))
{
//declare our context
using (var scope = new TransactionScope())
{
args.Proceed();
scope.Complete();
}
}
}
}
Then whenever you need it,
然后在你需要的时候，
[ReadUncommitedTransactionScope()]
public static SomeEntities[] GetSomeEntities()
{
using (var context = new MyEntityConnection())
{
//any reads we do here will also read uncomitted data
//...
//...
}
}
Being Able to add "NOLOCK" with an interceptor is also nice but will not work when connecting to other database systems like Oracle as such.
能够使用拦截器添加“NOLOCK”也很好，但在连接到其他数据库系统（如 Oracle）时将不起作用。
回答by Rafiki
One option is to use a stored procedure (similar to the view solution proposed by Ryan) and then execute the stored procedure from EF. This way the stored procedure performs the dirty read while EF just pipes the results.
一种选择是使用存储过程（类似于 Ryan 提出的视图解决方案），然后从 EF 执行存储过程。这样，存储过程执行脏读，而 EF 只是通过管道传输结果。
回答by Ali
With the introduction of EF6, Microsoft recommends using BeginTransaction() method.
随着 EF6 的引入，Microsoft 建议使用 BeginTransaction() 方法。
You can use BeginTransaction instead of TransactionScope in EF6+ and EF Core
您可以在 EF6+ 和 EF Core 中使用 BeginTransaction 而不是 TransactionScope
using (var ctx = new ContractDbContext())
using (var transaction = ctx.Database.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted))
{
//any reads we do here will also read uncommitted data
}
