Dapper Read-Side Performance & Connection Pooling

Under the CQRS architectural pattern, read queries and write mutations possess fundamentally different technical and operational requirements:

Write Operations: Prioritize domain aggregate consistency, transactional boundaries, and event publishing (ideal for SqlSugar or EF Core);
Read Operations: Demand minimal latency, high-concurrency resilience, flexible multi-join projections, and zero memory allocation overhead.

BitzOrcas.Modern incorporates dedicated Dapper Read-Only Connection Pooling within src/Framework/BitzOrcas.Infrastructure.Dapper. By isolating read replica connection strings from master write instances, analytical dashboards and high-throughput paginated queries run directly on near-bare-metal ADO.NET pipelines.

1. Read Connection Factory: `IDapperConnectionFactory`

The framework defines an explicit factory abstraction resolving database connections while managing lifecycle lifespans:

using System.Data.Common;
using System.Threading;
using System.Threading.Tasks;

namespace BitzOrcas.Infrastructure.Dapper;

/// <summary>
/// Dedicated Dapper database connection factory port
/// </summary>
public interface IDapperConnectionFactory
{
    /// <summary>
    /// Creates an open connection bound to the read-only replica pool
    /// </summary>
    Task<DbConnection> CreateReadOnlyConnectionAsync(CancellationToken cancellationToken = default);

    /// <summary>
    /// Creates an open connection bound to the primary read-write master database
    /// </summary>
    Task<DbConnection> CreateMasterConnectionAsync(CancellationToken cancellationToken = default);
}

2. CQRS Query Handler: High-Performance Dashboard Query

The following example illustrates a complex multi-table query handler implementing Dapper projection alongside mandatory multi-tenant scoping:

using System.Collections.Generic;
using System.Threading;
using System.Threading.Tasks;
using BitzOrcas.Application.Abstractions.Tenancy;
using BitzOrcas.Domain.Abstractions.Queries;
using BitzOrcas.Domain.Results;
using BitzOrcas.Infrastructure.Dapper;
using Dapper;
using Mediator;

namespace BitzOrcas.Modules.Litigation.Application.Queries.Cases;

/// <summary>
/// Query retrieving a paginated case overview dashboard
/// </summary>
public sealed record GetCaseDashboardQuery(
    string? Keyword,
    string? CaseType,
    int PageIndex,
    int PageSize
) : IQuery<Result<PagedResult<CaseDashboardRowDto>>>;

/// <summary>
/// Dapper-backed query handler
/// </summary>
public sealed class GetCaseDashboardQueryHandler(
    IDapperConnectionFactory connectionFactory,
    ICurrentTenant currentTenant) : IQueryHandler<GetCaseDashboardQuery, Result<PagedResult<CaseDashboardRowDto>>>
{
    public async ValueTask<Result<PagedResult<CaseDashboardRowDto>>> Handle(
        GetCaseDashboardQuery query,
        CancellationToken cancellationToken)
    {
        var tenant = currentTenant.Tenant;
        if (!tenant.IsAvailable)
        {
            return Result.Failure<PagedResult<CaseDashboardRowDto>>(LitigationErrors.TenantRequired);
        }

        await using var connection = await connectionFactory.CreateReadOnlyConnectionAsync(cancellationToken);

        const string sqlCount = """
            SELECT COUNT(1)
            FROM LitigationCase c WITH (NOLOCK)
            WHERE c.TenantId = @TenantId
              AND c.IsDeleted = 0
              AND (@Keyword IS NULL OR c.Title LIKE '%' + @Keyword + '%' OR c.CaseNumber LIKE '%' + @Keyword + '%')
              AND (@CaseType IS NULL OR c.CaseType = @CaseType);
            """;

        const string sqlData = """
            SELECT
                c.Id AS CaseId,
                c.CaseNumber,
                c.Title,
                c.CaseType,
                c.ClaimAmount,
                c.Status,
                c.CreateTime,
                l.RealName AS LeadAttorneyName
            FROM LitigationCase c WITH (NOLOCK)
            LEFT JOIN SysUser l WITH (NOLOCK) ON c.LeadAttorneyId = l.Id
            WHERE c.TenantId = @TenantId
              AND c.IsDeleted = 0
              AND (@Keyword IS NULL OR c.Title LIKE '%' + @Keyword + '%' OR c.CaseNumber LIKE '%' + @Keyword + '%')
              AND (@CaseType IS NULL OR c.CaseType = @CaseType)
            ORDER BY c.CreateTime DESC
            OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;
            """;

        var parameters = new
        {
            TenantId = tenant.EffectiveTenantId,
            Keyword = string.IsNullOrWhiteSpace(query.Keyword) ? null : query.Keyword,
            CaseType = string.IsNullOrWhiteSpace(query.CaseType) ? null : query.CaseType,
            Offset = (query.PageIndex - 1) * query.PageSize,
            PageSize = query.PageSize
        };

        var totalCount = await connection.ExecuteScalarAsync<int>(new CommandDefinition(sqlCount, parameters, cancellationToken: cancellationToken));
        var items = await connection.QueryAsync<CaseDashboardRowDto>(new CommandDefinition(sqlData, parameters, cancellationToken: cancellationToken));

        var pagedResult = new PagedResult<CaseDashboardRowDto>(items.AsList(), totalCount, query.PageIndex, query.PageSize);
        return Result.Success(pagedResult);
    }
}

3. Security Guidelines & Invariants

When writing raw SQL with Dapper, engineering teams must maintain strict invariants:

No Inline String Concatenation: Dynamic query arguments must pass via @Parameter names or DynamicParameters, preventing SQL injection vulnerabilities;
Mandatory Tenant Scoping in WHERE Clauses: Because raw SQL bypasses ORM interceptors, every single query must explicitly include TenantId = @TenantId;
Read Hints: For analytical queries on SQL Server, WITH (NOLOCK) prevents blocking read transactions while mutations proceed on the master;
ANSI Pagination Standards: Utilize standard OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY syntax rather than pulling unfiltered result sets into memory.

Architecture Overview: Multi-ORM Dual-Engine Architecture & Selection Matrix
EF Core Guide: EF Core Isomorphic Integration & Interceptor Guide
ADR Reference: ADR 0302: Multi-ORM Isomorphic Persistence