Converting from SQL to linq (With(NOLOCK))

Question

SQL:

select * 
from INVOICE inv WITH(NOLOCK)
inner join ERP_TRANSACTION erpt WITH(NOLOCK) on inv.Id = erpt.Id
inner join ERP_TRANSACTION_LOG erptl WITH(NOLOCK) on inv.InvoiceId = erptl.FimId

Linq:

from inv in _invoiceService.QueryableAsNoTracking()
join erpt in _erpTransactionService.QueryableAsNoTracking() on inv.Id = erpt.Id
join erpt1 _erpTransactionLogService.QueryableAsNoTracking() on inv.InvoiceId = erptl.FimId
select new Model {}

I'm trying to convert my SQL query to a linq query but I couldn't figure out the linq equivalent of WİTH(NOLOCK).

If you have `select new Model {...}`, you can remove AsNoTracking. Also needed only one AsNotacking call per query. — Svyatoslav Danyliv, May 27 '22 at 05:02
Why do you think you need `NOLOCK` in the first place? It is not a go-faster switch, it's a give-incorrect-results switch — Charlieface, May 27 '22 at 09:10
Does this answer your question? [NOLOCK with Linq to SQL](https://stackoverflow.com/questions/1220807/nolock-with-linq-to-sql) — Charlieface, May 27 '22 at 09:11

score 2 · Answer 1 · answered May 27 '22 at 04:29

You effectively need to use a "IsolationLevel.ReadUncommitted". It means that the query doesn't care if stuff is in the process of being written to the rows it's reading from - it'll read that "dirty" data and return it as part of the result set.

using (var txn = new TransactionScope(
    TransactionScopeOption.Required, 
    new TransactionOptions
    {
        IsolationLevel = IsolationLevel.ReadUncommitted
    }
))
{
    // Your LINQ to SQL query goes here
}

Link to original post - Link

Converting from SQL to linq (With(NOLOCK))

1 Answers1