UseTransaction() operator to automatically execute a query in a specific isolation level (e.g. split query)

[dario-l]

I'm facing with something related to #33826. We are trying to adopt RCSI and Snapshot Isolation with specific cases.

Is there any quick extension method to wrap executed query in specified transaction with isolation level?

Something like this:

// We have enabled READ_COMMITTED_SNAPSHOT Isolation

await _db.Table1
    .UseTransaction(IsolationLevel.Snapshot)
    .AsSplitQuery()
    .Include(x => x.Table2)
    .Include(x => x.Table3)
    .Include(x => x.Table4).ThenInclude(x => x.Table4_1)
    .Include(x => x.Table5)
    .Where(...)
    .ToListAsync(...);

Or maybe I could add this by some interceptor but i do now know which one?

UPDATE
@roji Overall, the idea behind this #30353 task isn't that bad? 😃

[roji]

It's pretty trivial to use EF's existing transaction management APIs for this, so the above would just be a bit of sugar. Am noting that this sugar could be relevant in some edge cases without split query, e.g. using snapshot isolation in SQL Server where RCSI is not enabled. Putting this in the backlog.

Note that RCSI is different from snapshot isolation. Your above code sample doesn't really make sense - if you explicitly start a transaction in IsolationLevel.Snapshot (as you'd like to do above), then it doesn't matter whether your database has enabled RCSI or not.

Overall, the idea behind this #30353 task isn't that bad?

Possibly...

[dario-l]

I should be more specific but... I understand your opinion. 😃
I do not like the idea of global setting like the Xriuk proposed in #30353 but on the query basis could be a really neat feature.

Maybe I will explain little bit more.

Our application is made with this default behavior in mind. Almost every method when we use DbContext.SaveChanges (pseudo) code looks like this:

    Fetch/Query aggregates/entities

    Do business logic which leads to changing state

    SaveChanges - transaction is scoped only to this one line

We have enabled RCSI and that's why snapshot isolation transaction is crucial when we want use SplitQuery.

PS
Of course sometimes we need to embrace in transaction (with snapshot isolation) the whole business logic code because of lazy-loading. But that is another problem to solve depending on the use case.

[dario-l]

Quick handy extension methods:

    /// <summary>
    /// Wrap query execution in transaction scope with given transaction isolation level.
    /// </summary>
    public static async Task<List<TSource>> ToListAsync<TSource>(
        this IQueryable<TSource> source,
        IsolationLevel isolationLevel,
        CancellationToken cancellationToken)
    {
        using var scope = new TransactionScope(
            TransactionScopeOption.Required,
            new TransactionOptions { IsolationLevel = isolationLevel },
            TransactionScopeAsyncFlowOption.Enabled);

        var result = await source.ToListAsync(cancellationToken);
        scope.Complete();

        return result;
    }
    
    
    /// <summary>
    /// Wrap query execution in transaction scope with given transaction isolation level.
    /// </summary>
    public static async Task<TSource?> FirstOrDefaultAsync<TSource>(
        this IQueryable<TSource> source,
        IsolationLevel isolationLevel,
        CancellationToken cancellationToken)
    {
        using var scope = new TransactionScope(
            TransactionScopeOption.Required,
            new TransactionOptions { IsolationLevel = isolationLevel },
            TransactionScopeAsyncFlowOption.Enabled);

        var result = await source.FirstOrDefaultAsync(cancellationToken);
        scope.Complete();

        return result;
    }

[roji]

Sure, I understand.

I summarized the different options and my opinions in #30353 (comment). Let's keep this issue to track the per-query UseTransaction(), and #30353 to track the global option.