Using Entity Framework Core v8.0.8 DbContext and TransactionScope to read uncommitted from a table

Question

I'm currently looking at upgrading a .NET project (standard moving to .NET 8) that uses Entity Framework (v6.4.4) to EF Core (v8.0.8).

For the most part the experience has been painless but I have one case I haven't been able to migrate.

For certain queries against a set of large tables (1-2 TB) a set of lockless reads were used using the transaction scope. I understand the risks of using dirty reads, however there are instances where a WITH (NOLOCK) is beneficial.

In EF 6.4.4, we performed this action like this:

using myDbContext db = ContextHelper.Context(); db.Database.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted); db.mytable.tolist();

I have attempted the same using EF Core 8.0.8, trying to wrap the whole thing in transactions based on the example here:

https://learn.microsoft.com/en-us/ef/ef6/saving/transactions

specifically considering this

What EF does by default

In all versions of Entity Framework, whenever you execute SaveChanges() to insert, update or delete > on the database the framework will wrap that operation in a transaction. This transaction lasts only long enough to execute the operation and then completes. When you execute another such operation a > new transaction is started.

Starting with EF6 Database.ExecuteSqlCommand() by default will wrap the command in a transaction if > one was not already present. There are overloads of this method that allow you to override this behavior if you wish. Also in EF6 execution of stored procedures included in the model through APIs such as ObjectContext.ExecuteFunction() does the same (except that the default behavior cannot at the moment be overridden).

In either case, the isolation level of the transaction is whatever isolation level the database provider considers its default setting. By default, for instance, on SQL Server this is READ COMMITTED.

Entity Framework does not wrap queries in a transaction.

This default functionality is suitable for a lot of users and if so there is no need to do anything different in EF6; just write the code as you always did.

However some users require greater control over their transactions – this is covered in the following sections.

So what I tried was wrapping my query in a transaction

using (var scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted })) using (var context = new AthenaContext()) { var query = context.Mytable; Console.WriteLine(query.ToQueryString()); query.ToList(); } scope.Complete(); }

I have looked at both the output ToQuery string and looked over in SQL Server Profiler and can see the query does not result in one with an isolation mode read uncommitted or adding a WITH (NOLOCK).

Has anyone encountered this? Any insight is appreciated appreciated SQL Server version is 2016:

Microsoft SQL Server 2016 (SP3-CU1-GDR) (KB5040944) - 13.0.7037.1 (X64) Jun 19 2024 14:36:41 Developer Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: )

Why changing your EF6 transaction approach with TransactionScope? EF Core supports the exact same code as EF6, e.g. using var transaction = db.Database.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted); — Ivan Stoev
– Ivan Stoev, Commented Sep 3, 2024 at 17:17

Steve Py · Accepted Answer · 2024-09-03 23:49:01Z

EF Core works fine with an explicit transaction, no need for TransactionScope. To read the current uncommitted transaction state:

using var context =new AthenaContext(); using var tx = context.Database.BeginTransaciton(IsolationLevel.ReadUncommitted); var query = context.Mytable.AsNotracking(); Console.WriteLine(query.ToQueryString()); var results = query.ToList();

Note: that reading data from a privately scoped DbContext won't matter, but if you are using an injected DbContext it is a good idea to use AsNoTracking() to ensure that the query returns the database state and not potentially tracked entities. This also avoids putting uncommitted data into the tracking cache.

You can verify the behaviour on a Read Committed database by running a query with an open-ended transaction:

begin tran; update MyTable set SomeStatusId = 2 where Id = 1 /* commit tran; rollback; */

Here if I do not create the ReadUncommitted transaction, my code will block until I either commit or rollback the above transaction. If I add the ReadUncommitted and execute, I will get back the updated SomeStatusId value of 2 while the transaction is still in progress.

This won't show up as anything in any captured SQL or even SQL Server Profiler as the explicit transaction will be started but the profiler does not track/report on the isolation level. (Aparently EF Profiler from NHibernating Rhinos may log the isolation level used for transactions started through the EF DbContext)

Thank you very much, you are correct the process wasn't blocking at all, an insert/update/Delete was able to occur at the same time, the mistake I was making was I was expecting the query to show some kind of Nolock but that is already taken care of by the transactionscope uncommitted.

Collectives™ on Stack Overflow

Using Entity Framework Core v8.0.8 DbContext and TransactionScope to read uncommitted from a table

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related