如何让Linq2Sql在其SQL语句中发出NOLOCK指令? 如果可以的话,具体应该怎么做?
如何让Linq2Sql在其SQL语句中发出NOLOCK指令? 如果可以的话,具体应该怎么做?
没错,这里是我博客上的文章:
以下是在谷歌搜索 "linq sql nolock" 的第一批结果:The NOLOCK hint is essentially the same as wrapping a query in a transaction whose "isolation level" is set to "read uncommitted". It means that the query doesn't care if stuff is in the process of being written to the rows it's reading from - it'll read that "dirty" data and return it as part of the result set.
Turns out that you can do the whole "read uncommitted" transaction thing using the old System.Transactions namespace introduced in .NET 2.0. Here's some sample code:
using (var txn = new TransactionScope( TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted } )) { // Your LINQ to SQL query goes here }
So I'm creating a new TransactionScope object and telling it to use a read-uncommitted isolation level. The query within the "using" statement now acts as if all its tables were reading with the NOLOCK hint.
InfoQ:使用 LINQ to SQL 和 LINQ to Entities 实现 NOLOCK
Matt Hamilton - LINQ to SQL 和 NOLOCK 提示:真棒!
Scott Hanselman's Computer Zen - 获取 LINQ to SQL 和 LINQ to ...
关于King的LinqPad My Extensions
添加的进一步说明:
public static IQueryable<T> DumpNoLock<T>(this IQueryable<T> query)
{
using (var txn = GetNewReadUncommittedScope())
{
return query.Dump();
}
}
public static System.Transactions.TransactionScope GetNewReadUncommittedScope()
{
return new System.Transactions.TransactionScope(
System.Transactions.TransactionScopeOption.RequiresNew,
new System.Transactions.TransactionOptions
{
IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted
});
}
public static IQueryable<T> DumpNoLock<T>(this IQueryable<T> query, string description)
{
using (var txn = GetNewReadUncommittedScope())
{
return query.Dump(description);
}
}
public static List<T> ToListNoLock<T>(this IQueryable<T> query)
{
using (var txn = GetNewReadUncommittedScope())
{
return query.ToList();
}
}
public static U NoLock<T,U>(this IQueryable<T> query, Func<IQueryable<T>,U> expr)
{
using (var txn = GetNewReadUncommittedScope())
{
return expr(query);
}
}
最后一个意味着你可以在任何未显式写入NoLock
的评估查询上执行NOLOCK
(就像我上面为ToListNoLock
所做的一样)。例如:
somequery.NoLock((x)=>x.Count()).Dump();
将使用 NOLOCK
评估查询。
请注意,您必须确保正在评估查询。例如,.NoLock((x)=>x.Distinct()).Count().Dump()
不会从 .Distinct().Count().Dump()
中有任何有用的区别。
一个简单的方法可能是在您的DataContext类上运行一个命令。
using (var dataContext = new DataContext())
{
dataContext.ExecuteCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED");
// Your SQL query
}
这里有一个与LINQPAD一起使用的扩展方法。
public static IQueryable<T> Dump2<T>(this IQueryable<T> query)
{
using (var txn = new System.Transactions.TransactionScope(TransactionScopeOption.RequiresNew,
new TransactionOptions
{
IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted
}))
{
return query.Dump();
}
}
然后您可以这样调用:
MyTable.Where(t => t.Title = "Blah").Dump2();
private FoobarEntities db = new FoobarEntities();
public FoobarController()
{
db.Database.ExecuteSqlCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED");
}