Entity Framework WITH(NOLOCK)

http://stackoverflow.com/questions/926656/entity-framework-with-nolock http://stackoverflow.com/questions/24684914/get-entity-framework-6-use-nolock-in-its-underneath-select-statements Entity Framework with NOLOCK Entity Framework 乐观并发控制 C# Entity Framework并发处理

在SqlServer中，频繁在同一个数据库表同时进行读写的时候，会存在锁的问题，也就是在前一个insert、update、delete事务操作完毕之前，你不能进行读取，必须要等到操作完毕，你才能进行select操作，目的是为了防止并发操作而读到脏数据，在SQL语句中，如果能容忍这种情况、加快查询速度，可以忽略锁进行查询：

select * from [User] with(nolock)

但是如果你项目中使用EntityFramework，可以使用下面这段代码进行nolock查询：需要添加System.Transactions程序集的引用
EF本身不支持WITH(NOLOCK)，都指出建议设置事务的级别为允许脏读。
IsolationLevel = IsolationLevel.ReadUncommitted

//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();
}

改进，如果项目里有多处地方都需要nolock查询，这段代码就需要不断的拷贝了，这时候需要考虑进行封装，很明显，外层代码可以很容易的抽出来，但是，里面的代码段是不确定的，如果封装的话，执行的时候，就需要传一个代码片段进去，委托在这种情况就派上用场了，我们可以使用委托来改进一下，也就是查询数据库时候的逻辑代码代由委托传递进去。

public static void NoLockInvokeDB(Action action)
{
    var transactionOptions = new System.Transactions.TransactionOptions();
    transactionOptions.IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted;
    using (var transactionScope = new System.Transactions.TransactionScope(System.Transactions.TransactionScopeOption.Required, transactionOptions))
    {
        try
        {
            action();
        }
        finally
        {
            transactionScope.Complete();
        }
    }
}
//使用
NoLockInvokeDB(() =>
{
   using (var db = new TicketDB())
   {
      lst = db.User.ToList();
   }
});

如果你不太习惯Action这种微软高度封装的委托的写法（其本质就是委托），可以继续看下去原始委托的写法：

public class Helper
{
    public void NoLockInvokeDB(EFdelegate d)
    {
        var transactionOptions = new System.Transactions.TransactionOptions();
        transactionOptions.IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted;
        using (var transactionScope = new System.Transactions.TransactionScope(System.Transactions.TransactionScopeOption.Required, transactionOptions))
        {
            d();
            transactionScope.Complete();
        }
    }
}
public delegate void EFdelegate();
//使用
EFdelegate d = new EFdelegate(() => {
    //这里写传递的代码段
});
//使用非匿名函数方法
protected void Page_Load(object sender, EventArgs e)
{
    EFdelegate d = new EFdelegate(SonFun);
}
public void SonFun()
{
    //这里写传递的代码片段
}

Entity Framework with nolock. 允许脏读扩展方法

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;
    }
}