Nahmadov.DapperForge.Sqlite 2.5.0

Nahmadov.DapperForge

A lightweight, high-performance Dapper-based data access layer that provides an Entity Framework-style API surface while maintaining Dapper's speed and simplicity.

Key Philosophy

• Immediate execution — No change tracker, no SaveChanges()
• Explicit control — Direct SQL execution for maximum performance
• Minimal allocations — Reduced memory pressure compared to EF Core
• Database-agnostic — Pluggable SQL dialects (SQL Server, Oracle, SQLite)
• Fluent API — Familiar EF-like query and configuration syntax

NuGet Packages

Quick Start

Installation

dotnet add package Nahmadov.DapperForge.Core
dotnet add package Nahmadov.DapperForge.SqlServer

Define Your Context

public class AppDbContext : DapperDbContext
{
    public DapperSet<Customer> Customers => Set<Customer>();
    public DapperSet<Order> Orders => Set<Order>();

    public AppDbContext(DapperDbContextOptions<AppDbContext> options)
        : base(options) { }

    protected override void OnModelCreating(DapperModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Customer>(b =>
        {
            b.ToTable("Customers", "dbo");
            b.HasKey(c => c.Id);
            b.Property(c => c.Name).HasMaxLength(100).IsRequired();
        });
    }
}

Register in DI

services.AddDapperDbContext<AppDbContext>(options =>
{
    options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection"));
});

Use in Your Application

var activeCustomers = await _db.Customers
    .Where(c => c.IsActive)
    .OrderBy(c => c.Name)
    .ToListAsync();

var id = await _db.Customers.InsertAndGetIdAsync<int>(new Customer
{
    Name = "Alice",
    IsActive = true
});

await _db.Customers.DeleteByIdAsync(id);

Features

Fluent Query API

var customers = await db.Customers
    .Where(c => c.IsActive && c.Name.StartsWith("John"))
    .OrderBy(c => c.Name)
    .Skip(10)
    .Take(20)
    .ToListAsync();

Expression Translation

LINQ expressions are translated to parameterized SQL. Supported patterns:

Case-insensitive filtering is available via the ignoreCase parameter:

var customers = await db.Customers.WhereAsync(
    c => c.Name.Contains("john"), ignoreCase: true);
// WHERE LOWER(a.[Name]) LIKE LOWER(@p0)

Include / ThenInclude

Load related entities with an EF-style API:

var orders = await db.Orders
    .Include(o => o.Customer)
        .ThenInclude(c => c.Address)
    .Include(o => o.OrderItems)
        .ThenInclude(i => i.Product)
    .AsSplitQuery()
    .ToListAsync();

AsSingleQuery (default) — single SQL with JOINs; best for small result sets. AsSplitQuery — separate queries with IN clauses; avoids cartesian explosion for collections.

Bulk Insert

Insert large collections efficiently using automatic batching. Entities are split into batches that respect database parameter limits (2,100 for SQL Server, 32,767 for Oracle).

var products = GenerateProducts(5000);

var result = await db.Products.BulkInsertAsync(products);

Console.WriteLine($"Inserted {result.TotalAffected} rows in {result.BatchCount} batch(es), took {result.ElapsedTime}");

Configure with BulkInsertOptions:

var result = await db.Products.BulkInsertAsync(products, new BulkInsertOptions
{
    BatchSize = 200,
    CommandTimeout = 60,
    ValidateBeforeInsert = true   // default: true
});

Works with transactions:

var scope = await db.BeginTransactionScopeAsync();
try
{
    await db.Products.BulkInsertAsync(products, transaction: scope.Transaction);
    scope.Complete();
}
finally
{
    scope.Dispose();
}

Bulk Merge (Upsert)

Perform INSERT-or-UPDATE (MERGE) operations in bulk. Uses the SQL MERGE statement under the hood.

var result = await db.Products.BulkMergeAsync(products);

Console.WriteLine($"Affected {result.TotalAffected} rows (inserted: {result.RowsInserted}, updated: {result.RowsUpdated})");

Control merge behavior with BulkMergeOptions:

