Rochas.DapperRepository

English | Português | Español | Français | Deutsch

English

What is it?

Rochas.DapperRepository is a lightweight generic repository for data access built on top of Dapper. It follows the Repository and Unit of Work patterns described by Martin Fowler, with no new query language, no state tracking and no dynamic proxies. It is designed to work directly with POCOs decorated with metadata attributes that drive querying, persistence and entity relationships.

It supports SQL Server, MySQL, PostgreSQL and SQLite through the DatabaseEngine enum.

The package targets .NET Standard 2.1 and is published on NuGet as Rochas.DapperRepository (current version 1.9.5).

Features

Full CRUD (Add, AddRange, Update, Remove, Get) with sync and async overloads
Typed filter queries (Query), with AND/OR conjunction via filterConjunction
Tokenized search over [Filterable] properties (Search)
Pagination returning PaginatedResult<T>
Fluent query builder with OrderBy, OrderByDescending, GroupBy (with aggregates) and Paginate — the builder is awaitable and generates SQL in the correct order (SELECT → FROM → WHERE → GROUP BY → ORDER BY)
Range filters with [RangeFilter]
Automatic composition loading with [RelatedEntity] (1-1, 1-N, N-1, N-N)
Pluggable caching via ICacheProvider (in-memory, Redis/Garnet, composite L1/L2, master-to-slave persistence channel)
DW/ETL support with [RelationalColumn] (automatic JOIN generation) and [DataAggregationColumn] (SUM, COUNT, MIN, MAX, AVG)
Raw SQL queries (QueryRaw)

Installation

dotnet add package Rochas.DapperRepository

Quick start

using Rochas.DapperRepository;
using Rochas.Data.Specification.Annotations;
using Rochas.Data.Specification.Enums;

var connString = "Data Source=sample.db;Cache=Shared";

using var repo = new GenericRepository<SampleEntity>(DatabaseEngine.SQLite, connString);

var result = await repo.Search("Celulares");

Registration in DI

services.AddScoped<IGenericRepository<SampleEntity>>(provider =>
    new GenericRepository<SampleEntity>(
        DatabaseEngine.SQLite,
        configuration.GetConnectionString("Default")));

Then inject IGenericRepository<SampleEntity> into your services.

Entity example

[Table] and [Column] are optional. When omitted, the class name is used for the table and the property name for the column. Use them when your database uses different names (for example, snake_case):

[Cacheable]
[Table("sample_entities")]
public class SampleEntity
{
    [Key]
    [AutoGenerated]
    public int Id { get; set; }

    [Column("creation_date")]
    public DateTime CreationDate { get; set; }

    [RangeFilter(LinkedRangeProperty = "CreationDateEnd")]
    public DateTime CreatedAt { get; set; }

    [NotMapped]
    public DateTime CreationDateEnd { get; set; }

    [Filterable]
    public string Name { get; set; }

    public int Age { get; set; }

    public bool Active { get; set; }

    [RelatedEntity(Cardinality = RelationCardinality.OneToMany,
                   ForeignKeyAttribute = "ParentId")]
    public IList<ChildEntity> Childs { get; set; }
}

CRUD operations

repo.Add(new SampleEntity { Name = "Renato Rocha" });

repo.AddRange(list);

var filter = new SampleEntity { DocNumber = 12345 };
var entityToUpdate = await repo.Get(filter);
entityToUpdate.Age = 40;
int affected = await repo.Update(entityToUpdate, filter);

int removed = await repo.Remove(filter);

Query (typed filter)

var all = await repo.Query(new SampleEntity()); // all records

var filter = new SampleEntity { Name = "Roberto", Email = "gmail.com" };
var results = await repo.Query(filter, filterConjunction: true);

Use the filterConjunction parameter to define the behavior of the attributes in the query: logical AND when enabled, logical OR when disabled. With conjunction the applied criterion is equality, otherwise similarity.

Search (tokenized over `[Filterable]` properties)

var results = await repo.Search("Paulo");

var page = await repo.Search("Paulo", page: 1, pageSize: 20);

Pagination

var result = await repo.Query(filter, page: 1, pageSize: 10);

var result = repo.QuerySync(filter, page: 1, pageSize: 10);

PaginatedResult<T> exposes Items, TotalCount, Page, PageSize and PageCount.

Fluent query builder

var result = await repo.Query(filter)
    .OrderBy("Name")
    .Paginate(1, 10);

var result = await repo.Query(filter)
    .GroupBy("Category")
    .OrderByDescending("Price");

var list = repo.QuerySync(filter)
    .OrderBy("Name")
    .ToList();

The order of the methods does not matter; the builder collects the sorting and grouping attributes and executes the query in the correct SQL order.

GroupBy with aggregates

var aggregates = new Dictionary<string, DataAggregationType>
{
    { "Price", DataAggregationType.Sum },
    { "Id", DataAggregationType.Count }
};
var result = await repo.Query(filter).GroupBy(["Category"], aggregates);

The builder generates the SELECT ... GROUP BY ... automatically with the requested aggregation functions (SUM, COUNT, MIN, MAX, AVG).

With aggregates, the generated SELECT contains only the group keys and the aggregate expressions — required by PostgreSQL and SQL Server, which reject ungrouped bare columns.

DW/ETL fact entities

For Data Warehouse and ETL work, [RelationalColumn] generates automatic JOINs (INNER/LEFT) with dimensional tables and [DataAggregationColumn] generates computed aggregate columns:

[Table("fact_sales")]
public class FactSalesEntity
{
    [Key] [AutoGenerated]
    public int Id { get; set; }

    [Column("product_id")]
    public int ProductId { get; set; }

    [Column("total_amount")]
    public decimal TotalAmount { get; set; }

    // Automatic JOIN: dim_product.product_name AS ProductName
    [RelationalColumn(
        TableName = "dim_product",
        ColumnName = "product_name",
        ColumnAlias = "ProductName",
        KeyColumn = "product_id",
        ForeignKeyColumn = "id",
        JunctionType = RelationalJunctionType.Mandatory)]
    public string ProductName { get; set; }

    // Aggregation: SUM(fact_sales.total_amount) AS SumTotalAmount
    [DataAggregationColumn(ColumnName = "total_amount", AggregationType = DataAggregationType.Sum)]
    public decimal SumTotalAmount { get; set; }
}

var result = await repo.Query(new FactSalesEntity());

Generated SQL:

SELECT dim_product.product_name AS ProductName,
       SUM(fact_sales.total_amount) AS SumTotalAmount,
       fact_sales.id, fact_sales.product_id, fact_sales.total_amount
FROM fact_sales
INNER JOIN dim_product ON fact_sales.product_id = dim_product.id
WHERE 1 = 1

RelationalJunctionType.Mandatory generates INNER JOIN; Optional generates LEFT JOIN. Properties marked with [RelationalColumn] or [DataAggregationColumn] are read-only and ignored in INSERT/UPDATE operations.

Pluggable caching

Mark the entity as [Cacheable] to enable caching. The default provider is in-memory, but Redis, Garnet or composite providers can be injected without changing repository code:

DataCache.Initialize(memorySizeLimit: 100); // in-memory, 100 MB limit
DataCache.Initialize();                     // in-memory, no limit

DataCache.Initialize(new DistributedCacheProvider("localhost:6379")); // Redis / Garnet

DataCache.Initialize(new CompositeCacheProvider( // L1 in-memory + L2 distributed
    new InMemoryCacheProvider(),
    new DistributedCacheProvider("redis:6379")));

Benchmark — DapperRepository vs EF Core

Linux, .NET 9.0.20, SQLite, 5,000 rows (+ composition: 21 parents), ORM 2.0.4 + Wrapper 1.5.0 — 80 cols ≈ 152 ms

Insert          EF ####### (13.26 ms) Tie
                ORM ======= (13.44 ms)
GetById         EF # (1.00 ms)
                ORM = (0.40 ms) ORM 2.5x
QueryFiltered   EF # (0.87 ms)
                ORM = (0.27 ms) ORM 3.2x
Search2F-COUNT  EF # (2.03 ms)
                ORM = (1.54 ms) ORM 1.3x
Search2F-LISTA  EF ############################################# (86.12 ms)
                ORM ======================================= (73.84 ms) ORM 1.2x
Search8F-COUNT  EF # (2.21 ms)
                ORM = (1.53 ms) ORM 1.4x
SortAsc-5000    EF ################################################ (91.92 ms)
                ORM ============================================== (87.08 ms) ORM 1.1x
SortAscFirst    EF ## (4.70 ms)
                ORM == (3.52 ms) ORM 1.3x
SortDesc-5000   EF ############################################################################ (145.08 ms)
                ORM ============================================================== (117.30 ms) ORM 1.2x
SortFiltFirst   EF ### (5.48 ms)
                ORM === (6.07 ms) EF 1.1x
SortMulti       EF ################################################# (93.94 ms)
                ORM ============================================== (87.91 ms) ORM 1.1x
SortFilt-4500   EF ############################################################ (113.81 ms)
                ORM ========================================== (80.00 ms) ORM 1.4x
GroupBy-1col    EF ############## (26.52 ms)
                ORM = (1.29 ms) ORM 20.6x
GroupBy-2col    EF #################### (37.21 ms)
                ORM ====== (11.82 ms) ORM 3.1x
