EF Core 3.1 set default IsolationLevel in DbContext

Question

I need to apply the same IsolationLevel to all the operation executed by the DbContext, so I don't have to specify it each time I use it. Is there any way to do it?

I'm working with EF Core 3.1 and SqlServer

UPDATE: after some research and tests I found out that what I'm looking for is to apply WITH (NOLOCK) to the tables. Also I tried to apply the transaction scope to a single query and tried to read data from a locked table and it is not working.

This is the code I used:

using var transactionScope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted });

// query execution

transactionScope.Complete();

This code have been copied from: https://learn.microsoft.com/en-gb/ef/core/saving/transactions, the only difference is on the IsolationLevel.

if you're using stored procedures - SET ON; inside your stored proc. If you're using plain text, add "SET ON;" to your command text. — yob, Mar 05 '21 at 16:08
In this context I don't care about the reliability of the data and I want to read it in Read Uncommitted IsolationLevel — Emanuele, Mar 05 '21 at 16:08
@yob I'm not using neither stored procedure or plain text. I need to specify it once in the context so that each operation executed by the context will not need to specify it's own isolation level — Emanuele, Mar 05 '21 at 16:14

score 1 · Accepted Answer · answered Mar 09 '21 at 09:34

https://stackoverflow.com/a/53082098/12919581

I have this solution that is the best I could found, even if in my opinion is not good enought.

It generates this warning that could create some problems for future update.

Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerQuerySqlGenerator is an internal API that supports the Entity Framework Core infrastructure and not subject to the same compatibility standards as public APIs. It may be changed or removed without notice in any release.

I'm not adopting it, but at the moment it is the only way I could found to reach the goal of reading uncommitted data without locking any table.

score 0 · Answer 2 · answered Mar 05 '21 at 16:14

0

EF Core is an ORM that can run against multiple data sources, not all of them changing transaction isolation level, because of this, in EF this property is readonly. If you want to change it for SQL Server, you will need to do it from within your T-Sql code.

answered Mar 05 '21 at 16:14

Mauricio Atanache

2,424
1
13
18

But you can do it on a single operation specifing the TransactionScope. I think it should be possible to do it also at context level. What am I missing? – Emanuele Mar 05 '21 at 16:23
I don't believe this is possible at the context level, as you mention, you may want to embed you context operations inside a transaction scope to change the default context isolation level, but this is not recommended. I'd just change the full database default isolation level if you want it for all operations in your database. – Mauricio Atanache Mar 05 '21 at 16:37
@Emanuele - yes, either using TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions{IsolationLevel = .... or using DbCommandInterceptor (https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.diagnostics.dbcommandinterceptor?view=efcore-5.0) – yob Mar 05 '21 at 16:40
I find out that it is not possible to change the database isolation level to read uncommitted , it is not allowed by sql server. so I have to do it by code. at this point I don't think what I want to do can be done – Emanuele Mar 08 '21 at 17:02

score 0 · Answer 3 · answered Mar 05 '21 at 18:08

0

You should do it inside the chain-of-responsibility of Dotnet core middlewares. before each action execution, you can start a transaction scope and set transaction isolation level and, end it after action execution. See middlewares and dotnet pipelines here:

public class Startup
{
    public void Configure(IApplicationBuilder app)
    {
       app.Use(async (context, next) =>
       {
           //begin your transaction scope.
           await next.Invoke();
           //finalize the corresponding scope.
       });
    }
 }

this pattern is very similar to unit-of-work implementation.

answered Mar 05 '21 at 18:08

Amir

1,214
7
10

This cannot work, I have discovered that the isolation level must be set for each query and can't be done at startup level. https://github.com/dotnet/SqlClient/issues/96 – Emanuele Mar 08 '21 at 17:04
It does not execute at startup level :| it's just inside the startup.cs class! it will be executed inside each request context :) – Amir Mar 08 '21 at 20:23
1

Yes, but it will be applied only to the first query. – Emanuele Mar 09 '21 at 09:27

EF Core 3.1 set default IsolationLevel in DbContext

3 Answers3