var result = await db.Products.BulkMergeAsync(products, new BulkMergeOptions
{
    MatchColumns = new[] { "TenantId", "ProductCode" },  // columns used for matching
    Mode = MergeMode.InsertOrUpdate,  // or InsertOnly, UpdateOnly
    BatchSize = 100,
    ValidateBeforeMerge = true
});

Shorthand for custom match columns:

var result = await db.Products.BulkMergeAsync(
    products,
    matchColumns: new[] { "TenantId", "ProductCode" });

Merge Modes:

Bulk Copy

First-class bulk insert that derives column mappings from the entity mapping — no more hand-written new SqlBulkCopy(...) + ColumnMappings or manual DataTable plumbing. SQL Server uses SqlBulkCopy; SQLite uses a batched parameterized-insert fallback.

using var scope = db.CreateConnectionScope();

// Stage into a temp table that mirrors the entity, then bulk-copy into it:
await db.Customers.CreateTempTableLikeAsync("#StagingCustomers", scope.Connection);
int copied = await db.Customers.BulkCopyAsync(customers, "#StagingCustomers", scope.Connection);

// DataTable-driven overload on the context:
await db.BulkCopyAsync(dataTable, "#StagingCustomers", scope.Connection);

Tune with BulkCopyOptions (BatchSize, TimeoutSeconds, EnableStreaming, UseTableLock — SqlBulkCopy-specific options are ignored by SQLite). The temp-table DDL and bulk-copy column mappings share the same EntityMapping, so CreateTempTableLikeAsync<T> and BulkCopyAsync(rows, "#tmp") compose cleanly. Dialects without bulk-copy support throw (ISqlDialect.SupportsBulkCopy == false).

Session Temp Tables

Create a session temp table without hand-writing DDL. Column types come from the same mapping metadata DapperForge already owns, so the temp shape stays in sync with your entities.

using var scope = db.CreateConnectionScope();

await db.TempTable("TmpHistDaily")          // SQL Server ensures the leading '#'
    .Column<int>("MVSID")
    .Column<int>("HistColID")
    .Column<DateTime>("HistDate")
    .Column("Value", SqlColumnType.Decimal, new ColumnTypeFacets(Precision: 18, Scale: 4))
    .Column<DateTime>("InsertDate", nullable: true)
    .CreateAsync(scope.Connection);

• Column<T>(...) infers the SQL type from the CLR type; Column(name, type, facets) is explicit.
• BuildCreateTableSql() returns the CREATE [TEMP] TABLE statement without executing it.
• SQL Server → CREATE TABLE #TmpHistDaily ( … ); SQLite → CREATE TEMP TABLE "TmpHistDaily" ( … ).
• Temp tables are connection-scoped — create and use them on the same connection (CreateConnectionScope()).

Already have a DataTable? Derive the temp table directly from its schema:

await db.CreateTempTableFromAsync("TempImport", dataTable, scope.Connection);
// Each DataColumn → a column: type from DataType, nullability from AllowDBNull, length from MaxLength.

Or mirror a mapped entity — the temp table uses the same column names and types as the entity (identity/generated columns excluded), so it composes cleanly with bulk copy into the staging table:

await db.Customers.CreateTempTableLikeAsync("#StagingCustomers", scope.Connection);

// Subset of columns, in the given order:
await db.Customers.CreateTempTableLikeAsync(
    "#StagingCustomers", scope.Connection, c => c.Id, c => c.Name);

// Convenience by type:
await db.CreateTempTableLikeAsync<Customer>("#StagingCustomers", scope.Connection);

Other dialects throw NotSupportedException (ISqlDialect.SupportsSessionTempTables == false).

Transactions

var scope = await db.BeginTransactionScopeAsync();
try
{
    var customerId = await db.Customers.InsertAndGetIdAsync<int>(customer, scope.Transaction);
    order.CustomerId = customerId;
    await db.Orders.InsertAsync(order, scope.Transaction);
    scope.Complete();
}
finally
{
    scope.Dispose();
}

Validation

Entities are validated before insert/update using [Required], [MaxLength], [StringLength] attributes or fluent configuration.