GroupBy-AGG     EF ####### (12.41 ms)
                ORM == (3.38 ms) ORM 3.7x
GroupBy-ORDER   EF ## (4.26 ms)
                ORM == (2.95 ms) ORM 1.4x
GroupBy-MULTIAG EF ###### (11.75 ms)
                ORM === (5.84 ms) ORM 2.0x
GroupBy-HAVING* EF # (1.15 ms)
                ORM = (1.29 ms) EF 1.1x
BulkInsert-100  EF ################################# (61.99 ms)
                ORM ============= (24.58 ms) ORM 2.5x
QueryRaw-50     EF ### (5.40 ms)
                ORM == (4.69 ms) ORM 1.2x
QueryBuildSort  EF ############################################## (88.02 ms) Tie
                ORM =============================================== (89.90 ms)
Update          EF ####################### (43.40 ms) Tie
                ORM ====================== (42.44 ms)
Delete          EF ################ (30.27 ms)
                ORM =============== (28.36 ms) ORM 1.1x
CountSync       EF # (1.00 ms)
                ORM = (0.63 ms) ORM 1.6x
SearchPag-20    EF ### (6.04 ms)
                ORM === (5.38 ms) ORM 1.1x
Search2F-async  ORM = (1.35 ms) ref. no pair **
PersistComp-10  EF ######### (16.20 ms)
                ORM ======= (12.70 ms) ORM 1.3x
PersistComp-50  EF ########## (19.86 ms)
                ORM ========= (16.22 ms) ORM 1.2x
PersistComp-10a EF ######## (15.65 ms)
                ORM ======= (13.68 ms) ORM 1.1x
PersistComp-50a EF ########## (19.13 ms)
                ORM ======== (15.95 ms) ORM 1.2x
LoadComp-10     EF ## (2.88 ms)
                ORM = (0.38 ms) ORM 7.6x
LoadComp-50     EF ### (5.05 ms)
                ORM = (0.74 ms) ORM 6.9x
LoadComp-10a    EF # (2.20 ms)
                ORM = (0.40 ms) ORM 5.5x
LoadComp-50a    EF ### (5.85 ms)
                ORM = (0.74 ms) ORM 7.9x
QueryComp-21    EF ################ (30.40 ms)
                ORM == (4.28 ms) ORM 7.1x
QueryComp-21a   EF ########## (18.58 ms)
                ORM == (4.02 ms) ORM 4.6x
PaginateComp    EF #### (8.08 ms)
                ORM = (2.17 ms) ORM 3.7x
PaginateComp-a  EF ##### (9.16 ms)
                ORM = (2.36 ms) ORM 3.9x

Score: ORM 32 × EF 2 × Tie 3 × ref 1.

GroupBy HAVING: ORM 2.0.4 emits no HAVING (capability gap, not a bug). ** ORM-only reference (no symmetric pair).

Benchmark — Channel vs Replica (Cluster Replication)

PersistenceChannel (CacheIndexer) vs DapperRepository native replicaConnStrings, 3-node SQLite cluster

Scenario	Channel	Replica	Speedup
Single Invoice	2 ms (500 inv/s)	1 ms (1000 inv/s)	Replica 2.0x
Bulk 100	41 ms (2439 inv/s)	287 ms (348 inv/s)	Channel 7.0x
Bulk 1,000	783 ms (1277 inv/s)	1,697 ms (589 inv/s)	Channel 2.2x
Delete 10	17 ms	176 ms	Channel 10.4x
Update 5	30 ms (15 rows)	62 ms (15 rows)	Channel 2.1x

Median of 3 rounds (1st warms up); Total = burst (enqueue) + drain to the last node (FlushAsync/barrier); delivery asserted per node (1/1/1, 100/100/100, 1000/1000/1000, 0/0/0). Bus re-Put appends (no upsert); bus update is full-entity + filter.

Tests and coverage

The Rochas.DapperRepository.Test project (xUnit, net9.0) contains 227 tests covering async CRUD, composition, builders, GROUP BY/aggregates, pluggable cache, SQL parsers, type handlers and engine detection, with 80% line coverage on the main assembly and 100% on Rochas.Data.Specification.

Português

O que é?

Rochas.DapperRepository é um repositório genérico leve para acesso a dados construído sobre o Dapper. Segue os padrões de Repositório e Unidade de Trabalho descritos por Martin Fowler, sem novas linguagens de consulta, sem controle de estado e sem proxies dinâmicos. Foi desenhado para uso direto com POCOs decorados com atributos de metadados que orientam a consulta, a persistência e os relacionamentos entre entidades.

Suporta SQL Server, MySQL, PostgreSQL e SQLite através do enum DatabaseEngine.

O pacote tem como alvo o .NET Standard 2.1 e é publicado no NuGet como Rochas.DapperRepository (versão atual 1.9.5).

Funcionalidades

CRUD completo (Add, AddRange, Update, Remove, Get) com sobrecargas síncronas e assíncronas
Consultas por filtro tipado (Query), com conjunção E/OU via filterConjunction
Busca tokenizada sobre propriedades [Filterable] (Search)
Paginação retornando PaginatedResult<T>
Builder fluente de consultas com OrderBy, OrderByDescending, GroupBy (com agregações) e Paginate — o builder é awaitable e gera o SQL na ordem correta (SELECT → FROM → WHERE → GROUP BY → ORDER BY)
Filtros de intervalo com [RangeFilter]
Leitura automática de composição com [RelatedEntity] (1-1, 1-N, N-1, N-N)
Cache plugável via ICacheProvider (in-memory, Redis/Garnet, composto L1/L2, canal de persistência master→slave)
Suporte a DW/ETL com [RelationalColumn] (geração automática de JOIN) e [DataAggregationColumn] (SUM, COUNT, MIN, MAX, AVG)
Consultas SQL diretas (QueryRaw)

Instalação

dotnet add package Rochas.DapperRepository

Exemplo de uso rápido

using Rochas.DapperRepository;
using Rochas.Data.Specification.Annotations;
using Rochas.Data.Specification.Enums;

var connString = "Data Source=sample.db;Cache=Shared";

using var repo = new GenericRepository<SampleEntity>(DatabaseEngine.SQLite, connString);

var result = await repo.Search("Celulares");

Registro no DI

services.AddScoped<IGenericRepository<SampleEntity>>(provider =>
    new GenericRepository<SampleEntity>(
        DatabaseEngine.SQLite,
        configuration.GetConnectionString("Default")));

Em seguida, injete IGenericRepository<SampleEntity> nos seus serviços.

Exemplo de entidade

[Table] e [Column] são opcionais. Quando omitidos, o nome da classe é usado para a tabela e o nome da propriedade para a coluna. Use-os quando o banco de dados usar nomes diferentes (por exemplo, snake_case):

[Cacheable]
[Table("sample_entities")]
public class SampleEntity
{
    [Key]
    [AutoGenerated]
    public int Id { get; set; }

    [Column("creation_date")]
    public DateTime CreationDate { get; set; }

    [RangeFilter(LinkedRangeProperty = "CreationDateEnd")]
    public DateTime CreatedAt { get; set; }

    [NotMapped]
    public DateTime CreationDateEnd { get; set; }

    [Filterable]
    public string Name { get; set; }

    public int Age { get; set; }

    public bool Active { get; set; }

    [RelatedEntity(Cardinality = RelationCardinality.OneToMany,
                   ForeignKeyAttribute = "ParentId")]
    public IList<ChildEntity> Childs { get; set; }
}

Operações CRUD

repo.Add(new SampleEntity { Name = "Renato Rocha" });

repo.AddRange(list);

var filter = new SampleEntity { DocNumber = 12345 };
var entityToUpdate = await repo.Get(filter);
entityToUpdate.Age = 40;
int affected = await repo.Update(entityToUpdate, filter);

int removed = await repo.Remove(filter);

Query (consulta por filtro tipado)

var all = await repo.Query(new SampleEntity()); // listar todos

var filter = new SampleEntity { Name = "Roberto", Email = "gmail.com" };
var results = await repo.Query(filter, filterConjunction: true);

Utilize o parâmetro filterConjunction para definir o comportamento dos atributos na consulta: conjunção lógica E quando ligado, disjunção lógica OU quando desligado. Com conjunção o critério aplicado é de igualdade, caso contrário de semelhança.

Search (busca tokenizada em propriedades `[Filterable]`)

var results = await repo.Search("Paulo");

var page = await repo.Search("Paulo", page: 1, pageSize: 20);

Paginação

var result = await repo.Query(filter, page: 1, pageSize: 10);

var result = repo.QuerySync(filter, page: 1, pageSize: 10);

PaginatedResult<T> expõe Items, TotalCount, Page, PageSize e PageCount.

Builder fluente de consultas

var result = await repo.Query(filter)
    .OrderBy("Name")
    .Paginate(1, 10);

var result = await repo.Query(filter)
    .GroupBy("Category")
    .OrderByDescending("Price");

var list = repo.QuerySync(filter)
    .OrderBy("Name")
    .ToList();

A ordem dos métodos não importa; o builder coleta os atributos de ordenação e agrupamento e executa a consulta na ordem correta do SQL.

GroupBy com agregações

