C# NetCore Transaction null

Solution 1:

Remove this line

IDbContextTransaction transaction = localcontext.Database.CurrentTransaction;

If there's an active TrasnactionScope your SqlConnection will be automatically enlisted in it. The whole point of TransactionScope is that your data access methods can be completely free of transaction handling. Then in some outer business layer or controller method, the transaction is orchestrated.

The reason CurrentTransaction is null is that there are two different ways to handle transactions. If you want the current System.Transactions.Transaction, you get it with System.Transactions.Transaction.Current.

Stepping back, there are 3 separate ways to manage transactions with SqlConnection.

1. TSQL Transactions: You can use TSQL API directly issuing BEGIN TRAN, COMMIT TRAN, etc.

2. ADO.NET Transactions: SqlConnection.BeginTrasaction, IDbTransaction , SqlTransaction, etc. This is a wrapper over the TSQL API, and is a PITA because it introduces a useless requirement to pass the SqlTransaction to each SqlCommand that you want to enlist in the Transaction. But enlisting TSQL commands in the current transaction is not optional, and never has been. And that's a pain because methods that user SqlCommand may not know whether there is a transaction. Dapper and EF both wrap this API in their transaction handling methods.

3. System.Transactions Transactions: Partly because of this System.Transactions was introduced in .NET 2.0 as a new and unified way to handle transactions in .NET, and SqlClient added support for it. The main innovation of System.Transactions was adding "ambient" transactions. So code could be agnositc about whether there's a transaction and the right thing will just happen. When opening a SqlConnection if there is a current Transaction, the SqlConnection will be enlisted in it, and the changes made using the SqlConnection will not be committed until the Transaction is committed. And there is no need for your ADO.NET code to know about the Transaction. Dapper and EF are both built on top of ADO.NET and SqlClient, so this all just works.

Solution 2:

It's easier to explain what's wrong by showing what the code should be:

using(var connection=new SqlConnection(_connectionString))
{
    await connection.ExecuteAsync("SET IDENTITY_INSERT [dbo].[Whatever] ON");
}

Where ExecuteAsync comes from Dapper.

There's no reason to create a transaction, much less a transaction scope, to execute a single command.

There's no reason to create a DbContext just to open a connection to the database either, or to execute raw SQL commands. DbContext isn't a database connection, it's job is to Map Objects to Relational data. There are no objects involved here.

To execute multiple commands there's no reason to use multiple connections. Just execute the commands one after the other. If it's really necessary, use an explicit database transaction around those commands. Or create the connection inside a single transaction scope.

Let's say you have an array with those commands, eg something read from a script file :

string[] commands=new[]{...};
using(var connection=new SqlConnection(_connectionString))
{
    await connection.OpenAsync();

    using (var transaction = connection.BeginTransaction())
    {
        foreach(var sql in commands)
        {
            await connection.ExecuteAsync(sql,transaction:transaction);
        }
        transaction.Commit();
    }
}

Doing the same thing using a TransactionScope only requires opening the connection inside the transaction scope.

string[] commands=new[]{...};

using( var scope = new TransactionScope(TransactionScopeOption.Required,
            System.TimeSpan.FromMinutes(10), TransactionScopeAsyncFlowOption.Enabled)
using(var connection=new SqlConnection(_connectionString))
{
    await connection.OpenAsync();

    foreach(var sql in commands)
    {
        await connection.ExecuteAsync(sql);
    }
    scope.Complete();
}