Skip to main content

Return to Question

deleted 36 characters in body; edited title
Source Link
marc_s
marc_s
  • 759.9k
  • 186
  • 1.4k
  • 1.5k

Using Entity Framework (8Core v8.0.8) DBContext DbContext and Transaction scopeTransactionScope to read uncommitted from a table

I'm currently looking at upgrading a dotnet.NET project (standard moving to dotnet.NET 8) that uses Entity frameworkFramework (6v6.4.4) to EF Core (8v8.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-2TB2 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)WITH (NOLOCK) is beneficial.

In EF 6.4.4, we performed this action aslike this:

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

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

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 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 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.

  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 toqueryToQuery string and looked over in sql profilerSQL Server Profiler and can see the query does not result in one with an isolation mode read uncommiteduncommitted or adding a WITH (NOLOCK)WITH (NOLOCK).

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

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

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: )

Using Entity Framework (8.0.8) DBContext and Transaction scope to read uncommitted from a table

I'm currently looking at upgrading a dotnet project (standard moving to dotnet 8) that uses Entity framework (6.4.4) to EF Core (8.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-2TB) 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 as:

I have attempted the same using EF Core 8.0.8 trying this, and 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.

  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 profiler and can see the query does not result in one with an isolation mode read uncommited or adding a WITH (NOLOCK)

Has anyone encountered this? Any insight is appreciated appreciated Sql version is 2016

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

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

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:

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.

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: )
Source Link
Mike
Mike
  • 1
  • 2

Using Entity Framework (8.0.8) DBContext and Transaction scope to read uncommitted from a table

I'm currently looking at upgrading a dotnet project (standard moving to dotnet 8) that uses Entity framework (6.4.4) to EF Core (8.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-2TB) 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 as:

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 this, and 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 profiler and can see the query does not result in one with an isolation mode read uncommited or adding a WITH (NOLOCK)

Has anyone encountered this? Any insight is appreciated appreciated Sql version is 2016

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