var aggregates = new Dictionary<string, DataAggregationType>
{
    { "Price", DataAggregationType.Sum },
    { "Id", DataAggregationType.Count }
};
var result = await repo.Query(filter).GroupBy(["Category"], aggregates);

O builder gera o SELECT ... GROUP BY ... automaticamente com as funções de agregação solicitadas (SUM, COUNT, MIN, MAX, AVG).

Com agregações, o SELECT gerado contém só as chaves de grupo e as expressões agregadas — exigido pelo PostgreSQL e SQL Server, que rejeitam colunas soltas fora do agrupamento.

Entidades fato DW/ETL

Para trabalhos de Data Warehouse e ETL, [RelationalColumn] gera JOINs automáticos (INNER/LEFT) com tabelas dimensionais e [DataAggregationColumn] gera colunas calculadas de agregação:

[Table("fact_sales")]
public class FactSalesEntity
{
    [Key] [AutoGenerated]
    public int Id { get; set; }

    [Column("product_id")]
    public int ProductId { get; set; }

    [Column("total_amount")]
    public decimal TotalAmount { get; set; }

    // JOIN automático: dim_product.product_name AS ProductName
    [RelationalColumn(
        TableName = "dim_product",
        ColumnName = "product_name",
        ColumnAlias = "ProductName",
        KeyColumn = "product_id",
        ForeignKeyColumn = "id",
        JunctionType = RelationalJunctionType.Mandatory)]
    public string ProductName { get; set; }

    // Agregação: SUM(fact_sales.total_amount) AS SumTotalAmount
    [DataAggregationColumn(ColumnName = "total_amount", AggregationType = DataAggregationType.Sum)]
    public decimal SumTotalAmount { get; set; }
}

var result = await repo.Query(new FactSalesEntity());

SQL gerado:

SELECT dim_product.product_name AS ProductName,
       SUM(fact_sales.total_amount) AS SumTotalAmount,
       fact_sales.id, fact_sales.product_id, fact_sales.total_amount
FROM fact_sales
INNER JOIN dim_product ON fact_sales.product_id = dim_product.id
WHERE 1 = 1

RelationalJunctionType.Mandatory gera INNER JOIN; Optional gera LEFT JOIN. Propriedades marcadas com [RelationalColumn] ou [DataAggregationColumn] são somente leitura e ignoradas em operações de INSERT/UPDATE.

Cache plugável

Marque a entidade com [Cacheable] para habilitar o cache. O provedor padrão é in-memory, mas provedores Redis, Garnet ou compostos podem ser injetados sem alterar o código do repositório:

DataCache.Initialize(memorySizeLimit: 100); // in-memory, limite de 100 MB
DataCache.Initialize();                     // in-memory, sem limite

DataCache.Initialize(new DistributedCacheProvider("localhost:6379")); // Redis / Garnet

DataCache.Initialize(new CompositeCacheProvider( // L1 in-memory + L2 distribuído
    new InMemoryCacheProvider(),
    new DistributedCacheProvider("redis:6379")));

📊 Benchmark — DapperRepository vs EF Core

Linux, .NET 9.0.20, SQLite, 5.000 linhas (+ composição: 21 pais), ORM 2.0.4 + Wrapper 1.5.0 — 80 col ≈ 152 ms

Insert          EF ####### (13,26 ms) Empate
                ORM ======= (13,44 ms)
GetById         EF # (1,00 ms)
                ORM = (0,40 ms) ORM 2.5x
QueryFiltered   EF # (0,87 ms)
                ORM = (0,27 ms) ORM 3.2x
Search2F-COUNT  EF # (2,03 ms)
                ORM = (1,54 ms) ORM 1.3x
Search2F-LISTA  EF ############################################# (86,12 ms)
                ORM ======================================= (73,84 ms) ORM 1.2x
Search8F-COUNT  EF # (2,21 ms)
                ORM = (1,53 ms) ORM 1.4x
SortAsc-5000    EF ################################################ (91,92 ms)
                ORM ============================================== (87,08 ms) ORM 1.1x
SortAscFirst    EF ## (4,70 ms)
                ORM == (3,52 ms) ORM 1.3x
SortDesc-5000   EF ############################################################################ (145,08 ms)
                ORM ============================================================== (117,30 ms) ORM 1.2x
SortFiltFirst   EF ### (5,48 ms)
                ORM === (6,07 ms) EF 1.1x
SortMulti       EF ################################################# (93,94 ms)
                ORM ============================================== (87,91 ms) ORM 1.1x
SortFilt-4500   EF ############################################################ (113,81 ms)
                ORM ========================================== (80,00 ms) ORM 1.4x
GroupBy-1col    EF ############## (26,52 ms)
                ORM = (1,29 ms) ORM 20.6x
GroupBy-2col    EF #################### (37,21 ms)
                ORM ====== (11,82 ms) ORM 3.1x
GroupBy-AGG     EF ####### (12,41 ms)
                ORM == (3,38 ms) ORM 3.7x
GroupBy-ORDER   EF ## (4,26 ms)
                ORM == (2,95 ms) ORM 1.4x
GroupBy-MULTIAG EF ###### (11,75 ms)
                ORM === (5,84 ms) ORM 2.0x
GroupBy-HAVING* EF # (1,15 ms)
                ORM = (1,29 ms) EF 1.1x
BulkInsert-100  EF ################################# (61,99 ms)
                ORM ============= (24,58 ms) ORM 2.5x
QueryRaw-50     EF ### (5,40 ms)
                ORM == (4,69 ms) ORM 1.2x
QueryBuildSort  EF ############################################## (88,02 ms) Empate
                ORM =============================================== (89,90 ms)
Update          EF ####################### (43,40 ms) Empate
                ORM ====================== (42,44 ms)
Delete          EF ################ (30,27 ms)
                ORM =============== (28,36 ms) ORM 1.1x
CountSync       EF # (1,00 ms)
                ORM = (0,63 ms) ORM 1.6x
SearchPag-20    EF ### (6,04 ms)
                ORM === (5,38 ms) ORM 1.1x
Search2F-async  ORM = (1,35 ms) ref. sem par **
PersistComp-10  EF ######### (16,20 ms)
                ORM ======= (12,70 ms) ORM 1.3x
PersistComp-50  EF ########## (19,86 ms)
                ORM ========= (16,22 ms) ORM 1.2x
PersistComp-10a EF ######## (15,65 ms)
                ORM ======= (13,68 ms) ORM 1.1x
PersistComp-50a EF ########## (19,13 ms)
                ORM ======== (15,95 ms) ORM 1.2x
LoadComp-10     EF ## (2,88 ms)
                ORM = (0,38 ms) ORM 7.6x
LoadComp-50     EF ### (5,05 ms)
                ORM = (0,74 ms) ORM 6.9x
LoadComp-10a    EF # (2,20 ms)
                ORM = (0,40 ms) ORM 5.5x
LoadComp-50a    EF ### (5,85 ms)
                ORM = (0,74 ms) ORM 7.9x
QueryComp-21    EF ################ (30,40 ms)
                ORM == (4,28 ms) ORM 7.1x
QueryComp-21a   EF ########## (18,58 ms)
                ORM == (4,02 ms) ORM 4.6x
PaginateComp    EF #### (8,08 ms)
                ORM = (2,17 ms) ORM 3.7x
PaginateComp-a  EF ##### (9,16 ms)
                ORM = (2,36 ms) ORM 3.9x

Placar: ORM 32 × EF 2 × Empate 3 × ref 1.

GroupBy HAVING: a ORM 2.0.4 não emite HAVING (gap, não erro). ** Referência só-ORM (sem par simétrico).

Benchmark — Channel vs Replica (Replicação em Cluster)

PersistenceChannel (CacheIndexer) vs DapperRepository nativo replicaConnStrings, cluster SQLite de 3 nós

Cenário	Channel	Replica	Aceleração
Invoice Única	2 ms (500 inv/s)	1 ms (1000 inv/s)	Replica 2.0x
Bulk 100	41 ms (2439 inv/s)	287 ms (348 inv/s)	Channel 7.0x
Bulk 1.000	783 ms (1277 inv/s)	1.697 ms (589 inv/s)	Channel 2.2x
Delete 10	17 ms	176 ms	Channel 10.4x
Update 5	30 ms (15 linhas)	62 ms (15 linhas)	Channel 2.1x

Mediana de 3 rodadas (a 1ª aquece); Total = burst + drain até o último nó (FlushAsync/barreira); entrega assertiva por nó (1/1/1, 100/100/100, 1000/1000/1000, 0/0/0). Re-Put no bus anexa (sem upsert); update no bus é entidade cheia + filtro.

Testes e cobertura

O projeto Rochas.DapperRepository.Test (xUnit, net9.0) contém 227 testes cobrindo CRUD assíncrono, composição, builders, GROUP BY/agregações, cache plugável, parsers SQL, type handlers e detecção de engine, com 80% de cobertura de linha no assembly principal e 100% em Rochas.Data.Specification.

Español

¿Qué es?

Rochas.DapperRepository es un repositorio genérico ligero para acceso a datos construido sobre Dapper. Sigue los patrones de Repositorio y Unidad de Trabajo descritos por Martin Fowler, sin nuevos lenguajes de consulta, sin seguimiento de estado y sin proxies dinámicos. Está diseñado para trabajar directamente con POCOs decorados con atributos de metadatos que guían las consultas, la persistencia y las relaciones entre entidades.

