Dapper

Description

Dapper is the relational “micro-ORM” option in FlexBase: explicit SQL, minimal overhead, and high performance.

Flex wraps Dapper with Flex Dapper (a Dapper-first implementation of the Flex MicroORM abstraction). Your application code can stay provider-agnostic by using:

• IFlexDapperRepository for query/command helpers

• FlexDapperSql + ProviderSqlMap for provider-specific SQL variants

• Built-in soft-delete filtering (optional)

• Paging helpers

• Transaction helpers

If you prefer LINQ, change tracking, and migrations, see Entity Framework Core.

When to Use Dapper

Scenario	Recommendation
High-performance reads (lists, dashboards, reporting)	✅ Use Dapper/Flex Dapper
Complex/hand-tuned SQL (CTEs, vendor features, query hints)	✅ Use Dapper/Flex Dapper
Bulk operations (insert/update many rows)	✅ Use Dapper/Flex Dapper
Simple table-centric CRUD on TFlex entities	✅ Use FlexDapperTableRepository<TEntity>
Rich domain graphs + change tracking + navigation properties	Prefer Entity Framework Core

Configuration in DI

using Sumeru.Flex;

services.AddSingleton<IFlexDapperConnectionFactory, FlexDapperConnectionFactory>();

services.AddScoped(_ => new FlexDapperRepositoryOptions
{
    ProviderName = "SqlServer",
    ConnectionString = configuration.GetConnectionString("FlexWriteDb"),
    EnableSoftDeleteFilter = true,
    SoftDeleteColumnName = "IsDeleted"
});

appsettings.json

Where you store the connection string is app-specific, but most apps keep it under standard connection strings:

{
  "ConnectionStrings": {
    "FlexWriteDb": "Server=...;Database=...;User Id=...;Password=<store-in-secrets>;TrustServerCertificate=True"
  }
}

Sample usage (Queries / Handlers)

The typical pattern is: queries call QueryAsync/QuerySingleAsync, and handlers call ExecuteAsync/ExecuteScalarAsync, all through interfaces.

Query example (read model)

using Sumeru.Flex;

public sealed class ListActiveUsers
{
    private readonly IFlexDapperRepository _db;

    public ListActiveUsers(IFlexDapperRepository db) => _db = db;

    public Task<IReadOnlyList<UserListItemDto>> FetchAsync()
    {
        FlexDapperSql sql = "SELECT Id, UserName FROM Users WHERE IsActive = 1 ORDER BY UserName";
        return _db.QueryAsync<UserListItemDto>(sql);
    }
}

public sealed class UserListItemDto
{
    public string Id { get; set; }
    public string UserName { get; set; }
}

Handler example (command)

using Sumeru.Flex;

public sealed class CreateUserHandler
{
    private readonly IFlexDapperRepository _db;

    public CreateUserHandler(IFlexDapperRepository db) => _db = db;

    public Task<int?> ExecuteAsync(CreateUserCommand cmd)
    {
        FlexDapperSql sql = """
            INSERT INTO Users (UserName, Email, IsActive)
            VALUES (@UserName, @Email, 1);
            SELECT CAST(SCOPE_IDENTITY() as int);
            """;

        return _db.ExecuteScalarAsync<int>(sql, new { cmd.UserName, cmd.Email });
    }
}

public sealed record CreateUserCommand(string UserName, string Email);

Common Patterns

Custom SQL (Provider-Agnostic)

Use ProviderSqlMap + FlexDapperSql.FromProviderMap(...) to keep provider differences in one place.

using Sumeru.Flex;

public sealed class ListRecentAccounts
{
    private readonly IFlexDapperRepository _db;

    public ListRecentAccounts(IFlexDapperRepository db) => _db = db;

    public Task<IReadOnlyList<Accountability>> FetchAsync(int take)
    {
        var sql = FlexDapperSql.FromProviderMap(
            ProviderSqlMap.Create(map =>
                map.ForSqlServer($"SELECT TOP {take} * FROM Accountabilities ORDER BY CreatedDate DESC")
                   .ForPostgres($"SELECT * FROM \"Accountabilities\" ORDER BY \"CreatedDate\" DESC LIMIT {take}")));

        return _db.QueryAsync<Accountability>(sql);
    }
}

Paging

For custom queries, use QueryPageAsync with FlexDapperPagedQuery:

public sealed class GetUsersPaged
{
    private readonly IFlexDapperRepository _db;

    public GetUsersPaged(IFlexDapperRepository db) => _db = db;

    public Task<FlexDapperPagedResult<User>> FetchAsync(int pageNumber, int pageSize)
    {
        var query = new FlexDapperPagedQuery(
            dataSql: "SELECT * FROM Users ORDER BY Id OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY",
            countSql: "SELECT COUNT(1) FROM Users",
            pageNumber: pageNumber,
            pageSize: pageSize);

        return _db.QueryPageAsync<User>(query);
    }
}

Transactions

Use WithTransactionAsync to ensure atomic multi-statement work.

await _db.WithTransactionAsync(async (connection, tx) =>
{
    await connection.ExecuteAsync("update ...", transaction: tx);
    await connection.ExecuteAsync("insert ...", transaction: tx);
    return true;
});

Soft Delete Filter

• Enable filtering with FlexDapperRepositoryOptions.EnableSoftDeleteFilter and set SoftDeleteColumnName (commonly IsDeleted).

• FlexDapperTableRepository<TEntity> automatically appends the soft-delete predicate when the filter is enabled.

• To temporarily include soft-deleted rows:

using (_db.SuppressSoftDeleteFilter())
{
    var all = await _db.QueryAsync<MyEntity>("SELECT * FROM MyEntities");
}

Testing

• For unit tests, inject a fake IFlexDapperConnectionFactory and/or assert SQL text + parameters.

• For integration tests, use a real database (local/container) and reuse the same repository registrations.

See Also

• Relational Database Overview

• Entity Framework Core

• Provider SQL Map

• Full-Text Search