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?

score 4 · Accepted Answer · answered Jul 27 '16 at 17:08

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.

    }
}

I don't know if I'd ever use it, but is there a way to do it per table? — adam0101, Jul 27 '16 at 17:17
No out of the box, it would take some additional logic in your DbContext to alter the query that is sent to the server. You could create a VIEW with NOLOCK hints and map that view to an entity in IF, then you abstract the NOLOCK hints from your c# code all together. — Igor, Jul 27 '16 at 17:19
I'm converting these over for my job, the SPs I've converted so far were much easier. My instinct is telling me to not question the NOLOCK as that's above my paygrade right now. Thanks for tip on getting rid of ToList(). — C-Scholl20, Jul 27 '16 at 17:27
@C-Scholl20 - glad it helped. Please do not forget to mark the answer using the checkbox if you feel it was the correct one for you. — Igor, Jul 27 '16 at 17:39

Entity Framework Joins with NOLOCK

1 Answers1