command.ExecuteNonQueryAsync() thorws error: 'BeginExecuteNonQuery requires the command to have a transaction

Question

I have a function that executes a stored procedure:

public async Task RunFinanceSummaryAggregationAsync()
{

    connection ??= dBcontext.Database.GetDbConnection();
    using var command = connection.CreateCommand();
    command.CommandType = CommandType.StoredProcedure;
    command.CommandText = "sp_FinanceSummaryAggregation";

    if (connection.State != ConnectionState.Open)
        await connection.OpenAsync();

    await command.ExecuteNonQueryAsync();

}

This function is called by an Azure Service bus. For that I have a message object and a message handler.

public record DrawCompletedMessage() : IMessage
{
}

public class DrawCompletedMessageHandler : IMessageHandler<DrawCompletedMessage>
{
    private readonly IReportService reportService;

    private readonly ILogger<DrawCompletedMessageHandler> logger;

    public DrawCompletedMessageHandler(IReportService reportService, ILogger<DrawCompletedMessageHandler> logger)
    {
        this.reportService = reportService;
        this.logger = logger;
    }

    public async Task HandleAsync(DrawCompletedMessage message, CancellationToken cancellationToken)
    {
        await reportService.RunFinanceSummaryAggregationAsync();
    }
}

The queue is registered on WebApplicationBuilder:

public static void Configure(WebApplicationBuilder builder)
{
    builder.Services.AddMessaging().AddServiceBus(serviceBus =>
    {
        var drawCompletedQueue = builder.Configuration.GetQueueConfiguration("DrawCompletedQueue");

        serviceBus.AddNamespace(drawCompletedQueue.Namespace, drawCompletedQueue.ConnectionString);

        serviceBus.AddQueue(drawCompletedQueue.Namespace, drawCompletedQueue.QueueName, q =>
        {
            q.AddMessage<DrawCompletedMessage, DrawCompletedMessageHandler>();
        });
    });
}

The RunFinanceSummaryAggregationAsync is get called, and when executes the line:

await command.ExecuteNonQueryAsync();

it throws an error:

System.InvalidOperationException: 'BeginExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized.'

In my stored procedure I don't have a transaction it has a marge table or update table command.

thnx

Answer 1

Here is the solution. Might help someone.

var executionStrategy = dBcontext.Database.CreateExecutionStrategy();
await executionStrategy.ExecuteAsync(async () =>
{ 
    using var transaction = await dBcontext.Database.BeginTransactionAsync();
    try
    {
        using var connection = new SqlConnection(dBcontext.Database.GetConnectionString());

        if (connection.State != ConnectionState.Open)
            await connection.OpenAsync();

        var command = new SqlCommand("exec sp_FinanceSummaryAggregation", connection);
        await command.ExecuteNonQueryAsync();

        transaction.Commit();
    }
    catch(Exception ex)
    {
        await transaction.RollbackAsync();
    }
});