Entity Framework Joins with NOLOCK

Question

I'm converting a SQL function to C# using Entity Framework, and the function I'm converting uses the NOLOCK hint on every table (10 of them) being joined.

Because of this, I decided to set the IsolationLevel to ReadUncommitted for the entire transaction. Currently, I'm doing inner joins like so.

from a in context.table1.ToList()
join b in context.table2.ToList on a.Id equals b.Id

so on and so forth

Would declaring lists, for example

IEnumerable<table1> Table1 = new List<table1>();

and then populating them using the context BEFORE I start my query yield different results in those tables? I.e would Table1 be the same as context.table1.ToList()? And if so, which implementation should I go with?

Answer 1

from a in context.table1.ToList()
join b in context.table2.ToList on a.Id equals b.Id

That statement will materialize all items in table1 into memory, all items in table2 to memory and then join in memory for the rest of the query. Do not do that unless you just do not care about performance at all. Instead remove the ToList calls and join like so.

from a in context.table1
join b in context.table2 on a.Id equals b.Id

That would yield a join in sql server and now you can proceed with the remainder of the query in Linq. The query would then materialize the results from the database as soon as you start iterating over the results or use an extension method ToList, ToArray, AsEnumerable, or one of the other methods that retrieves a single item like Single or First etc (the Async versions are also implied here).

---

Secondly I do not recommend using NOLOCK, you can have unexpected results that pop up unless you know for a fact that incorrect data is not a big deal like maybe showing volatile data where no decisions are being made on that data. Now if this does not bother you and you still want to use the equivalent of NOLOCK then create a new transaction around your EF calls.

using (new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted })) 
{
    using (var context = new MyDbContext()) 
    { 
        var result = from a in context.table1
            join b in context.table2 on a.Id equals b.Id
            // rest of your linq query here.

    }
}