Es compatible con SQL Server, MySQL, PostgreSQL y SQLite a través del enum DatabaseEngine.

El paquete apunta a .NET Standard 2.1 y se publica en NuGet como Rochas.DapperRepository (versión actual 1.9.5).

Características

CRUD completo (Add, AddRange, Update, Remove, Get) con sobrecargas síncronas y asíncronas
Consultas por filtro tipado (Query), con conjunción Y/O mediante filterConjunction
Búsqueda tokenizada sobre propiedades [Filterable] (Search)
Paginación que devuelve PaginatedResult<T>
Constructor fluido de consultas con OrderBy, OrderByDescending, GroupBy (con agregaciones) y Paginate — el builder es awaitable y genera el SQL en el orden correcto (SELECT → FROM → WHERE → GROUP BY → ORDER BY)
Filtros de rango con [RangeFilter]
Carga automática de composición con [RelatedEntity] (1-1, 1-N, N-1, N-N)
Caché conectable mediante ICacheProvider (en memoria, Redis/Garnet, compuesto L1/L2, canal de persistencia maestro→esclavo)
Soporte DW/ETL con [RelationalColumn] (generación automática de JOIN) y [DataAggregationColumn] (SUM, COUNT, MIN, MAX, AVG)
Consultas SQL directas (QueryRaw)

Instalación

dotnet add package Rochas.DapperRepository

Inicio rápido

using Rochas.DapperRepository;
using Rochas.Data.Specification.Annotations;
using Rochas.Data.Specification.Enums;

var connString = "Data Source=sample.db;Cache=Shared";

using var repo = new GenericRepository<SampleEntity>(DatabaseEngine.SQLite, connString);

var result = await repo.Search("Celulares");

Registro en DI

services.AddScoped<IGenericRepository<SampleEntity>>(provider =>
    new GenericRepository<SampleEntity>(
        DatabaseEngine.SQLite,
        configuration.GetConnectionString("Default")));

Luego inyecte IGenericRepository<SampleEntity> en sus servicios.

Ejemplo de entidad

[Table] y [Column] son opcionales. Cuando se omiten, se usa el nombre de la clase para la tabla y el nombre de la propiedad para la columna. Úselos cuando la base de datos use nombres diferentes (por ejemplo, snake_case):

[Cacheable]
[Table("sample_entities")]
public class SampleEntity
{
    [Key]
    [AutoGenerated]
    public int Id { get; set; }

    [Column("creation_date")]
    public DateTime CreationDate { get; set; }

    [RangeFilter(LinkedRangeProperty = "CreationDateEnd")]
    public DateTime CreatedAt { get; set; }

    [NotMapped]
    public DateTime CreationDateEnd { get; set; }

    [Filterable]
    public string Name { get; set; }

    public int Age { get; set; }

    public bool Active { get; set; }

    [RelatedEntity(Cardinality = RelationCardinality.OneToMany,
                   ForeignKeyAttribute = "ParentId")]
    public IList<ChildEntity> Childs { get; set; }
}

Operaciones CRUD

repo.Add(new SampleEntity { Name = "Renato Rocha" });

repo.AddRange(list);

var filter = new SampleEntity { DocNumber = 12345 };
var entityToUpdate = await repo.Get(filter);
entityToUpdate.Age = 40;
int affected = await repo.Update(entityToUpdate, filter);

int removed = await repo.Remove(filter);

Query (consulta por filtro tipado)

var all = await repo.Query(new SampleEntity()); // listar todos

var filter = new SampleEntity { Name = "Roberto", Email = "gmail.com" };
var results = await repo.Query(filter, filterConjunction: true);

Use el parámetro filterConjunction para definir el comportamiento de los atributos en la consulta: conjunción lógica Y cuando está activada, disyunción lógica O cuando está desactivada. Con conjunción el criterio aplicado es de igualdad, de lo contrario de similitud.

Search (búsqueda tokenizada en propiedades `[Filterable]`)

var results = await repo.Search("Paulo");

var page = await repo.Search("Paulo", page: 1, pageSize: 20);

Paginación

var result = await repo.Query(filter, page: 1, pageSize: 10);

var result = repo.QuerySync(filter, page: 1, pageSize: 10);

PaginatedResult<T> expone Items, TotalCount, Page, PageSize y PageCount.

Constructor fluido de consultas

var result = await repo.Query(filter)
    .OrderBy("Name")
    .Paginate(1, 10);

var result = await repo.Query(filter)
    .GroupBy("Category")
    .OrderByDescending("Price");

var list = repo.QuerySync(filter)
    .OrderBy("Name")
    .ToList();

El orden de los métodos no importa; el builder recopila los atributos de ordenación y agrupación y ejecuta la consulta en el orden correcto del SQL.

GroupBy con agregaciones

var aggregates = new Dictionary<string, DataAggregationType>
{
    { "Price", DataAggregationType.Sum },
    { "Id", DataAggregationType.Count }
};
var result = await repo.Query(filter).GroupBy(["Category"], aggregates);

El builder genera el SELECT ... GROUP BY ... automáticamente con las funciones de agregación solicitadas (SUM, COUNT, MIN, MAX, AVG).

Con agregaciones, el SELECT generado solo contiene las claves de grupo y las expresiones agregadas — exigido por PostgreSQL y SQL Server, que rechazan columnas sueltas fuera del agrupamiento.

Entidades de hecho DW/ETL

Para trabajos de Data Warehouse y ETL, [RelationalColumn] genera JOINs automáticos (INNER/LEFT) con tablas dimensionales y [DataAggregationColumn] genera columnas calculadas de agregación:

[Table("fact_sales")]
public class FactSalesEntity
{
    [Key] [AutoGenerated]
    public int Id { get; set; }

    [Column("product_id")]
    public int ProductId { get; set; }

    [Column("total_amount")]
    public decimal TotalAmount { get; set; }

    // JOIN automático: dim_product.product_name AS ProductName
    [RelationalColumn(
        TableName = "dim_product",
        ColumnName = "product_name",
        ColumnAlias = "ProductName",
        KeyColumn = "product_id",
        ForeignKeyColumn = "id",
        JunctionType = RelationalJunctionType.Mandatory)]
    public string ProductName { get; set; }

    // Agregación: SUM(fact_sales.total_amount) AS SumTotalAmount
    [DataAggregationColumn(ColumnName = "total_amount", AggregationType = DataAggregationType.Sum)]
    public decimal SumTotalAmount { get; set; }
}

var result = await repo.Query(new FactSalesEntity());

SQL generado:

SELECT dim_product.product_name AS ProductName,
       SUM(fact_sales.total_amount) AS SumTotalAmount,
       fact_sales.id, fact_sales.product_id, fact_sales.total_amount
FROM fact_sales
INNER JOIN dim_product ON fact_sales.product_id = dim_product.id
WHERE 1 = 1

RelationalJunctionType.Mandatory genera INNER JOIN; Optional genera LEFT JOIN. Las propiedades marcadas con [RelationalColumn] o [DataAggregationColumn] son de solo lectura y se ignoran en las operaciones INSERT/UPDATE.

Caché conectable

Marque la entidad como [Cacheable] para habilitar el caché. El proveedor predeterminado es en memoria, pero se pueden inyectar proveedores Redis, Garnet o compuestos sin cambiar el código del repositorio:

DataCache.Initialize(memorySizeLimit: 100); // en memoria, límite de 100 MB
DataCache.Initialize();                     // en memoria, sin límite

DataCache.Initialize(new DistributedCacheProvider("localhost:6379")); // Redis / Garnet

DataCache.Initialize(new CompositeCacheProvider( // L1 en memoria + L2 distribuido
    new InMemoryCacheProvider(),
    new DistributedCacheProvider("redis:6379")));

Benchmark — DapperRepository vs EF Core

Linux, .NET 9.0.20, SQLite, 5.000 filas (+ composición: 21 padres), ORM 2.0.4 + Wrapper 1.5.0 — 80 col ≈ 152 ms

Insert          EF ####### (13.26 ms) Empate
                ORM ======= (13.44 ms)
GetById         EF # (1.00 ms)
                ORM = (0.40 ms) ORM 2.5x
QueryFiltered   EF # (0.87 ms)
                ORM = (0.27 ms) ORM 3.2x
Search2F-COUNT  EF # (2.03 ms)
                ORM = (1.54 ms) ORM 1.3x
Search2F-LISTA  EF ############################################# (86.12 ms)
                ORM ======================================= (73.84 ms) ORM 1.2x
Search8F-COUNT  EF # (2.21 ms)
                ORM = (1.53 ms) ORM 1.4x
SortAsc-5000    EF ################################################ (91.92 ms)
                ORM ============================================== (87.08 ms) ORM 1.1x
SortAscFirst    EF ## (4.70 ms)
                ORM == (3.52 ms) ORM 1.3x
SortDesc-5000   EF ############################################################################ (145.08 ms)
                ORM ============================================================== (117.30 ms) ORM 1.2x
SortFiltFirst   EF ### (5.48 ms)
                ORM === (6.07 ms) EF 1.1x