modelBuilder.Entity<Customer>(b =>
{
    b.Property(c => c.Name).IsRequired().HasMaxLength(100);
});

// Throws DapperValidationException on invalid data
await db.Customers.InsertAsync(customer);

Read-only entities and properties are protected from mutation.

Automatic Retry

Transient database failures (timeouts, deadlocks, connection drops) are automatically retried with exponential backoff.

Entity Configuration

Fluent API

public class CustomerConfiguration : IEntityTypeConfiguration<Customer>
{
    public void Configure(EntityTypeBuilder<Customer> builder)
    {
        builder.ToTable("Customers", "dbo");
        builder.HasKey(c => c.Id);
        builder.Property(c => c.Name).HasColumnName("FullName").HasMaxLength(100).IsRequired();
        builder.Property(c => c.CreatedAt).IsReadOnly();
    }
}

Apply configurations:

protected override void OnModelCreating(DapperModelBuilder modelBuilder)
{
    modelBuilder.ApplyConfigurationsFromAssembly(Assembly.GetExecutingAssembly());
}

Data Annotations

[Table("Customers", Schema = "dbo")]
public class Customer
{
    [Key]
    public int Id { get; set; }

    [Required, MaxLength(100), Column("FullName")]
    public string Name { get; set; }

    [ReadOnly]
    public DateTime CreatedAt { get; set; }
}

Key Detection

Keys are resolved in order: [Key] attribute, fluent HasKey(), property named Id, or property named {TypeName}Id.

Column Types

DapperForge resolves each column's SQL type from a dialect-agnostic SqlColumnType enum. By default the type is inferred from the CLR property type (int→Int, decimal→Decimal, DateTime→DateTime2, string→NVarChar, byte[]→VarBinary, Nullable<T>→underlying type marked nullable). Override it explicitly when needed:

modelBuilder.Entity<Invoice>(b =>
{
    b.Property(i => i.Code).HasColumnType(SqlColumnType.VarChar, 50);     // length facet
    b.Property(i => i.Amount).HasColumnType(SqlColumnType.Decimal, 18, 4); // precision/scale
    b.Property(i => i.Notes).HasColumnType(SqlColumnType.Text);
});

These types drive the temp-table and bulk-copy APIs. SQL Server resolves to concrete types (nvarchar(50), decimal(18,4), …); SQLite resolves to storage affinities (INTEGER, REAL, NUMERIC, TEXT, BLOB).

Database Dialects

// SQL Server
options.UseSqlServer(connectionString);

// Oracle
options.UseOracle(connectionString);

CRUD API Reference

Architecture

Application Layer
       │
   DapperDbContext  (connection management, entity sets)
       │
  ┌────┼────┐
Query  Mutation  Config
  │    │         │
  SQL Generation Layer  (SqlGenerator, PredicateVisitor)
       │
  SQL Dialect Layer  (SqlServer, Oracle)
       │
  Dapper + ADO.NET

Design patterns: Repository (DapperSet<T>), Unit of Work (TransactionScope), Builder (model configuration), Strategy (ISqlDialect), Facade, Factory, Template Method.

Performance

• No change tracker — minimal memory overhead
• SQL statements pre-generated and cached
• Expression compilation cached (thread-safe LRU, max 1,000 entries)
• Identity cache prevents duplicate instances during Include operations
• Parameterized queries enable database query plan caching
• Bulk operations use automatic batching for optimal throughput

Migration from EF Core

API Equivalence

Step 1 — Replace the Context Base Class

// EF Core
public class AppDbContext : DbContext
{
    public DbSet<Customer> Customers => Set<Customer>();
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }
    protected override void OnModelCreating(ModelBuilder modelBuilder) { ... }
}

// DapperForge
public class AppDbContext : DapperDbContext
{
    public DapperSet<Customer> Customers => Set<Customer>();
    public AppDbContext(DapperDbContextOptions<AppDbContext> options) : base(options) { }
    protected override void OnModelCreating(DapperModelBuilder modelBuilder) { ... }
}

Step 2 — Register the Context

