Nahmadov.DapperForge.SqlServer 2.1.1

Nahmadov.DapperForge v1

Lightweight Dapper-based data access with an EF-style surface (DapperDbContext + DapperSet<TEntity>) and fluent model building. This doc is for maintainers and consumers of v1.

---

Highlights

• Immediate execution: no change tracker, no SaveChanges.
• Fluent model builder plus data annotation support.
• SQL generation with pluggable dialects (SqlServer, Oracle).
• Mapping conventions: table name from type or [Table], key from [Key], Id, or {TypeName}Id.
• Auto-generated keys by default; override with Property(x => x.Id).AutoGenerated(false) or provide [DatabaseGenerated].
• Expression-to-SQL translator for WhereAsync/FirstOrDefaultAsync supports comparisons, null checks, booleans, string Contains/StartsWith/EndsWith (with case-insensitive option), and collection Contains → IN.
• Validation via [Required]/[StringLength] on insert/update; read-only entities protected.

---

Quick Start

// Registration (SqlServer; Oracle also supported)
services.AddDapperDbContext<AppDapperDbContext>(options =>
{
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection"));
});

// Use inside a scoped service
var users = await _db.Users.WhereAsync(u => u.IsActive && u.Name.StartsWith("a"), ignoreCase: true);
var id = await _db.Users.InsertAndGetIdAsync(new User { Name = "Alice", IsActive = true });
await _db.Users.DeleteByIdAsync(id);

Mapping with fluent API:

modelBuilder.Entity<User>(b =>
{
    b.ToTable("Users", "dbo");
    b.Property(u => u.Name).HasColumnName("username").HasMaxLength(100).IsRequired();
    b.Property(u => u.Id).AutoGenerated(false); // opt out of default identity
});

---

Sample App (samples/ConnectionSample)

• Demonstrates registration, seeding, CRUD, validation errors, read-only queries, and all predicate translations including IN.
• Configure user secrets under FullSample:
  • Provider: SqlServer (default) or Oracle
  • SqlServerConnectionString or OracleConnectionString (fallback ConnectionString for SqlServer)
• Tables expected (schemata align with fluent/attribute mappings):
  • Customers: Id (identity), FullName (nvarchar(120) not null), Email (nvarchar(200)), City (nvarchar(100)), IsActive (bit), CreatedAt (datetime), LastLogin (datetime null; read-only in fluent config).
  • SupportTickets: TicketId (identity), CustomerId (int FK), Title (nvarchar(200) not null), Description (nvarchar(500)), Status (nvarchar(50) not null), IsEscalated (bit), OpenedOn (datetime), ClosedOn (datetime null).
  • AuditLogs: Id (identity), Entity (nvarchar(100)), Action (nvarchar(50)), Details (nvarchar(200)), CreatedAt (datetime). Run the sample:

cd samples/ConnectionSample
dotnet run

---

Core Concepts

• DapperDbContext wraps a connection factory and dialect, exposes low-level Dapper wrappers plus Set<TEntity>().
• DapperSet<TEntity> provides GetAllAsync, FindAsync, WhereAsync(Expression), FirstOrDefaultAsync, InsertAsync, InsertAndGetIdAsync, UpdateAsync, DeleteAsync, DeleteByIdAsync.
• SqlGenerator<TEntity> builds SELECT/INSERT/UPDATE/DELETE; skips generated/read-only columns.
• EntityValidator<TEntity> enforces required/length rules and read-only restrictions before SQL.
• Dialects (ISqlDialect): identifier quoting, parameter formatting, boolean literals, insert-returning-key SQL.

---

Expression Translation (supported patterns)

• Comparisons: ==, !=, >, >=, <, <=
• Null checks: x.Prop == null, x.Prop != null
• Booleans: x.IsActive, !x.IsActive, x.IsActive == true/false
• Strings: Contains, StartsWith, EndsWith (optional case-insensitive lowering)
• Collections: list.Contains(x.Prop) → Prop IN (...) (empty list ⇒ 1=0)
• Logical: &&, ||, !

---

Limitations (v1)

