Nahmadov.DapperForge.SqlServer 2.3.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:

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.

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