// EF Core
services.AddDbContext<AppDbContext>(o => o.UseSqlServer(connectionString));

// DapperForge
services.AddDapperDbContext<AppDbContext>(o => o.UseSqlServer(connectionString));

Both register the context as scoped — do not change the lifetime.

Step 3 — Convert Entity Configuration

EF Core IEntityTypeConfiguration<T> classes can be reused with minimal changes:

// EF Core
public class CustomerConfiguration : IEntityTypeConfiguration<Customer>
{
    public void Configure(EntityTypeBuilder<Customer> builder)
    {
        builder.ToTable("Customers");
        builder.HasKey(c => c.Id);
        builder.Property(c => c.Name).HasColumnName("CustomerName");
    }
}

// DapperForge — identical interface, use DapperEntityTypeBuilder<T>
public class CustomerConfiguration : IEntityTypeConfiguration<Customer>
{
    public void Configure(DapperEntityTypeBuilder<Customer> builder)
    {
        builder.ToTable("Customers");
        builder.HasKey(c => c.Id);
        builder.Property(c => c.Name).HasColumnName("CustomerName");
    }
}

Step 4 — Convert Query Patterns

// EF Core
var customers = await ctx.Customers
    .Where(c => c.IsActive)
    .OrderBy(c => c.Name)
    .Skip(20).Take(10)
    .ToListAsync();

// DapperForge — identical fluent API
var customers = await ctx.Customers
    .Query()
    .Where(c => c.IsActive)
    .OrderBy(c => c.Name)
    .Skip(20).Take(10)
    .ToListAsync();

Step 5 — Convert Mutations

// EF Core
ctx.Customers.Add(customer);
await ctx.SaveChangesAsync();

// DapperForge — immediate execution
await ctx.Customers.InsertAsync(customer);

Step 6 — Convert Transactions

// EF Core
await using var tx = await ctx.Database.BeginTransactionAsync();
try { ...; await ctx.SaveChangesAsync(); await tx.CommitAsync(); }
catch { await tx.RollbackAsync(); }

// DapperForge
var scope = await ctx.BeginTransactionScopeAsync();
try
{
    await ctx.Customers.InsertAsync(customer, scope.Transaction);
    await ctx.Orders.InsertAsync(order, scope.Transaction);
    scope.Complete();
}
finally { scope.Dispose(); }

Step 7 — Convert Include Queries

// EF Core
var orders = await ctx.Orders
    .Include(o => o.Customer)
    .Include(o => o.Lines)
    .AsNoTracking()
    .ToListAsync();

// DapperForge (single JOIN query — default)
var orders = await ctx.Orders
    .Query()
    .Include(o => o.Customer)
    .Include(o => o.Lines)
    .ToListAsync();

// DapperForge (split query — one SELECT per Include level)
var orders = await ctx.Orders
    .Query()
    .Include(o => o.Customer)
    .Include(o => o.Lines)
    .AsSplitQuery()
    .ToListAsync();

Known Gaps

Limitations

1. Single-column keys only — no composite key support
2. Limited LINQ — no aggregations, grouping, or complex joins in the fluent API (use raw SQL)
3. No lazy loading — must use Include
4. No change tracking
5. No migrations — DDL managed externally
6. Expression translator covers common filter patterns only

Project Structure

DapperToolkit/
├── src/
│   ├── Nahmadov.DapperForge.Core/       # Core library
│   ├── Nahmadov.DapperForge.SqlServer/  # SQL Server dialect
│   ├── Nahmadov.DapperForge.Oracle/     # Oracle dialect
│   └── Nahmadov.DapperForge.Sqlite/     # SQLite dialect
├── tests/
│   └── Nahmadov.DapperForge.UnitTests/  # Unit tests
├── samples/
│   └── ConnectionSample/                # Usage samples
└── docs/                                # Detailed documentation

Testing

dotnet test

Sample App

cd samples/ConnectionSample
dotnet run

Configure via user secrets (FullSample):

• Provider: SqlServer (default) or Oracle
• SqlServerConnectionString / OracleConnectionString

Documentation

• Overview
• Architecture
• Usage Guide
• Complete Reference