1. No change tracker or SaveChanges; operations execute immediately.
2. Keys assumed single-column.
3. No paging helpers/soft-delete conventions.
4. Expression translator intentionally limited to common filters.
5. Oracle dialect returns null for InsertReturningId unless extended.
6. No logging hooks beyond console SQL echo in DapperDbContext.LogSql.

---

Testing

Run all tests:

dotnet test

Key coverage:

• SQL generation (identity handling, quoting)
• Predicate translation (booleans, nulls, LIKE, case-insensitive, IN)
• Context connection/transaction behavior with fakes

---

Best Practices

When to Use DapperForge vs EF Core

Use DapperForge when:

• You need maximum query performance with minimal overhead
• You're building read-heavy applications (reporting, analytics, APIs)
• You want explicit control over SQL execution and transactions
• You have existing Dapper code and want a cleaner API surface
• You need immediate execution without change tracking overhead
• Database schema is stable and migrations are handled externally
• Your team is comfortable writing predicates instead of full LINQ queries

Use EF Core when:

• You need automatic change tracking and batched SaveChanges()
• You require complex LINQ queries with aggregations, grouping, or joins
• You need database migrations managed from code
• You want full navigation property support with lazy loading
• You're building CRUD-heavy applications with complex domain models
• You need database-agnostic code (EF Core supports more providers)
• Your team prefers full ORM abstraction over SQL awareness

Performance Comparison:

Benchmark: Querying 1000 rows
- DapperForge:  ~2ms (minimal allocations)
- EF Core (No Tracking): ~4ms
- EF Core (Tracking): ~8ms (change tracking overhead)

Benchmark: Simple Insert
- DapperForge: ~0.5ms (immediate execution)
- EF Core: ~1.2ms (with SaveChanges)

Hybrid Approach: You can use both in the same application:

• EF Core for complex domain logic with change tracking
• DapperForge for read-heavy queries and bulk operations

---

Performance Tuning Guide

1. Expression Caching

DapperForge automatically caches compiled expressions to avoid repeated compilation overhead:

// First call: compiles expression and caches it
var users1 = await db.Users.WhereAsync(u => u.IsActive);

// Second call: reuses cached compiled expression (faster)
var users2 = await db.Users.WhereAsync(u => u.IsActive);

Cache Configuration:

• Default cache size: 1000 expressions
• Cache automatically clears when limit is reached
• Thread-safe via ConcurrentDictionary

2. Identity Cache for Include Operations

When using Include(), DapperForge maintains an identity cache to prevent duplicate entity instances:

// Identity cache ensures each Customer instance appears only once
var orders = await db.Orders
    .Include(o => o.Customer)
    .ToListAsync();

Cache Configuration:

• Default max size: 10,000 entities per query
• LRU eviction when limit is reached
• Automatically disposed after query completion

Tuning Identity Cache:

// For large Include queries, increase cache size in IdentityCache constructor
// (requires code modification - not currently exposed in options)

3. Connection Resilience

Configure retry logic for transient failures:

services.AddDapperDbContext<AppDbContext>(options =>
{
    options.UseSqlServer(connectionString);
    // Defaults shown - adjust based on your requirements
    // MaxRetryCount: 3 attempts
    // RetryDelayMilliseconds: 100ms base delay (exponential backoff)
    // CommandTimeoutSeconds: 30 seconds
});

Retry Strategy:

• Attempt 1: Immediate
• Attempt 2: 100ms delay
• Attempt 3: 200ms delay
• Attempt 4: 400ms delay

Transient Errors Detected:

• Timeout exceptions
• Connection failures
• Network errors
• Deadlocks
• Transport-level errors

4. Query Optimization Tips

Use Predicates for Filtering:

// GOOD: Predicate translates to WHERE clause
var activeUsers = await db.Users.WhereAsync(u => u.IsActive);

// AVOID: Fetches all rows, filters in memory
var allUsers = await db.Users.GetAllAsync();
var activeUsers = allUsers.Where(u => u.IsActive).ToList();

Avoid N+1 Queries with Include:

// GOOD: Single query with JOIN
var orders = await db.Orders
    .Include(o => o.Customer)
    .AsSplitQuery() // or AsSingleQuery()
    .ToListAsync();

// AVOID: N+1 queries (one per order)
var orders = await db.Orders.GetAllAsync();
foreach (var order in orders)
{
    order.Customer = await db.Customers.FindAsync(order.CustomerId);
}

