Using Artisan in ASP.NET Core - lobodava/artisan-orm GitHub Wiki

A repository derived from RepositoryBase uses one SqlConnection and a mutable Transaction property, so it is not safe to share across concurrent operations. A unit of work is typically one HTTP request, one background work item, or one console operation. Scoped registration does not make parallel calls on the same repository safe.

For the source being prepared for 4.1.0, a connection-string repository owns its connection; a repository constructed with an external transaction borrows that transaction's existing connection. The ordinary scoped registrations below use the owning form. Disposing a borrowing repository leaves the external transaction and connection with their caller. See Transactions for the ownership contract and limitations in older packages.

In ASP.NET Core, the DI container handles this for you: register repositories as Scoped and let the framework dispose them when each request ends.

// Program.cs
var builder = WebApplication.CreateBuilder(args);
var connStr = builder.Configuration.GetConnectionString("DatabaseConnection")!;

builder.Services.AddScoped(_ => new UserRepository(connStr));
builder.Services.AddScoped(_ => new RecordRepository(connStr));

// Controller
public class UsersController : ControllerBase
{
    private readonly UserRepository _repo;

    public UsersController(UserRepository repo) => _repo = repo;

    [HttpGet("{id:int}")]
    public async Task<User?> Get(int id, CancellationToken ct) =>
        await _repo.ReadToAsync<User>("dbo.GetUserById", ct,
            new SqlParameter("@Id", id));
}

That is the whole pattern. The DI container disposes _repo at the end of the HTTP request automatically.

Because Scoped repos are shared across services within one request, a transaction started by one service is visible to the others as long as they use the same repository:

[HttpPost]
public async Task<IActionResult> Transfer(TransferDto dto, CancellationToken ct)
{
    // Both _accountService and _ledgerService inject the SAME AccountRepository
    // (Scoped), so commands they issue land in the same transaction.
    await _accountRepo.RunInTransactionAsync(async (tran, ct2) =>
    {
        await _accountService.WithdrawAsync(dto.From, dto.Amount, ct2);
        await _accountService.DepositAsync (dto.To,   dto.Amount, ct2);
        await _ledgerService .RecordAsync   (dto, ct2);
    }, ct);

    return Ok();
}

If the registration was Transient, each of those services would receive a different repository instance with its own connection, and the transaction started in one would not cover the others.

Scoped registration shares an instance of a particular repository registration; it does not automatically give UserRepository and RecordRepository the same connection or transaction. To coordinate different repository types, their constructors can pass the same active transaction to base(transaction, connectionString). The transaction owner performs commit/rollback and disposal, and commands execute sequentially. A completed transaction is rejected, and BeginTransaction* / RunInTransaction* cannot start another transaction while one is attached. Assigning the public Transaction property on an independently created connection is not equivalent. See sharing an external transaction.

IHostedService and BackgroundService are singletons themselves, so they cannot inject a Scoped repository directly. Use IServiceScopeFactory to create a fresh scope per work item:

public class ImportWorker : BackgroundService
{
    private readonly IServiceScopeFactory _scopeFactory;

    public ImportWorker(IServiceScopeFactory scopeFactory) =>
        _scopeFactory = scopeFactory;

    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        while (!stoppingToken.IsCancellationRequested)
        {
            // One scope per iteration -> one repository per iteration -> disposed at scope end.
            using var scope = _scopeFactory.CreateScope();
            var repo = scope.ServiceProvider.GetRequiredService<RecordRepository>();

            await repo.ImportNextBatchAsync(stoppingToken);

            await Task.Delay(TimeSpan.FromMinutes(1), stoppingToken);
        }
    }
}

If your worker is simple and doesn't need DI for anything else, using var repo = new RecordRepository(connStr); inside the loop is a fine alternative.

ADO.NET pools physical SQL Server connections per process, with pools separated by connection configuration and authentication context. Creating new MyRepo(connStr) creates a closed connection object; it does not open or borrow a physical connection. When an operation opens it, pooling can reuse an available connection instead of establishing a new authenticated connection. Actual latency depends on the network, authentication, server load, and pool state.

Keep pooling enabled for ordinary application use. Use Pooling=False for a specific test or operational requirement rather than copying it accidentally from a test configuration. A repository borrowing an external transaction reuses the connection its owner already opened.

# Production: configure a trusted server certificate. Pooling is on by default.
Data Source=...;Initial Catalog=...;Integrated Security=True;Encrypt=True;TrustServerCertificate=False;

# A test that deliberately excludes pooling can add Pooling=False.

Layered configuration works the same way as for any other service:

// appsettings.json + appsettings.Development.json + environment variables + user-secrets
var connStr = builder.Configuration.GetConnectionString("DatabaseConnection")!;

For developer-machine overrides, prefer .NET user-secrets over machine-name-suffixed config keys:

dotnet user-secrets set "ConnectionStrings:DatabaseConnection" "Data Source=..."

If you have many repository classes, you can roll your own bulk-registration extension to avoid one AddScoped line per type:

public static IServiceCollection AddRepositories(
    this IServiceCollection services,
    string connectionString,
    Assembly assembly)
{
    foreach (var t in assembly.GetTypes()
                              .Where(t => !t.IsAbstract &&
                                          typeof(RepositoryBase).IsAssignableFrom(t)))
    {
        services.AddScoped(t, sp => ActivatorUtilities.CreateInstance(sp, t, connectionString));
    }
    return services;
}

ActivatorUtilities.CreateInstance lets the constructor request additional DI dependencies (an ILogger<T>, etc.) alongside the connection string.

See also:

• About Connection Strings
• Transactions
• Getting Started