linq nolock_获取LINQ to SQL和LINQ to Entities以使用NOLOCK

linq nolock

I was visiting a local company where a bunch of friends work and they asked how to get LINQ to SQL to use NOLOCK. They were explicitly asking how to get the SQL generated by LINQ to SQL to add the NOLOCK hints to the end.

我去了一家本地公司，那里有很多朋友在工作，他们问如何使LINQ to SQL使用NOLOCK 。 他们明确询问如何将LINQ生成SQL转换为SQL，以在末尾添加NOLOCK提示。

However, with NOLOCK (even though "everyone" has used it at some point or another) is generally considered a last resort. Queries that use NOLOCK aren't guaranteed to return correct results or technically, return any results at all.  

但是，使用NOLOCK(即使“每个人”都曾在某个时候使用过它)通常被认为是万不得已的方法。 不保证使用NOLOCK的查询返回正确的结果，或者从技术上讲，根本不返回任何结果。

SQL 2005 has snapshot-based isolation levels that prevent readers from blocking writers or writers from blocking readers without allowing dirty reads.

SQL 2005具有基于快照的隔离级别，可防止读者阻止写者或写者阻止读者而又不允许脏读。

Now, I have said that NOLOCK has served me personally very well in the past on systems of some size, but I hear what folks who say no to NOLOCK are saying. It certainly depends on one's definition of "correct results." ;)

现在，我说过NOLOCK过去在某种规模的系统上为我个人提供了很好的服务，但是我听到对NOLOCK表示拒绝的人们所说的。 当然，这取决于一个人对“正确结果”的定义。 ;)

There's three ways to get the behavior your want. Using TransactionScope is the recommended way to affect the transaction options associated with the commands generated by either LINQ to SQL or LINQ to Entities.

可以通过三种方式获得所需的行为。 建议使用TransactionScope来影响与LINQ to SQL或LINQ to Entities生成的命令关联的事务选项。

LINQ to SQL also supports explicitly setting the transaction on the context, so you could get the connection from the context, open it, start a transaction, and set it on the context. This can be desirable if you think SQL 2005 is promoting transactions too often, but the preferred method is TransactionScope.

LINQ to SQL还支持在上下文中显式设置事务，因此您可以从上下文中获取连接，打开它，启动一个事务，然后在上下文中进行设置。 如果您认为SQL 2005过于频繁地促进事务，但是首选的方法是TransactionScope，则这可能是理想的。

ProductsNewViewData viewData = new ProductsNewViewData();
using (var t = new TransactionScope(TransactionScopeOption.Required,
    new TransactionOptions { 
        IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted 
    }))
{
   viewData.Suppliers = northwind.Suppliers.ToList();
   viewData.Categories = northwind.Categories.ToList();
 }

Here's an example where I used it in some recent code. This TransactionScope could be hidden (layered away) in your DAL (Data Access Layer) or in your Data Context directly if you wanted it to be neater.

这是我在最近的一些代码中使用它的示例。 如果您希望它更整洁，则可以在DAL(数据访问层)或直接在数据上下文中隐藏(分层)此TransactionScope。

A second way is that you can still create and call Stored Procedures (sprocs) from LINQ to SQL and those sprocs could include NOLOCK, TransactionScope is a better choice for LINQ to SQL or LINQ to Entity generated SQL if you feel that your query doesn't need to lock down the table(s) it's reading from.

第二种方法是，您仍然可以创建和调用LINQ to SQL中的存储过程(存储过程)，并且这些存储过程可以包括NOLOCK，如果您认为查询没有，那么TransactionScope是LINQ to SQL或LINQ to Entity Generated SQL的更好选择。无需锁定正在读取的表。

Note that you'll want to be aware of which statement in your LINQ to SQL actually starts talking to the database. You can setup a query ahead of time and it won't be executed, for example, until someone calls ToList() or the like. It's at this point you'll want to wrap it in the using(TransactionScope){}.

请注意，您将需要知道LINQ to SQL中的哪个语句实际上开始与数据库进行对话。 您可以提前设置查询，并且直到有人调用ToList()之类的查询时，该查询才会执行。 此时，您需要将其包装在using(TransactionScope){}中。

Another third way you could set it at a DataContext level (which, to be clear, would affect every generated LINQ to SQL query executed on that context) would be to execute the command:

可以将其设置为DataContext级别的另一种第三种方法(很明显，这会影响在该上下文上执行的每个生成的LINQ to SQL查询)，将执行以下命令：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

...using the connection available inside your DataContext.

...使用DataContext内部可用的连接。

Thanks to David Browne and Michael Pizzo for their help on this!

感谢David Browne和Michael Pizzo在此方面的帮助！

翻译自: https://www.hanselman.com/blog/getting-linq-to-sql-and-linq-to-entities-to-use-nolock

linq nolock