Use FirstOrDefaultAsync Instead of GetAllAsync When Possible:

// GOOD: Returns immediately after first match
var user = await db.Users.FirstOrDefaultAsync(u => u.Email == email);

// LESS EFFICIENT: Fetches all matching rows
var users = await db.Users.WhereAsync(u => u.Email == email);
var user = users.FirstOrDefault();

5. Memory Management

Dispose Context Properly:

// GOOD: Using statement ensures connection disposal
await using var db = serviceProvider.GetRequiredService<AppDbContext>();
var users = await db.Users.GetAllAsync();

// ALSO GOOD: ASP.NET Core DI handles disposal automatically for scoped services
public class UserService
{
    private readonly AppDbContext _db;

    public UserService(AppDbContext db) => _db = db;

    public Task<List<User>> GetActiveUsers()
        => _db.Users.WhereAsync(u => u.IsActive);
}

Large Result Sets:

// For very large result sets, consider pagination
// (Note: DapperForge v1 doesn't have built-in paging - use raw SQL)
var sql = "SELECT * FROM Users ORDER BY Id OFFSET @Skip ROWS FETCH NEXT @Take ROWS ONLY";
var page = await db.QueryAsync<User>(sql, new { Skip = 0, Take = 100 });

---

Include/ThenInclude Behavior

Split Query vs Single Query

DapperForge supports two strategies for loading related entities:

AsSingleQuery (Default)

Executes a single SQL query with JOINs:

var orders = await db.Orders
    .Include(o => o.Customer)
    .AsSingleQuery()
    .ToListAsync();

// Generated SQL:
// SELECT o.*, c.*
// FROM Orders o
// LEFT JOIN Customers c ON o.CustomerId = c.Id

Advantages:

• Single database round-trip
• Better for small result sets
• Atomic consistency (single snapshot)

Disadvantages:

• Cartesian explosion with collections
• Can generate very large result sets
• Network transfer overhead with duplicate data

AsSplitQuery

Executes separate queries for each navigation level:

var orders = await db.Orders
    .Include(o => o.Customer)
    .AsSplitQuery()
    .ToListAsync();

// Generated SQL (two queries):
// Query 1: SELECT * FROM Orders
// Query 2: SELECT * FROM Customers WHERE Id IN (@ids...)

Advantages:

• No cartesian explosion
• Better for collections or multiple includes
• Less network transfer with large result sets

Disadvantages:

• Multiple database round-trips
• Potential consistency issues if data changes between queries
• More queries = more overhead for small datasets

When to Use Each Strategy

Use AsSingleQuery when:

• Loading single reference navigation (one-to-one, many-to-one)
• Result set is small (<100 rows)
• You need atomic consistency
• Network latency to database is high

Use AsSplitQuery when:

• Loading collection navigations (one-to-many)
• Multiple Include() calls on the same query
• Large result sets (>100 rows)
• Potential for cartesian explosion

Example - Collection Navigation:

// AVOID: AsSingleQuery with collections causes cartesian explosion
// If Customer has 10 Orders, Customer data is duplicated 10 times
var customers = await db.Customers
    .Include(c => c.Orders)
    .AsSingleQuery() // Bad choice here!
    .ToListAsync();

// PREFER: AsSplitQuery avoids duplication
var customers = await db.Customers
    .Include(c => c.Orders)
    .AsSplitQuery() // Better choice!
    .ToListAsync();

ThenInclude for Nested Navigation

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

// Generated SQL (three queries with AsSplitQuery):
// Query 1: SELECT * FROM Orders
// Query 2: SELECT * FROM Customers WHERE Id IN (@customerIds...)
// Query 3: SELECT * FROM Addresses WHERE CustomerId IN (@customerIds...)

Depth Limits:

• DapperForge supports unlimited ThenInclude depth
• Performance degrades with each level (more queries/joins)
• Recommended maximum: 3-4 levels deep

---

Dialect-Specific Differences

SQL Server vs Oracle