SortMulti       EF ################################################# (93.94 ms)
                ORM ============================================== (87.91 ms) ORM 1.1x
SortFilt-4500   EF ############################################################ (113.81 ms)
                ORM ========================================== (80.00 ms) ORM 1.4x
GroupBy-1col    EF ############## (26.52 ms)
                ORM = (1.29 ms) ORM 20.6x
GroupBy-2col    EF #################### (37.21 ms)
                ORM ====== (11.82 ms) ORM 3.1x
GroupBy-AGG     EF ####### (12.41 ms)
                ORM == (3.38 ms) ORM 3.7x
GroupBy-ORDER   EF ## (4.26 ms)
                ORM == (2.95 ms) ORM 1.4x
GroupBy-MULTIAG EF ###### (11.75 ms)
                ORM === (5.84 ms) ORM 2.0x
GroupBy-HAVING* EF # (1.15 ms)
                ORM = (1.29 ms) EF 1.1x
BulkInsert-100  EF ################################# (61.99 ms)
                ORM ============= (24.58 ms) ORM 2.5x
QueryRaw-50     EF ### (5.40 ms)
                ORM == (4.69 ms) ORM 1.2x
QueryBuildSort  EF ############################################## (88.02 ms) Empate
                ORM =============================================== (89.90 ms)
Update          EF ####################### (43.40 ms) Empate
                ORM ====================== (42.44 ms)
Delete          EF ################ (30.27 ms)
                ORM =============== (28.36 ms) ORM 1.1x
CountSync       EF # (1.00 ms)
                ORM = (0.63 ms) ORM 1.6x
SearchPag-20    EF ### (6.04 ms)
                ORM === (5.38 ms) ORM 1.1x
Search2F-async  ORM = (1.35 ms) ref. sin par **
PersistComp-10  EF ######### (16.20 ms)
                ORM ======= (12.70 ms) ORM 1.3x
PersistComp-50  EF ########## (19.86 ms)
                ORM ========= (16.22 ms) ORM 1.2x
PersistComp-10a EF ######## (15.65 ms)
                ORM ======= (13.68 ms) ORM 1.1x
PersistComp-50a EF ########## (19.13 ms)
                ORM ======== (15.95 ms) ORM 1.2x
LoadComp-10     EF ## (2.88 ms)
                ORM = (0.38 ms) ORM 7.6x
LoadComp-50     EF ### (5.05 ms)
                ORM = (0.74 ms) ORM 6.9x
LoadComp-10a    EF # (2.20 ms)
                ORM = (0.40 ms) ORM 5.5x
LoadComp-50a    EF ### (5.85 ms)
                ORM = (0.74 ms) ORM 7.9x
QueryComp-21    EF ################ (30.40 ms)
                ORM == (4.28 ms) ORM 7.1x
QueryComp-21a   EF ########## (18.58 ms)
                ORM == (4.02 ms) ORM 4.6x
PaginateComp    EF #### (8.08 ms)
                ORM = (2.17 ms) ORM 3.7x
PaginateComp-a  EF ##### (9.16 ms)
                ORM = (2.36 ms) ORM 3.9x

Marcador: ORM 32 × EF 2 × Empate 3 × ref 1.

GroupBy HAVING: el ORM no emite HAVING (brecha, no error). ** Referencia solo-ORM (sin par simétrico).

Benchmark — Channel vs Replica (Replicación en Cluster)

PersistenceChannel (CacheIndexer) vs DapperRepository nativo replicaConnStrings, cluster SQLite de 3 nodos

Escenario	Channel	Replica	Aceleración
Factura Individual	2 ms (500 inv/s)	1 ms (1000 inv/s)	Replica 2.0x
Bulk 100	41 ms (2439 inv/s)	287 ms (348 inv/s)	Channel 7.0x
Bulk 1.000	783 ms (1277 inv/s)	1.697 ms (589 inv/s)	Channel 2.2x
Delete 10	17 ms	176 ms	Channel 10.4x
Update 5	30 ms (15 filas)	62 ms (15 filas)	Channel 2.1x

Mediana de 3 rondas (la 1ª calienta); Total = burst + drain hasta el último nodo (FlushAsync/barrera); entrega verificada por nodo (1/1/1, 100/100/100, 1000/1000/1000, 0/0/0). Re-Put en el bus anexa (sin upsert); update en el bus es entidad completa + filtro.

Pruebas y cobertura

El proyecto Rochas.DapperRepository.Test (xUnit, net9.0) contiene 227 pruebas que cubren CRUD asíncrono, composición, builders, GROUP BY/agregaciones, caché conectable, parsers SQL, type handlers y detección de engine, con 80% de cobertura de línea en el ensamblado principal y 100% en Rochas.Data.Specification.

Français

Qu'est-ce que c'est ?

Rochas.DapperRepository est un dépôt générique léger pour l'accès aux données, construit sur Dapper. Il suit les modèles Repository et Unit of Work décrits par Martin Fowler, sans nouveau langage de requête, sans suivi d'état et sans proxys dynamiques. Il est conçu pour fonctionner directement avec des POCO décorés d'attributs de métadonnées qui pilotent les requêtes, la persistance et les relations entre entités.

Il prend en charge SQL Server, MySQL, PostgreSQL et SQLite via l'enum DatabaseEngine.

Le paquet cible .NET Standard 2.1 et est publié sur NuGet sous le nom Rochas.DapperRepository (version actuelle 1.9.5).

Fonctionnalités

CRUD complet (Add, AddRange, Update, Remove, Get) avec surcharges synchrones et asynchrones
Requêtes par filtre typé (Query), avec conjonction ET/OU via filterConjunction
Recherche tokenisée sur les propriétés [Filterable] (Search)
Pagination renvoyant PaginatedResult<T>
Constructeur de requêtes fluide avec OrderBy, OrderByDescending, GroupBy (avec agrégats) et Paginate — le builder est awaitable et génère le SQL dans le bon ordre (SELECT → FROM → WHERE → GROUP BY → ORDER BY)
Filtres de plage avec [RangeFilter]
Chargement automatique de la composition avec [RelatedEntity] (1-1, 1-N, N-1, N-N)
Cache enfichable via ICacheProvider (en mémoire, Redis/Garnet, composite L1/L2, canal de persistance maître→esclave)
Support DW/ETL avec [RelationalColumn] (génération automatique de JOIN) et [DataAggregationColumn] (SUM, COUNT, MIN, MAX, AVG)
Requêtes SQL directes (QueryRaw)

Installation

dotnet add package Rochas.DapperRepository

Démarrage rapide

using Rochas.DapperRepository;
using Rochas.Data.Specification.Annotations;
using Rochas.Data.Specification.Enums;

var connString = "Data Source=sample.db;Cache=Shared";

using var repo = new GenericRepository<SampleEntity>(DatabaseEngine.SQLite, connString);

var result = await repo.Search("Celulares");

Enregistrement dans le DI

services.AddScoped<IGenericRepository<SampleEntity>>(provider =>
    new GenericRepository<SampleEntity>(
        DatabaseEngine.SQLite,
        configuration.GetConnectionString("Default")));

Injectez ensuite IGenericRepository<SampleEntity> dans vos services.

Exemple d'entité

[Table] et [Column] sont facultatifs. Lorsqu'ils sont omis, le nom de la classe est utilisé pour la table et le nom de la propriété pour la colonne. Utilisez-les lorsque votre base de données utilise des noms différents (par exemple, snake_case) :

[Cacheable]
[Table("sample_entities")]
public class SampleEntity
{
    [Key]
    [AutoGenerated]
    public int Id { get; set; }

    [Column("creation_date")]
    public DateTime CreationDate { get; set; }

    [RangeFilter(LinkedRangeProperty = "CreationDateEnd")]
    public DateTime CreatedAt { get; set; }

    [NotMapped]
    public DateTime CreationDateEnd { get; set; }

    [Filterable]
    public string Name { get; set; }

    public int Age { get; set; }

    public bool Active { get; set; }

    [RelatedEntity(Cardinality = RelationCardinality.OneToMany,
                   ForeignKeyAttribute = "ParentId")]
    public IList<ChildEntity> Childs { get; set; }
}

Opérations CRUD

repo.Add(new SampleEntity { Name = "Renato Rocha" });

repo.AddRange(list);

var filter = new SampleEntity { DocNumber = 12345 };
var entityToUpdate = await repo.Get(filter);
entityToUpdate.Age = 40;
int affected = await repo.Update(entityToUpdate, filter);

int removed = await repo.Remove(filter);

Query (requête par filtre typé)

var all = await repo.Query(new SampleEntity()); // tous les enregistrements

var filter = new SampleEntity { Name = "Roberto", Email = "gmail.com" };
var results = await repo.Query(filter, filterConjunction: true);

Utilisez le paramètre filterConjunction pour définir le comportement des attributs dans la requête : conjonction logique ET lorsqu'il est activé, disjonction logique OU lorsqu'il est désactivé. Avec la conjonction, le critère appliqué est l'égalité, sinon la similarité.

Search (recherche tokenisée sur les propriétés `[Filterable]`)

var results = await repo.Search("Paulo");

var page = await repo.Search("Paulo", page: 1, pageSize: 20);

Pagination

