NOLOCK이있는 엔터티 프레임 워크

NOLOCK이있는 엔터티 프레임 워크

---

NOLOCKEntity Framework에서이 기능을 사용하려면 어떻게 해야합니까? XML이이를 수행 할 수있는 유일한 방법입니까?

---

아니요, 그러나 트랜잭션을 시작하고 격리 수준을 커밋되지 않은 읽기로 설정할 수 있습니다 . 이것은 본질적으로 NOLOCK과 동일하지만 테이블 단위로 수행하는 대신 트랜잭션 범위 내의 모든 항목에 대해 수행합니다.

그것이 원하는 것처럼 들리면 다음과 같이하십시오.

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

---

확장 방법으로 이것을 쉽게 만들 수 있습니다

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

---

큰 무언가가 필요한 경우 매번 transactionscope를 실제로 시작하는 것보다 덜 방해가되는 가장 좋은 방법은 다음 간단한 명령을 실행하여 객체 컨텍스트를 만든 후 연결에서 기본 트랜잭션 격리 수준을 설정하는 것입니다.

this.context.ExecuteStoreCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;");

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.

---

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.

With Entity Framework 6 you can implement own DbCommandInterceptor like this:

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 NOLOCK hint into queries for current thread:

NoLockInterceptor.SuppressNoLock = true;

---

Enhancing on Doctor Jones's accepted answer and using PostSharp;

First "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.

---

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.

---

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.

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).

Marc

---

With the introduction of EF6, Microsoft recommends using BeginTransaction() method.

You can use BeginTransaction instead of TransactionScope in EF6+ and EF Core

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
}

---

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.

참고URL : https://stackoverflow.com/questions/926656/entity-framework-with-nolock