Feature	SQL Server	Oracle	Notes
Identifier Quoting	[Table], [Column]	"Table", "Column"	Automatic per dialect
Parameter Prefix	@p0, @p1	:p0, :p1	Automatic per dialect
Boolean Literals	1, 0	1, 0	Stored as bit/number
String Comparison	Case-insensitive (collation-dependent)	Case-sensitive by default	Use ignoreCase: true for consistency
Auto-generated Keys	OUTPUT INSERTED.Id	Not supported in v1	Oracle returns null from InsertAndGetIdAsync
Date Functions	GETDATE()	SYSDATE	Use raw SQL for date functions
String Concatenation	+ operator	\|\| operator	Use predicates or raw SQL
Top N Rows	TOP N	ROWNUM <= N (Oracle <12c) or FETCH FIRST N ROWS ONLY	Use raw SQL for pagination

SQL Server-Specific Features

// Auto-generated identity keys work out of the box
var id = await db.Users.InsertAndGetIdAsync(new User
{
    Name = "Alice"
});
// Returns generated identity value

// Health check query: SELECT 1
var healthy = await db.HealthCheckAsync();

Oracle-Specific Features

// InsertAndGetIdAsync returns null (no OUTPUT support in v1)
await db.Users.InsertAsync(new User
{
    Id = nextSequenceValue, // Must provide from sequence
    Name = "Alice"
});

// Workaround: Use raw SQL with RETURNING clause
var sql = "INSERT INTO Users (Name) VALUES (:name) RETURNING Id INTO :id";
var parameters = new DynamicParameters();
parameters.Add("name", "Alice");
parameters.Add("id", dbType: DbType.Int32, direction: ParameterDirection.Output);
await db.ExecuteAsync(sql, parameters);
var id = parameters.Get<int>("id");

// Health check query: SELECT 1 FROM DUAL
var healthy = await db.HealthCheckAsync();

Case Sensitivity

// SQL Server (collation-dependent, typically case-insensitive)
var users = await db.Users.WhereAsync(u => u.Name == "alice");
// Matches "Alice", "ALICE", "alice" (if collation is CI)

// Oracle (case-sensitive by default)
var users = await db.Users.WhereAsync(u => u.Name == "alice");
// Only matches "alice", NOT "Alice"

// Solution: Use ignoreCase parameter for cross-database compatibility
var users = await db.Users.WhereAsync(
    u => u.Name == "alice",
    ignoreCase: true
);
// Generates: WHERE LOWER([Name]) = LOWER(@p0)
// Works consistently across both databases

Schema Handling

// SQL Server: Default schema is "dbo"
modelBuilder.Entity<User>(b =>
    b.ToTable("Users", "dbo")); // Generates: [dbo].[Users]

// Oracle: Default schema is username (e.g., "SYSTEM")
modelBuilder.Entity<User>(b =>
    b.ToTable("Users", "MYSCHEMA")); // Generates: "MYSCHEMA"."Users"

// Cross-database: Let dialect handle default schema
modelBuilder.Entity<User>(b =>
    b.ToTable("Users")); // Uses dialect's default schema

---

Migration from EF Core

API Mapping

EF Core	DapperForge	Notes
DbContext	DapperDbContext	Base class for context
DbSet<T>	DapperSet<T>	Entity collection
ToListAsync()	GetAllAsync() or ToListAsync()	Fetch all entities
FirstOrDefaultAsync()	FirstOrDefaultAsync()	Same API
SingleOrDefaultAsync()	SingleOrDefaultAsync()	Same API
AnyAsync()	AnyAsync()	Same API
CountAsync()	CountAsync()	Same API
FindAsync(id)	FindAsync(id)	Find by primary key
Where(predicate).ToListAsync()	WhereAsync(predicate)	Expression-based filtering
Add(entity) + SaveChanges()	InsertAsync(entity)	Immediate execution
Update(entity) + SaveChanges()	UpdateAsync(entity)	Immediate execution
Remove(entity) + SaveChanges()	DeleteAsync(entity)	Immediate execution
Include(x => x.Nav)	Include(x => x.Nav)	Same API
ThenInclude(x => x.Nav)	ThenInclude(x => x.Nav)	Same API
AsNoTracking()	N/A	DapperForge never tracks changes
SaveChangesAsync()	N/A	All operations execute immediately

Step-by-Step Migration

1. Update Package References