var result = await repo.Query(filter, page: 1, pageSize: 10);

var result = repo.QuerySync(filter, page: 1, pageSize: 10);

PaginatedResult<T> expose Items, TotalCount, Page, PageSize et PageCount.

Constructeur de requêtes fluide

var result = await repo.Query(filter)
    .OrderBy("Name")
    .Paginate(1, 10);

var result = await repo.Query(filter)
    .GroupBy("Category")
    .OrderByDescending("Price");

var list = repo.QuerySync(filter)
    .OrderBy("Name")
    .ToList();

L'ordre des méthodes n'a pas d'importance ; le builder collecte les attributs de tri et de regroupement et exécute la requête dans le bon ordre SQL.

GroupBy avec agrégats

var aggregates = new Dictionary<string, DataAggregationType>
{
    { "Price", DataAggregationType.Sum },
    { "Id", DataAggregationType.Count }
};
var result = await repo.Query(filter).GroupBy(["Category"], aggregates);

Le builder génère automatiquement le SELECT ... GROUP BY ... avec les fonctions d'agrégation demandées (SUM, COUNT, MIN, MAX, AVG).

Avec des agrégations, le SELECT généré ne contient que les clés de groupe et les expressions agrégées — exigé par PostgreSQL et SQL Server, qui rejettent les colonnes isolées hors regroupement.

Entités de fait DW/ETL

Pour les travaux de Data Warehouse et d'ETL, [RelationalColumn] génère des JOIN automatiques (INNER/LEFT) avec les tables de dimensions et [DataAggregationColumn] génère des colonnes d'agrégation calculées :

[Table("fact_sales")]
public class FactSalesEntity
{
    [Key] [AutoGenerated]
    public int Id { get; set; }

    [Column("product_id")]
    public int ProductId { get; set; }

    [Column("total_amount")]
    public decimal TotalAmount { get; set; }

    // JOIN automatique : dim_product.product_name AS ProductName
    [RelationalColumn(
        TableName = "dim_product",
        ColumnName = "product_name",
        ColumnAlias = "ProductName",
        KeyColumn = "product_id",
        ForeignKeyColumn = "id",
        JunctionType = RelationalJunctionType.Mandatory)]
    public string ProductName { get; set; }

    // Agrégation : SUM(fact_sales.total_amount) AS SumTotalAmount
    [DataAggregationColumn(ColumnName = "total_amount", AggregationType = DataAggregationType.Sum)]
    public decimal SumTotalAmount { get; set; }
}

var result = await repo.Query(new FactSalesEntity());

SQL généré :

SELECT dim_product.product_name AS ProductName,
       SUM(fact_sales.total_amount) AS SumTotalAmount,
       fact_sales.id, fact_sales.product_id, fact_sales.total_amount
FROM fact_sales
INNER JOIN dim_product ON fact_sales.product_id = dim_product.id
WHERE 1 = 1

RelationalJunctionType.Mandatory génère INNER JOIN ; Optional génère LEFT JOIN. Les propriétés marquées avec [RelationalColumn] ou [DataAggregationColumn] sont en lecture seule et sont ignorées dans les opérations INSERT/UPDATE.

Cache enfichable

Marquez l'entité avec [Cacheable] pour activer le cache. Le fournisseur par défaut est en mémoire, mais des fournisseurs Redis, Garnet ou composites peuvent être injectés sans modifier le code du dépôt :

DataCache.Initialize(memorySizeLimit: 100); // en mémoire, limite de 100 Mo
DataCache.Initialize();                     // en mémoire, sans limite

DataCache.Initialize(new DistributedCacheProvider("localhost:6379")); // Redis / Garnet

DataCache.Initialize(new CompositeCacheProvider( // L1 en mémoire + L2 distribué
    new InMemoryCacheProvider(),
    new DistributedCacheProvider("redis:6379")));

Benchmark — DapperRepository vs EF Core

Linux, .NET 9.0.20, SQLite, 5 000 lignes (+ composition : 21 parents), ORM 2.0.4 + Wrapper 1.5.0 — 80 col ≈ 152 ms

Insert          EF ####### (13,26 ms) Égalité
                ORM ======= (13,44 ms)
GetById         EF # (1,00 ms)
                ORM = (0,40 ms) ORM 2.5x
QueryFiltered   EF # (0,87 ms)
                ORM = (0,27 ms) ORM 3.2x
Search2F-COUNT  EF # (2,03 ms)
                ORM = (1,54 ms) ORM 1.3x
Search2F-LISTA  EF ############################################# (86,12 ms)
                ORM ======================================= (73,84 ms) ORM 1.2x
Search8F-COUNT  EF # (2,21 ms)
                ORM = (1,53 ms) ORM 1.4x
SortAsc-5000    EF ################################################ (91,92 ms)
                ORM ============================================== (87,08 ms) ORM 1.1x
SortAscFirst    EF ## (4,70 ms)
                ORM == (3,52 ms) ORM 1.3x
SortDesc-5000   EF ############################################################################ (145,08 ms)
                ORM ============================================================== (117,30 ms) ORM 1.2x
SortFiltFirst   EF ### (5,48 ms)
                ORM === (6,07 ms) EF 1.1x
SortMulti       EF ################################################# (93,94 ms)
                ORM ============================================== (87,91 ms) ORM 1.1x
SortFilt-4500   EF ############################################################ (113,81 ms)
                ORM ========================================== (80,00 ms) ORM 1.4x
GroupBy-1col    EF ############## (26,52 ms)
                ORM = (1,29 ms) ORM 20.6x
GroupBy-2col    EF #################### (37,21 ms)
                ORM ====== (11,82 ms) ORM 3.1x
GroupBy-AGG     EF ####### (12,41 ms)
                ORM == (3,38 ms) ORM 3.7x
GroupBy-ORDER   EF ## (4,26 ms)
                ORM == (2,95 ms) ORM 1.4x
GroupBy-MULTIAG EF ###### (11,75 ms)
                ORM === (5,84 ms) ORM 2.0x
GroupBy-HAVING* EF # (1,15 ms)
                ORM = (1,29 ms) EF 1.1x
BulkInsert-100  EF ################################# (61,99 ms)
                ORM ============= (24,58 ms) ORM 2.5x
QueryRaw-50     EF ### (5,40 ms)
                ORM == (4,69 ms) ORM 1.2x
QueryBuildSort  EF ############################################## (88,02 ms) Égalité
                ORM =============================================== (89,90 ms)
Update          EF ####################### (43,40 ms) Égalité
                ORM ====================== (42,44 ms)
Delete          EF ################ (30,27 ms)
                ORM =============== (28,36 ms) ORM 1.1x
CountSync       EF # (1,00 ms)
                ORM = (0,63 ms) ORM 1.6x
SearchPag-20    EF ### (6,04 ms)
                ORM === (5,38 ms) ORM 1.1x
Search2F-async  ORM = (1,35 ms) réf. sans paire **
PersistComp-10  EF ######### (16,20 ms)
                ORM ======= (12,70 ms) ORM 1.3x
PersistComp-50  EF ########## (19,86 ms)
                ORM ========= (16,22 ms) ORM 1.2x
PersistComp-10a EF ######## (15,65 ms)
                ORM ======= (13,68 ms) ORM 1.1x
PersistComp-50a EF ########## (19,13 ms)
                ORM ======== (15,95 ms) ORM 1.2x
LoadComp-10     EF ## (2,88 ms)
                ORM = (0,38 ms) ORM 7.6x
LoadComp-50     EF ### (5,05 ms)
                ORM = (0,74 ms) ORM 6.9x
LoadComp-10a    EF # (2,20 ms)
                ORM = (0,40 ms) ORM 5.5x
LoadComp-50a    EF ### (5,85 ms)
                ORM = (0,74 ms) ORM 7.9x
QueryComp-21    EF ################ (30,40 ms)
                ORM == (4,28 ms) ORM 7.1x
QueryComp-21a   EF ########## (18,58 ms)
                ORM == (4,02 ms) ORM 4.6x
PaginateComp    EF #### (8,08 ms)
                ORM = (2,17 ms) ORM 3.7x
PaginateComp-a  EF ##### (9,16 ms)
                ORM = (2,36 ms) ORM 3.9x

Score : ORM 32 × EF 2 × Égalité 3 × réf 1.

GroupBy HAVING : l'ORM n'émet pas de HAVING (écart, pas une erreur). ** Référence ORM uniquement (sans paire symétrique).

Benchmark — Channel vs Replica (Réplication en Cluster)

PersistenceChannel (CacheIndexer) vs DapperRepository natif replicaConnStrings, cluster SQLite à 3 nœuds

Scénario	Channel	Replica	Accélération
Facture Individuelle	2 ms (500 inv/s)	1 ms (1000 inv/s)	Replica 2.0x
Bulk 100	41 ms (2439 inv/s)	287 ms (348 inv/s)	Channel 7.0x
Bulk 1 000	783 ms (1277 inv/s)	1 697 ms (589 inv/s)	Channel 2.2x
Delete 10	17 ms	176 ms	Channel 10.4x
Update 5	30 ms (15 lignes)	62 ms (15 lignes)	Channel 2.1x

