Entity Framework with NOLOCK

How can I use the NOLOCK function on Entity Framework? Is XML the only way to do this?

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.

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

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

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

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.

Entity Framework with NOLOCK

Related

Recent Posts