<PackageReference Include="Microsoft.EntityFrameworkCore" Version="8.0.0" Remove="true" />
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="8.0.0" Remove="true" />


<PackageReference Include="Nahmadov.DapperForge.Core" Version="1.0.0" />
<PackageReference Include="Nahmadov.DapperForge.SqlServer" Version="1.0.0" />

<PackageReference Include="Nahmadov.DapperForge.Oracle" Version="1.0.0" />

2. Update Context Registration

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

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

3. Update Context Class

// EF Core
public class AppDbContext : DbContext
{
    public DbSet<User> Users { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>(b =>
        {
            b.ToTable("Users");
            b.HasKey(u => u.Id);
            b.Property(u => u.Name).IsRequired().HasMaxLength(100);
        });
    }
}

// DapperForge (very similar!)
public class AppDbContext : DapperDbContext
{
    public DapperSet<User> Users => Set<User>();

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

    protected override void OnModelCreating(DapperModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>(b =>
        {
            b.ToTable("Users");
            // HasKey not needed - inferred from [Key] or Id property
            b.Property(u => u.Name).IsRequired().HasMaxLength(100);
        });
    }
}

4. Update Repository/Service Code

// EF Core
public class UserService
{
    private readonly AppDbContext _context;

    public async Task<List<User>> GetActiveUsers()
    {
        return await _context.Users
            .Where(u => u.IsActive)
            .ToListAsync();
    }

    public async Task CreateUser(User user)
    {
        _context.Users.Add(user);
        await _context.SaveChangesAsync();
    }
}

// DapperForge (minimal changes)
public class UserService
{
    private readonly AppDbContext _context;

    public async Task<List<User>> GetActiveUsers()
    {
        // Change Where().ToListAsync() to WhereAsync()
        return await _context.Users
            .WhereAsync(u => u.IsActive);
    }

    public async Task CreateUser(User user)
    {
        // Remove Add() + SaveChangesAsync(), use InsertAsync()
        await _context.Users.InsertAsync(user);
    }
}

5. Handle Change Tracking Scenarios

// EF Core - Implicit update via change tracking
var user = await _context.Users.FindAsync(id);
user.Name = "Updated";
await _context.SaveChangesAsync(); // Detects changes

// DapperForge - Explicit update required
var user = await _context.Users.FindAsync(id);
user.Name = "Updated";
await _context.Users.UpdateAsync(user); // Explicit update

6. Handle Complex LINQ Queries

// EF Core - Complex LINQ with grouping
var report = await _context.Orders
    .GroupBy(o => o.CustomerId)
    .Select(g => new { CustomerId = g.Key, Total = g.Sum(o => o.Amount) })
    .ToListAsync();

// DapperForge - Use raw SQL for complex queries
var sql = @"
    SELECT CustomerId, SUM(Amount) as Total
    FROM Orders
    GROUP BY CustomerId";
var report = await _context.QueryAsync<ReportDto>(sql);

Common Migration Pitfalls

1. Forgetting to Remove SaveChanges()

// WRONG: SaveChanges doesn't exist in DapperForge
await _context.Users.InsertAsync(user);
await _context.SaveChangesAsync(); // Compilation error!

// CORRECT: Operations execute immediately
await _context.Users.InsertAsync(user);

2. Using Navigation Properties Without Include

// EF Core with lazy loading
var orders = await _context.Orders.ToListAsync();
foreach (var order in orders)
{
    Console.WriteLine(order.Customer.Name); // Lazy loads Customer
}

// DapperForge - Must use Include (no lazy loading)
var orders = await _context.Orders
    .Include(o => o.Customer)
    .ToListAsync();
foreach (var order in orders)
{
    Console.WriteLine(order.Customer.Name); // Already loaded
}

3. Transaction Handling

// EF Core
using var transaction = await _context.Database.BeginTransactionAsync();
try
{
    _context.Users.Add(user);
    await _context.SaveChangesAsync();
    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

// DapperForge
var transaction = await _context.BeginTransactionAsync();
try
{
    await _context.Users.InsertAsync(user); // Pass transaction if needed
    transaction.Commit();
}
catch
{
    transaction.Rollback();
    throw;
}

---

Versioning

This document and code reflect Version 1 of Nahmadov.DapperForge.