Médiane de 3 rondes (la 1re chauffe) ; Total = burst + drain jusqu'au dernier nœud (FlushAsync/barrière) ; livraison vérifiée par nœud (1/1/1, 100/100/100, 1000/1000/1000, 0/0/0). Re-Put sur le bus ajoute (sans upsert) ; update sur le bus = entité complète + filtre.

Tests et couverture

Le projet Rochas.DapperRepository.Test (xUnit, net9.0) contient 227 tests couvrant le CRUD asynchrone, la composition, les builders, le GROUP BY/les agrégats, le cache enfichable, les analyseurs SQL, les type handlers et la détection de moteur, avec 80 % de couverture de lignes sur l'assemblage principal et 100 % dans Rochas.Data.Specification.

Deutsch

Was ist das?

Rochas.DapperRepository ist ein leichtgewichtiges generisches Repository für den Datenzugriff, aufgebaut auf Dapper. Es folgt den von Martin Fowler beschriebenen Repository- und Unit-of-Work-Mustern, ohne neue Abfragesprache, ohne Zustandsverfolgung und ohne dynamische Proxies. Es wurde für die direkte Verwendung mit POCOs entworfen, die mit Metadaten-Attributen dekoriert sind, die Abfragen, Persistenz und Entitätsbeziehungen steuern.

Unterstützt werden SQL Server, MySQL, PostgreSQL und SQLite über die Enum DatabaseEngine.

Das Paket zielt auf .NET Standard 2.1 und wird auf NuGet als Rochas.DapperRepository veröffentlicht (aktuelle Version 1.9.5).

Funktionen

Vollständiges CRUD (Add, AddRange, Update, Remove, Get) mit synchronen und asynchronen Überladungen
Typisierte Filterabfragen (Query) mit UND/ODER-Verknüpfung über filterConjunction
Tokenisierte Suche über [Filterable]-Eigenschaften (Search)
Seitierung mit Rückgabe von PaginatedResult<T>
Fließender Query-Builder mit OrderBy, OrderByDescending, GroupBy (mit Aggregaten) und Paginate — der Builder ist awaitable und generiert SQL in der richtigen Reihenfolge (SELECT → FROM → WHERE → GROUP BY → ORDER BY)
Bereichsfilter mit [RangeFilter]
Automatisches Laden von Kompositionen mit [RelatedEntity] (1-1, 1-N, N-1, N-N)
Austauschbares Caching über ICacheProvider (in-memory, Redis/Garnet, Composite L1/L2, Master-to-Slave-Persistenzkanal)
DW/ETL-Unterstützung mit [RelationalColumn] (automatische JOIN-Erzeugung) und [DataAggregationColumn] (SUM, COUNT, MIN, MAX, AVG)
Direkte SQL-Abfragen (QueryRaw)

Installation

dotnet add package Rochas.DapperRepository

Schnellstart

using Rochas.DapperRepository;
using Rochas.Data.Specification.Annotations;
using Rochas.Data.Specification.Enums;

var connString = "Data Source=sample.db;Cache=Shared";

using var repo = new GenericRepository<SampleEntity>(DatabaseEngine.SQLite, connString);

var result = await repo.Search("Celulares");

Registrierung in DI

services.AddScoped<IGenericRepository<SampleEntity>>(provider =>
    new GenericRepository<SampleEntity>(
        DatabaseEngine.SQLite,
        configuration.GetConnectionString("Default")));

Anschließend injizieren Sie IGenericRepository<SampleEntity> in Ihre Dienste.

Entitätsbeispiel

[Table] und [Column] sind optional. Wenn sie weggelassen werden, wird der Klassenname für die Tabelle und der Eigenschaftsname für die Spalte verwendet. Verwenden Sie sie, wenn Ihre Datenbank andere Namen verwendet (z. B. snake_case):

[Cacheable]
[Table("sample_entities")]
public class SampleEntity
{
    [Key]
    [AutoGenerated]
    public int Id { get; set; }

    [Column("creation_date")]
    public DateTime CreationDate { get; set; }

    [RangeFilter(LinkedRangeProperty = "CreationDateEnd")]
    public DateTime CreatedAt { get; set; }

    [NotMapped]
    public DateTime CreationDateEnd { get; set; }

    [Filterable]
    public string Name { get; set; }

    public int Age { get; set; }

    public bool Active { get; set; }

    [RelatedEntity(Cardinality = RelationCardinality.OneToMany,
                   ForeignKeyAttribute = "ParentId")]
    public IList<ChildEntity> Childs { get; set; }
}

CRUD-Operationen

repo.Add(new SampleEntity { Name = "Renato Rocha" });

repo.AddRange(list);

var filter = new SampleEntity { DocNumber = 12345 };
var entityToUpdate = await repo.Get(filter);
entityToUpdate.Age = 40;
int affected = await repo.Update(entityToUpdate, filter);

int removed = await repo.Remove(filter);

Query (Abfrage per typisiertem Filter)

var all = await repo.Query(new SampleEntity()); // alle Datensätze

var filter = new SampleEntity { Name = "Roberto", Email = "gmail.com" };
var results = await repo.Query(filter, filterConjunction: true);

Verwenden Sie den Parameter filterConjunction, um das Verhalten der Attribute in der Abfrage festzulegen: logische UND-Verknüpfung, wenn aktiviert, logische ODER-Verknüpfung, wenn deaktiviert. Mit Verknüpfung wird das Kriterium Gleichheit angewendet, andernfalls Ähnlichkeit.

Search (tokenisierte Suche über `[Filterable]`-Eigenschaften)

var results = await repo.Search("Paulo");

var page = await repo.Search("Paulo", page: 1, pageSize: 20);

Seitierung

var result = await repo.Query(filter, page: 1, pageSize: 10);

var result = repo.QuerySync(filter, page: 1, pageSize: 10);

PaginatedResult<T> stellt Items, TotalCount, Page, PageSize und PageCount bereit.

Fließender Query-Builder

var result = await repo.Query(filter)
    .OrderBy("Name")
    .Paginate(1, 10);

var result = await repo.Query(filter)
    .GroupBy("Category")
    .OrderByDescending("Price");

var list = repo.QuerySync(filter)
    .OrderBy("Name")
    .ToList();

Die Reihenfolge der Methoden spielt keine Rolle; der Builder sammelt die Sortier- und Gruppierungsattribute und führt die Abfrage in der korrekten SQL-Reihenfolge aus.

GroupBy mit Aggregaten

var aggregates = new Dictionary<string, DataAggregationType>
{
    { "Price", DataAggregationType.Sum },
    { "Id", DataAggregationType.Count }
};
var result = await repo.Query(filter).GroupBy(["Category"], aggregates);

Der Builder generiert automatisch das SELECT ... GROUP BY ... mit den angeforderten Aggregatfunktionen (SUM, COUNT, MIN, MAX, AVG).

Mit Aggregationen enthält das generierte SELECT nur die Gruppierungsschlüssel und die Aggregatausdrücke — von PostgreSQL und SQL Server gefordert, die lose Spalten außerhalb der Gruppierung ablehnen.

DW/ETL-Faktenentitäten

Für Data-Warehouse- und ETL-Arbeiten erzeugt [RelationalColumn] automatische JOINs (INNER/LEFT) mit Dimensionstabellen und [DataAggregationColumn] erzeugt berechnete Aggregatspalten:

[Table("fact_sales")]
public class FactSalesEntity
{
    [Key] [AutoGenerated]
    public int Id { get; set; }

    [Column("product_id")]
    public int ProductId { get; set; }

    [Column("total_amount")]
    public decimal TotalAmount { get; set; }

    // Automatischer JOIN: dim_product.product_name AS ProductName
    [RelationalColumn(
        TableName = "dim_product",
        ColumnName = "product_name",
        ColumnAlias = "ProductName",
        KeyColumn = "product_id",
        ForeignKeyColumn = "id",
        JunctionType = RelationalJunctionType.Mandatory)]
    public string ProductName { get; set; }

    // Aggregat: SUM(fact_sales.total_amount) AS SumTotalAmount
    [DataAggregationColumn(ColumnName = "total_amount", AggregationType = DataAggregationType.Sum)]
    public decimal SumTotalAmount { get; set; }
}

var result = await repo.Query(new FactSalesEntity());

Generiertes SQL:

SELECT dim_product.product_name AS ProductName,
       SUM(fact_sales.total_amount) AS SumTotalAmount,
       fact_sales.id, fact_sales.product_id, fact_sales.total_amount
FROM fact_sales
INNER JOIN dim_product ON fact_sales.product_id = dim_product.id
WHERE 1 = 1

RelationalJunctionType.Mandatory erzeugt INNER JOIN; Optional erzeugt LEFT JOIN. Eigenschaften, die mit [RelationalColumn] oder [DataAggregationColumn] markiert sind, sind schreibgeschützt und werden bei INSERT/UPDATE-Operationen ignoriert.

Austauschbares Caching

Markieren Sie die Entität mit [Cacheable], um das Caching zu aktivieren. Der Standardanbieter ist in-memory, aber Redis-, Garnet- oder Composite-Anbieter können injiziert werden, ohne den Repository-Code zu ändern:

DataCache.Initialize(memorySizeLimit: 100); // in-memory, Limit 100 MB
DataCache.Initialize();                     // in-memory, ohne Limit

DataCache.Initialize(new DistributedCacheProvider("localhost:6379")); // Redis / Garnet

DataCache.Initialize(new CompositeCacheProvider( // L1 in-memory + L2 verteilt
    new InMemoryCacheProvider(),
    new DistributedCacheProvider("redis:6379")));

Benchmark — DapperRepository vs EF Core

Linux, .NET 9.0.20, SQLite, 5.000 Zeilen (+ Komposition: 21 Parents), ORM 2.0.4 + Wrapper 1.5.0 — 80 Spalten ≈ 152 ms

Insert          EF ####### (13,26 ms) Unentschieden
                ORM ======= (13,44 ms)
GetById         EF # (1,00 ms)
                ORM = (0,40 ms) ORM 2.5x
QueryFiltered   EF # (0,87 ms)
                ORM = (0,27 ms) ORM 3.2x
Search2F-COUNT  EF # (2,03 ms)
                ORM = (1,54 ms) ORM 1.3x
Search2F-LISTA  EF ############################################# (86,12 ms)
                ORM ======================================= (73,84 ms) ORM 1.2x
Search8F-COUNT  EF # (2,21 ms)
                ORM = (1,53 ms) ORM 1.4x
SortAsc-5000    EF ################################################ (91,92 ms)
                ORM ============================================== (87,08 ms) ORM 1.1x
SortAscFirst    EF ## (4,70 ms)
                ORM == (3,52 ms) ORM 1.3x
SortDesc-5000   EF ############################################################################ (145,08 ms)
                ORM ============================================================== (117,30 ms) ORM 1.2x
SortFiltFirst   EF ### (5,48 ms)
                ORM === (6,07 ms) EF 1.1x
SortMulti       EF ################################################# (93,94 ms)
                ORM ============================================== (87,91 ms) ORM 1.1x
SortFilt-4500   EF ############################################################ (113,81 ms)
                ORM ========================================== (80,00 ms) ORM 1.4x
GroupBy-1col    EF ############## (26,52 ms)
                ORM = (1,29 ms) ORM 20.6x
GroupBy-2col    EF #################### (37,21 ms)
                ORM ====== (11,82 ms) ORM 3.1x
GroupBy-AGG     EF ####### (12,41 ms)
                ORM == (3,38 ms) ORM 3.7x
GroupBy-ORDER   EF ## (4,26 ms)
                ORM == (2,95 ms) ORM 1.4x
GroupBy-MULTIAG EF ###### (11,75 ms)
                ORM === (5,84 ms) ORM 2.0x
GroupBy-HAVING* EF # (1,15 ms)
                ORM = (1,29 ms) EF 1.1x
BulkInsert-100  EF ################################# (61,99 ms)
                ORM ============= (24,58 ms) ORM 2.5x
QueryRaw-50     EF ### (5,40 ms)
                ORM == (4,69 ms) ORM 1.2x
QueryBuildSort  EF ############################################## (88,02 ms) Unentschieden
                ORM =============================================== (89,90 ms)
Update          EF ####################### (43,40 ms) Unentschieden
                ORM ====================== (42,44 ms)
Delete          EF ################ (30,27 ms)
                ORM =============== (28,36 ms) ORM 1.1x
CountSync       EF # (1,00 ms)
                ORM = (0,63 ms) ORM 1.6x
SearchPag-20    EF ### (6,04 ms)
                ORM === (5,38 ms) ORM 1.1x
Search2F-async  ORM = (1,35 ms) Ref. ohne Paar **
PersistComp-10  EF ######### (16,20 ms)
                ORM ======= (12,70 ms) ORM 1.3x
PersistComp-50  EF ########## (19,86 ms)
                ORM ========= (16,22 ms) ORM 1.2x
PersistComp-10a EF ######## (15,65 ms)
                ORM ======= (13,68 ms) ORM 1.1x
PersistComp-50a EF ########## (19,13 ms)
                ORM ======== (15,95 ms) ORM 1.2x
LoadComp-10     EF ## (2,88 ms)
                ORM = (0,38 ms) ORM 7.6x
LoadComp-50     EF ### (5,05 ms)
                ORM = (0,74 ms) ORM 6.9x
LoadComp-10a    EF # (2,20 ms)
                ORM = (0,40 ms) ORM 5.5x
LoadComp-50a    EF ### (5,85 ms)
                ORM = (0,74 ms) ORM 7.9x
QueryComp-21    EF ################ (30,40 ms)
                ORM == (4,28 ms) ORM 7.1x
QueryComp-21a   EF ########## (18,58 ms)
                ORM == (4,02 ms) ORM 4.6x
PaginateComp    EF #### (8,08 ms)
                ORM = (2,17 ms) ORM 3.7x
PaginateComp-a  EF ##### (9,16 ms)
                ORM = (2,36 ms) ORM 3.9x

Stand: ORM 32 × EF 2 × Unentschieden 3 × Ref 1.

GroupBy HAVING: Das ORM gibt kein HAVING aus (Lücke, kein Fehler). ** Nur-ORM-Referenz (ohne symmetrisches Paar).

Benchmark — Channel vs Replica (Cluster-Replikation)

PersistenceChannel (CacheIndexer) vs DapperRepository nativ replicaConnStrings, 3-Knoten-SQLite-Cluster

Szenario	Channel	Replica	Beschleunigung
Einzelrechnung	2 ms (500 Inv/s)	1 ms (1000 Inv/s)	Replica 2.0x
Bulk 100	41 ms (2439 Inv/s)	287 ms (348 Inv/s)	Channel 7.0x
Bulk 1.000	783 ms (1277 Inv/s)	1.697 ms (589 Inv/s)	Channel 2.2x
Delete 10	17 ms	176 ms	Channel 10.4x
Update 5	30 ms (15 Zeilen)	62 ms (15 Zeilen)	Channel 2.1x

Median aus 3 Runden (1. wärmt auf); Total = Burst + Drain bis zum letzten Knoten (FlushAsync/Barrier); Zustellung pro Knoten verifiziert (1/1/1, 100/100/100, 1000/1000/1000, 0/0/0). Re-Put im Bus hängt an (kein Upsert); Update im Bus = vollständige Entity + Filter.

Tests und Abdeckung

Das Projekt Rochas.DapperRepository.Test (xUnit, net9.0) enthält 227 Tests, die asynchrones CRUD, Komposition, Builder, GROUP BY/Aggregate, austauschbares Caching, SQL-Parser, Type-Handler und Engine-Erkennung abdecken, mit 80 % Zeilenabdeckung der Hauptassembly und 100 % in Rochas.Data.Specification.

Name		Name	Last commit message	Last commit date
Rochas.DapperRepository.Test		Rochas.DapperRepository.Test
Rochas.DapperRepository		Rochas.DapperRepository
history		history
.gitignore		.gitignore
LICENSE		LICENSE
README.md		README.md
Rochas.DapperRepository.sln		Rochas.DapperRepository.sln

Latest commit

History

Folders and files

Repository files navigation

Rochas.DapperRepository

English

What is it?

Features

Installation

Quick start

Registration in DI

Entity example

CRUD operations

Query (typed filter)

Search (tokenized over [Filterable] properties)

Pagination

Fluent query builder

GroupBy with aggregates

DW/ETL fact entities

Pluggable caching

Benchmark — DapperRepository vs EF Core

Benchmark — Channel vs Replica (Cluster Replication)

Tests and coverage

Português

O que é?

Funcionalidades

Instalação

Exemplo de uso rápido

Registro no DI

Exemplo de entidade

Operações CRUD

Query (consulta por filtro tipado)

Search (busca tokenizada em propriedades [Filterable])

Paginação

Builder fluente de consultas

GroupBy com agregações

Entidades fato DW/ETL

Cache plugável

📊 Benchmark — DapperRepository vs EF Core

Benchmark — Channel vs Replica (Replicação em Cluster)

Testes e cobertura

Español

¿Qué es?

Características

Instalación

Inicio rápido

Registro en DI

Ejemplo de entidad

Operaciones CRUD

Query (consulta por filtro tipado)

Search (búsqueda tokenizada en propiedades [Filterable])

Paginación

Constructor fluido de consultas

GroupBy con agregaciones

Entidades de hecho DW/ETL

Caché conectable

Benchmark — DapperRepository vs EF Core

Benchmark — Channel vs Replica (Replicación en Cluster)

Pruebas y cobertura

Français

Qu'est-ce que c'est ?

Fonctionnalités

Installation

Démarrage rapide

Enregistrement dans le DI

Exemple d'entité

Opérations CRUD

Query (requête par filtre typé)

Search (recherche tokenisée sur les propriétés [Filterable])

Pagination

Constructeur de requêtes fluide

GroupBy avec agrégats

Entités de fait DW/ETL

Cache enfichable

Benchmark — DapperRepository vs EF Core

Benchmark — Channel vs Replica (Réplication en Cluster)

Tests et couverture

Deutsch

Was ist das?

Funktionen

Search (tokenized over `[Filterable]` properties)

Search (busca tokenizada em propriedades `[Filterable]`)

Search (búsqueda tokenizada en propiedades `[Filterable]`)

Search (recherche tokenisée sur les propriétés `[Filterable]`)

Search (tokenisierte Suche über `[Filterable]`-Eigenschaften)