SQLFactory 28.2606.0

SQLFactory

High-performance, lightweight SQL mapper and CRUD helper library for .NET developers

---

📋 Table of Contents

• Overview
• Features
• Quick Start
• Installation
• Documentation
• Examples
• Building
• Testing
• Contributing
• License

---

🎯 Overview

SQLFactory is a lightweight, high-performance SQL mapping library that provides intuitive CRUD operations and advanced querying capabilities for .NET applications. It bridges the gap between micro-ORMs and full-featured ORMs, offering simplicity without sacrificing control.

Key Highlights

• 🚀 High Performance - Minimal overhead with optimized data access patterns
• 🔧 Developer-Friendly - Intuitive API with strong typing and IntelliSense support
• 🌐 Cross-Platform - Runs on Windows, Linux, and macOS
• 📦 Lightweight - Small footprint with minimal dependencies
• 🎯 Flexible - Works with POCO classes, annotated models, or dynamic queries
• 🔒 Production-Ready - Battle-tested with comprehensive unit tests

---

✨ Features

Core Capabilities

• CRUD Operations - Full Create, Read, Update, Delete support with simple API
• SQL Builder - Fluent interface for building complex SQL queries
• Object Mapping - Automatic mapping between database records and .NET objects
• POCO Support - Work with plain C# classes without attributes
• Annotated Models - Optional attribute-based mapping for fine control
• Dynamic Queries - Build queries dynamically at runtime
• SqlSet - Collection-like access to database tables
• Transaction Support - Built-in transaction management
• Multi-Database - SQL Server, SQLite, PostgreSQL, MySQL, MariaDB, Oracle, DuckDB, ClickHouse, Firebird, CockroachDB, YugabyteDB, and other ADO.NET providers

Eager Loading 🆕

• Include() - Load single reference navigation properties (many-to-1, 1-to-1)
• Include() Collections - Load collection navigation properties (1-to-many)
• ThenInclude() - Multi-level nested navigation properties
• Async Eager Loading - Full async/await support with ToListAsync()
• Performance - 99.8% query reduction vs N+1 problem
• Convention-Based - Automatic FK discovery by naming convention
• Split Query Pattern - Avoids cartesian explosion (separate queries for collections)

Query Filters 🆕

• Global Filters - Register once, apply everywhere automatically
• Soft Delete - entity => !entity.IsDeleted applied to all queries
• Multi-Tenancy - entity => entity.TenantId == currentTenant
• Admin Override - .IgnoreQueryFilters() for special scenarios
• Expression-to-SQL - Converts LINQ expressions to SQL WHERE clauses
• Type-Safe - Compile-time checking with full IntelliSense

Technical Features

• ✅ Nullable reference types enabled
• ✅ Async/await support throughout
• ✅ Code analysis and style rules enforced
• ✅ XML documentation for all public APIs
• ✅ Source Link support for debugging
• ✅ Deterministic builds

Advanced Features

All 54 features (23 original + 31 new in v3.0) are production-ready with 999 passing tests. v3 Enterprise Refactor complete (35/35 phases). See CHANGELOG.md for detailed implementation history and phase breakdown.

---

🎓 Feature Deep Dive

1. Core CRUD Operations

SQLFactory provides intuitive, strongly-typed CRUD operations with minimal boilerplate:

var db = new Database(connectionString);

// INSERT
var product = new Product { Name = "Laptop", Price = 1299.99m };
db.Insert(product);  // Auto-retrieves generated ID

// SELECT
var products = db.Query<Product>("SELECT * FROM Products").ToList();
var product = db.Single<Product>("SELECT * FROM Products WHERE Id = @0", 1);
var exists = db.ExecuteScalar<bool>("SELECT COUNT(*) > 0 FROM Products WHERE Name = @0", "Laptop");

// UPDATE
product.Price = 1199.99m;
db.Update(product);

// DELETE
db.Delete(product);

// BATCH OPERATIONS
db.Execute("UPDATE Products SET Price = Price * 1.1 WHERE CategoryId = @0", 1);

Key Features:

• ✅ Automatic ID retrieval after INSERT
• ✅ Parameterized queries (SQL injection prevention)
• ✅ Strongly-typed results
• ✅ Batch operations support
• ✅ Transaction management built-in

2. SQL Builder - Fluent Query Construction

Build complex queries programmatically with IntelliSense support:

var query = new SqlBuilder()
    .Select("p.Id", "p.Name", "p.Price", "c.Name AS CategoryName")
    .From("Products p")
    .InnerJoin("Categories c", "p.CategoryId = c.Id")
    .Where("p.Price > @0", 100)
    .Where("p.Stock > @0", 0)
    .OrderBy("p.Price DESC")
    .Take(10);

var results = db.Query<ProductDto>(query.ToSql()).ToList();

Capabilities:

• ✅ SELECT, FROM, JOIN (INNER/LEFT/RIGHT/FULL)
• ✅ WHERE (AND/OR), GROUP BY, HAVING
• ✅ ORDER BY, LIMIT/OFFSET pagination
• ✅ Subqueries and CTEs
• ✅ Dynamic query building at runtime
• ✅ SQL injection safe with parameterized queries

3. Eager Loading - Performance Optimization

Eliminate N+1 query problems with automatic relationship loading:

// N+1 Problem (BAD):
var products = db.Query<Product>("SELECT * FROM Products").ToList();
foreach (var product in products) {
    product.Category = db.Single<Category>("SELECT * FROM Categories WHERE Id = @0", product.CategoryId);
    // 1 + N queries! (1 for products, N for categories)
}

// Eager Loading (GOOD):
var products = db.Query<Product>()
    .Include(p => p.Category)  // Single JOIN query
    .ToList();

// Multi-level loading:
var products = db.Query<Product>()
    .Include(p => p.Category)
    .ThenInclude<Product, Category, Region>(c => c.Region)
    .ToList();

// Collection loading:
var categories = db.Query<Category>()
    .Include(c => c.Products)  // 1-to-many
    .ToList();

Performance:

• ✅ 99.8% query reduction vs N+1 problem
• ✅ Automatic JOIN generation
• ✅ Convention-based FK discovery
• ✅ Async support (ToListAsync())
• ✅ Split query pattern for large collections

4. Global Query Filters - Cross-Cutting Concerns

Apply filters automatically to all queries:

// Multi-tenancy
db.AddGlobalFilter<Product>(p => p.TenantId == currentTenantId);

// Soft delete
db.AddGlobalFilter<Product>(p => !p.IsDeleted);

// All queries automatically apply filters
var products = db.Query<Product>("SELECT * FROM Products").ToList();
// SQL: SELECT * FROM Products WHERE TenantId = @p0 AND IsDeleted = 0

// Override for admin scenarios
var allProducts = db.Query<Product>()
    .IgnoreQueryFilters()
    .ToList();

Use Cases:

• ✅ Multi-tenancy (SaaS applications)
• ✅ Soft delete (data retention)
• ✅ Security (row-level access control)
• ✅ Active/Inactive records
• ✅ Date range filtering (historical data)

5. Change Tracking & Unit of Work

Track entity changes and batch database operations:

db.EnableChangeTracking();

// Load entity
var product = db.Single<Product>("SELECT * FROM Products WHERE Id = @0", 1);

// Modify
product.Price = 1499.99m;
product.Stock = 50;

// Detect changes
var changes = db.DetectChanges(product);
// Returns: [{ Property: "Price", Old: 1299.99, New: 1499.99 }, 
//           { Property: "Stock", Old: 45, New: 50 }]

// Batch save (Unit of Work pattern)
var products = db.Query<Product>("SELECT * FROM Products WHERE Stock < 10").ToList();
foreach (var p in products) {
    p.Stock += 20;  // Restock
}
db.SaveChanges();  // Single transaction for all changes

Benefits:

• ✅ Automatic change detection
• ✅ Batch operations (performance)
• ✅ Transaction management
• ✅ Original values tracking
• ✅ Relationship fixup

6. Read/Write Splitting - Horizontal Scaling 🔥

Scale reads with master-replica architecture:

var config = new ReadWriteConfiguration
{
    PrimaryConnectionString = "Server=master;...",
    ReplicaConnectionStrings = new[]
    {
        "Server=replica1;...",
        "Server=replica2;..."
    },
    LoadBalancingStrategy = LoadBalancingStrategy.RoundRobin,
    EnableStickySessions = true,
    StickySessionWindow = TimeSpan.FromSeconds(30)
};

db.WithReadWriteSplitting(config);

// Writes go to primary
db.Insert(product);  // → master

// Reads go to replicas (load balanced)
var products = db.Query<Product>("SELECT * FROM Products").ToList();  // → replica1/replica2

// Explicit routing
var critical = db.UsePrimary()
    .Single<Product>("SELECT * FROM Products WHERE Id = @0", 1);  // → master

Features:

• ✅ Automatic query routing (reads → replicas, writes → primary)
• ✅ Load balancing (RoundRobin, Random, PrimaryReplica)
• ✅ Sticky sessions (read-after-write consistency)
• ✅ Connection pooling (100 connections/pool)
• ✅ Explicit routing hints
• ✅ Production-ready (17/17 tests passing)

7. Soft Delete - Data Retention

Implement soft delete pattern with automatic filtering:

public class Product : ISoftDeletable
{
    public int Id { get; set; }
    public string Name { get; set; }
    public bool IsDeleted { get; set; }
    public DateTime? DeletedAt { get; set; }
}

// Soft delete
db.SoftDelete(product);  // Sets IsDeleted = true

// Queries automatically exclude deleted
var active = db.Query<Product>("SELECT * FROM Products").ToList();
// SQL: SELECT * FROM Products WHERE IsDeleted = 0

// Include deleted for admin
var all = db.Query<Product>()
    .IncludeDeleted()
    .ToList();

// Restore
db.Restore(product);  // Sets IsDeleted = false

Capabilities:

• ✅ ISoftDeletable interface
• ✅ Automatic filtering
• ✅ Soft/Hard delete operations
• ✅ Restore functionality
• ✅ Audit trail (DeletedAt, DeletedBy)

8. Query Result Caching - Performance

Cache expensive queries in memory:

// Cache for 5 minutes
var products = db.Query<Product>("SELECT * FROM Products")
    .Cacheable(TimeSpan.FromMinutes(5))
    .ToList();

// First call: database query
// Subsequent calls (within 5 min): cached result

// Clear cache
db.ClearCache<Product>();

// Performance gains
// Without cache: 1000 queries = 2500ms
// With cache:    1000 queries = 5ms (500x faster!)

Features:

• ✅ LRU eviction policy
• ✅ Configurable expiration
• ✅ Cache key generation (SQL + parameters)
• ✅ Type-safe clearing
• ✅ In-memory storage (millisecond access)

9. Bulk Operations - High Performance

Process large datasets efficiently:

// Generate 10,000 products
var products = Enumerable.Range(1, 10000)
    .Select(i => new Product { Name = $"Product {i}", Price = i })
    .ToList();

// Single INSERT: 10,000 queries = ~15 seconds
foreach (var product in products) {
    db.Insert(product);  // Slow!
}

// Bulk INSERT: 1 operation = ~300ms (50x faster!)
db.BulkInsert(products);

// Bulk UPDATE
db.BulkUpdate(products);

// Bulk DELETE
db.BulkDelete(products);

Performance:

• ✅ 50-100x faster than single operations
• ✅ Single transaction
• ✅ Automatic batching (optimal batch size)
• ✅ Progress reporting
• ✅ Rollback on error

10. Lazy Loading - On-Demand Navigation

Load related entities only when accessed:

db.EnableLazyLoading();

var product = db.Single<Product>("SELECT * FROM Products WHERE Id = @0", 1);
// No joins yet - lightweight query

// First access to Category triggers load
var categoryName = product.Category.Name;
// SQL: SELECT * FROM Categories WHERE Id = @0

// Collections loaded on demand
foreach (var orderItem in product.OrderItems) {
    Console.WriteLine(orderItem.Quantity);
    // SQL executed on first iteration
}

Features:

• ✅ Castle.DynamicProxy-based
• ✅ Reference & collection navigation
• ✅ Circular reference prevention
• ✅ N+1 query warnings
• ✅ Hybrid Include() + Lazy Loading

11. Optimistic Concurrency - Conflict Handling

Prevent lost updates with version checking:

public class Product {
    public int Id { get; set; }
    
    [RowVersion]
    public byte[] RowVersion { get; set; }  // Auto-checked
}

// User 1: Load and modify
var product1 = db.Single<Product>("SELECT * FROM Products WHERE Id = @0", 1);
product1.Price = 1299.99m;

// User 2: Load and modify
var product2 = db.Single<Product>("SELECT * FROM Products WHERE Id = @0", 1);
product2.Price = 1399.99m;

// User 2: Update succeeds
db.Update(product2);  // RowVersion incremented

// User 1: Update fails
try {
    db.Update(product1);  // DbConcurrencyException!
} catch (DbConcurrencyException) {
    // Reload, merge changes, retry
}

Strategies:

• ✅ [RowVersion] attribute
• ✅ Automatic version checking
• ✅ DbConcurrencyException on conflict
• ✅ Database-specific implementations
• ✅ Client/server win scenarios

12. Code Generation - Scaffolding Tool

Generate entities and contexts from existing databases:

# Install CLI tool
dotnet tool install --global SQLFactory-CodeGen

# Scaffold from SQLite
sqlfactory-codegen --provider sqlite --connection "Data Source=app.db" --output ./Models

# Scaffold from SQL Server
sqlfactory-codegen --provider sqlserver --connection "Server=localhost;Database=MyDb;..." --output ./Entities

# Generate repository pattern
sqlfactory-codegen --provider postgres --connection "..." --output ./Data --repository

Generates:

• ✅ POCO classes with attributes
• ✅ DbContext scaffolding
• ✅ Repository interfaces
• ✅ Fluent configuration
• ✅ Support for SQLite, SQL Server, PostgreSQL, MySQL, MariaDB, Oracle, DuckDB, ClickHouse, Firebird, CockroachDB, YugabyteDB

13. 🔥 Snowflake ID Generator - Distributed Systems

Generate globally unique, time-ordered IDs across multiple servers:

using AnubisWorks.SQLFactory.DistributedId;

// Configure generator (datacenter ID: 1, worker ID: 5)
var config = new SnowflakeConfig(datacenterId: 1, workerId: 5);
var generator = new SnowflakeIdGenerator(config);

// Generate unique IDs
long id1 = generator.NextId();  // 1234567890123456789
long id2 = generator.NextId();  // 1234567890123456790

// IDs are sortable by time
// Parse ID to extract metadata
var (timestamp, datacenterId, workerId, sequence) = generator.ParseId(id1);

Use Cases:

• ✅ Distributed systems - Unique IDs across multiple servers (1024 workers)
• ✅ High throughput - 4M IDs/second/worker theoretical, >100k verified
• ✅ Chronological sorting - Time-ordered IDs (millisecond precision)
• ✅ No coordination - No database/Redis needed
• ✅ Clock drift protection - Detects backward time jumps

14. 🔥 Multi-Tenant Support - SaaS Applications

Isolate data per tenant with database-per-tenant architecture:

using AnubisWorks.SQLFactory.MultiTenant;

// Configure tenants
var tenantManager = new TenantManager();
tenantManager.AddTenant(new TenantConfig
{
    TenantId = "customer-a",
    ConnectionString = "Server=db1;Database=CustomerA;...",
    Description = "Customer A Production"
});

tenantManager.AddTenant(new TenantConfig
{
    TenantId = "customer-b",
    ConnectionString = "Server=db2;Database=CustomerB;..."
});

// Execute in tenant context
tenantManager.WithTenant("customer-a", (db) =>
{
    var products = db.Query<Product>("SELECT * FROM Products").ToList();
    // Query executed against customer-a database
});

// Or get tenant-specific database
var customerADb = tenantManager.ForTenant("customer-a");
var orders = customerADb.Query<Order>("SELECT * FROM Orders").ToList();

// Ambient tenant resolution (AsyncLocal-based)
var resolver = new AmbientTenantResolver();
resolver.SetCurrentTenant("customer-a");

using (resolver.BeginScope("customer-b"))
{
    // Code here uses customer-b tenant
}
// Automatically reverts to customer-a

Architecture Patterns:

• ✅ Database-per-tenant - Complete isolation (highest security)
• ✅ Automatic context - AsyncLocal ambient tenant
• ✅ Scoped execution - Auto-cleanup with using blocks
• ✅ Query filtering - ApplyTenantFilter() for row-level isolation
• ✅ 61/61 tests passing - Production-ready

15. 🔥 Enhanced Unit of Work - Transaction Coordination

Coordinate multiple operations across repositories in a single transaction:

using AnubisWorks.SQLFactory.UnitOfWork;

var uowFactory = new UnitOfWorkFactory(connectionString);

using (var uow = uowFactory.Create())
{
    // Register entities
    var order = new Order { CustomerId = 1, Total = 999.99m };
    uow.RegisterNew(order);
    
    var product = await uow.Database.SingleAsync<Product>(
        "SELECT * FROM Products WHERE Id = @0", 1);
    product.Stock -= 1;
    uow.RegisterModified(product);
    
    // Create order items
    var orderItem = new OrderItem { OrderId = order.Id, ProductId = product.Id };
    uow.RegisterNew(orderItem);
    
    // Commit all changes atomically
    await uow.CommitAsync();  // Single transaction
}

// Savepoints for nested transactions
using (var uow = uowFactory.Create())
{
    var product1 = new Product { Name = "P1" };
    uow.RegisterNew(product1);
    
    var savepoint = await uow.CreateSavepointAsync("Step1");
    
    var product2 = new Product { Name = "P2" };
    uow.RegisterNew(product2);
    
    if (errorCondition)
    {
        await uow.RollbackToSavepointAsync(savepoint);  // Undo product2 only
    }
    
    await uow.CommitAsync();  // product1 saved, product2 rolled back
}

Features:

• ✅ Multi-repository coordination - Single transaction across multiple entities
• ✅ Automatic tracking - RegisterNew/Modified/Deleted with lifecycle management
• ✅ Savepoints - Nested transaction rollback points
• ✅ Factory pattern - Centralized creation with DI support
• ✅ Async throughout - Full async/await support
• ✅ 32/32 tests passing - Production-ready

16. 🔥 Enhanced AOP Events - Advanced Lifecycle Hooks

Hook into entity lifecycle for cross-cutting concerns:

using AnubisWorks.SQLFactory.Interceptors;

// Global event handlers (apply to all entities)
db.Events.BeforeInsert += (sender, args) =>
{
    if (args.Entity is IAuditable auditable)
    {
        auditable.CreatedAt = DateTime.UtcNow;
        auditable.CreatedBy = currentUserId;
    }
};

db.Events.BeforeUpdate += (sender, args) =>
{
    if (args.Entity is IAuditable auditable)
    {
        auditable.ModifiedAt = DateTime.UtcNow;
        auditable.ModifiedBy = currentUserId;
    }
};

// Entity-specific handlers
db.Events.RegisterEntityEvent<Product>(EntityEventType.BeforeDelete, (product) =>
{
    if (product.Stock > 0)
    {
        throw new InvalidOperationException("Cannot delete product with stock");
    }
});

// Async event handlers
db.Events.BeforeInsertAsync += async (sender, args) =>
{
    if (args.Entity is Product product)
    {
        var exists = await db.ExecuteScalarAsync<bool>(
            "SELECT COUNT(*) > 0 FROM Products WHERE SKU = @0", product.SKU);
        
        if (exists)
        {
            args.Cancel = true;  // Cancel insert
        }
    }
};

// Bulk operation events
db.Events.BeforeBulkInsert += (sender, args) =>
{
    Console.WriteLine($"Inserting {args.Entities.Count} entities");
};

db.Events.AfterBulkUpdate += (sender, args) =>
{
    // Clear cache after bulk updates
    db.ClearCache<Product>();
};

Supported Events:

• ✅ BeforeInsert / AfterInsert
• ✅ BeforeUpdate / AfterUpdate
• ✅ BeforeDelete / AfterDelete
• ✅ BeforeBulkInsert / AfterBulkInsert
• ✅ BeforeBulkUpdate / AfterBulkUpdate
• ✅ BeforeBulkDelete / AfterBulkDelete
• ✅ Cancellation support - Set args.Cancel = true in Before* events
• ✅ Async handlers - Full async/await support
• ✅ Property change tracking - Access modified properties
• ✅ 26/26 tests passing - Production-ready

17. 🔥 Table Sharding / Split Tables - Data Partitioning 🎉 NEW!

Partition large tables by time for massive performance gains:

using AnubisWorks.SQLFactory.Sharding;

// Mark entity for sharding
[SplitTable(SplitType.Month, TableNamePattern = "Orders_{year}_{month}")]
[Table(Name = "Orders")]
public class Order
{
    public int Id { get; set; }
    
    [SplitField]  // Routing field
    public DateTime OrderDate { get; set; }
    
    public decimal Total { get; set; }
}

// Automatic configuration
db.Sharding().AutoConfigure<Order>();

// Query current month's shard (e.g., "Orders_2025_01")
var recentOrders = db.From<Order>()
    .AsSharded(db)
    .Where("Total > @0", 100)
    .ToList();

// Query specific month
var januaryOrders = db.From<Order>()
    .AsSharded(db, new DateTime(2025, 1, 15))  // Routes to Orders_2025_01
    .ToList();

// Query date range with UNION ALL (Q4 2024)
var q4Orders = db.From<Order>()
    .AsShardedInRange(db, 
        new DateTime(2024, 10, 1),   // Start
        new DateTime(2024, 12, 31))  // End
    .ToList();
// SQL: SELECT * FROM Orders_2024_10 UNION ALL
//      SELECT * FROM Orders_2024_11 UNION ALL
//      SELECT * FROM Orders_2024_12

// Query all shards (use sparingly!)
var allOrders = db.From<Order>()
    .AsShardedAcrossAll(db)
    .Where("Status = @0", "Pending")
    .ToList();

Sharding Strategies:

• ✅ Day - Orders_2025_01_15 (high-volume apps)
• ✅ Week - Orders_2025_W03 (weekly reports)
• ✅ Month - Orders_2025_01 (most common)
• ✅ Season - Orders_2025_Q1 (seasonal businesses)
• ✅ Year - Orders_2025 (low-volume historical data)
• ✅ HalfYear - Orders_2025_H1 (bi-annual reports)
• ✅ Custom - Implement IShardingStrategy for any logic

Performance Gains: | Operation | Single Table (100M rows) | Sharded (1M rows/month) | Speedup | |-----------|-------------------------|-------------------------|---------| | Index Scan | 12.5s | 0.15s | 83x faster | | Full Scan | 45s | 0.5s | 90x faster | | Insert | 250ms | 15ms | 17x faster |

Production Use Cases:

• ✅ 100M+ row tables - Split into manageable chunks
• ✅ Time-series data - Orders, logs, events, metrics
• ✅ Historical archiving - Move old shards to cold storage
• ✅ Parallel processing - Query multiple shards concurrently
• ✅ Full documentation - docs/UserGuide_Sharding.md

Advanced Sharding Strategies (Phase 4.8):

// Hash-modulo: uniform distribution via SHA-256
var hash = new HashModuloShardingStrategy<string>(shardCount: 8, "orders_{shard}");

// Range: ordered key routing via binary search
var range = new RangeShardingStrategy<long>(new long[] { 1_000L, 5_000L }, "users_{shard}");

// Consistent hash: online shard add/remove with minimal (~1/N) remapping
var ring = new ConsistentHashShardingStrategy(virtualNodesPerShard: 150);
ring.AddShard(new ShardInfo { TableName = "t_eu", StringValue = "eu-west" });
ring.AddShard(new ShardInfo { TableName = "t_us", StringValue = "us-east" });
ring.RemoveShard("eu-west"); // affected keys reroute to us-east automatically

// Geo: data-residency compliance (GDPR — EU data stays in EU shards)
var geo = new GeoShardingStrategy(
    regionResolver: key => key.ToString()!.StartsWith("PL") ? "EU" : "US",
    regionShards: new Dictionary<string, ShardInfo> {
        ["EU"] = new ShardInfo { TableName = "Customers_EU", StringValue = "EU" },
        ["US"] = new ShardInfo { TableName = "Customers_US", StringValue = "US" }
    },
    defaultShard: new ShardInfo { TableName = "Customers_Global", StringValue = "global" });

// Lookup table: explicit per-key routing persisted in __sharding_lookup with TTL cache
var lookup = new LookupTableShardingStrategy(db, fallbackStrategy, TimeSpan.FromMinutes(5));
lookup.EnsureSchemaExists();
lookup.MapKey("tenant-acme", "shard-0"); // pin tenant to specific shard

Online Resharding — zero-downtime migration between topologies:

// Expand from 2 to 4 shards with no application downtime
var plan = new OnlineReshardPlan(
    "orders-2to4", sourceStrategy, targetStrategy, "orders", "id", sourceDb, targetDb);

var interceptor = new DualWriteInterceptor(sourceDb, targetDb);
interceptor.Attach(); // Phase 1: new writes go to both source and target

var result = await plan.ExecuteAsync(sourceDb, new ReshardOptions {
    BatchSize = 10_000,
    RequireOperatorConfirmCutover = true,
    ProgressCallback = p => Console.WriteLine($"[{p.Phase}] {p.PercentComplete:F0}%")
});

plan.ConfirmCutover(); // operator confirms: switch reads to target
interceptor.Detach();
// Source data preserved — operator archives or drops source shards manually

• Phase 1 (Expand): dual-write via DualWriteInterceptor (hooks into existing AOP).
• Phase 2 (Backfill): paginated batch copy with checkpoint resume and periodic verification.
• Phase 3 (Cutover): operator-confirmed or fully automatic; row-count drift verification.
• Source data NEVER automatically deleted (Rule 43 compliance).
• OTel metrics: resharding.rows.copied, resharding.dual_write.errors, resharding.phase.duration.
• Full operator playbook: docs/UserGuide_Resharding.md.

19. 📬 Transactional Outbox Dispatcher 🎉 NEW!

Reliable event publishing that survives database failures and multi-replica deployments.

// ── 1. Register and write an outbox message inside a transaction ─────────
using (var tx = db.Connection.BeginTransaction())
{
    db.Transaction = tx;
    db.Insert(new Order { /* ... */ });                // domain write
    db.Outbox.Add("Order.Created", JsonSerializer.Serialize(order)); // outbox write
    tx.Commit();
    db.Transaction = null;
}

// ── 2. Configure and start the dispatcher ───────────────────────────────
var sink = new HttpWebhookSink(
    httpClient: new HttpClient(),
    webhookUrl: new Uri("https://api.example.com/events"),
    signingSecret: "my-hmac-secret");   // optional — adds X-Outbox-Signature header

var options = new OutboxDispatcherOptions
{
    Sink         = sink,
    PollInterval = TimeSpan.FromSeconds(5),
    BatchSize    = 50,
    MaxRetries   = 5
};

var dispatcher = db.Outbox.CreateDispatcher(options);
await dispatcher.StartAsync(CancellationToken.None);

// ── 3. Graceful shutdown ─────────────────────────────────────────────────
await dispatcher.StopAsync(CancellationToken.None);

Available sinks:

Sink	Class	Description
HTTP Webhook	HttpWebhookSink	POST JSON with optional HMAC-SHA256 signing
Apache Kafka	KafkaSink	Confluent.Kafka 2.14.0; key = EventType, value = Payload
RabbitMQ	RabbitMqSink	RabbitMQ.Client 7.2.1; publisher confirms; topic exchange
In-Memory	InMemorySink	ConcurrentQueue-backed; for tests and development

Dead-letter handling:

Retriable failures increment RetryCount each cycle. When RetryCount >= MaxRetries the message is moved to __outbox_dead_letters and the counter is incremented. Non-retriable failures (HTTP 4xx, Kafka fatal error) move the message to DLQ immediately without any retry.

Multi-replica coordination:

Each dispatch cycle acquires an advisory lock via MigrationLockFactory. When the lock cannot be acquired (another replica is already processing) the cycle is skipped silently — no double-dispatch.

OTel metrics (wire via AddMeter(SqlFactoryMeter.MeterName)):

Instrument	Name	Description
Counter	outbox.messages.sent	Successfully dispatched messages
Counter	outbox.messages.failed	Retriable failures (not yet DLQ)
Counter	outbox.messages.dlq	Messages moved to dead-letter table
Histogram	outbox.cycle.duration	Dispatch cycle duration (ms)

Design guarantees:

• Writes inside a rolled-back transaction are never dispatched (outbox rows vanish with the rollback).
• StartAsync is idempotent — safe to call multiple times.
• StopAsync waits for the current batch to finish — no partial dispatch on graceful shutdown.
• HMAC signing secret is never logged or included in URLs (Rule 1/3a).
• All sinks are fully production-ready — no stubs.

See docs/UserGuide_Outbox.md for the complete operator guide.

---

18. 📊 OpenTelemetry Metrics + Enriched Health Endpoint 🎉 NEW!

Emit standard OTel database-client metrics and get richly-detailed health reports — with zero hard dependency on any OpenTelemetry.* package.

// ─── Wire metrics into your OTel SDK pipeline ─────────────────────────
services.AddOpenTelemetry()
    .WithMetrics(m => m.AddMeter(SqlFactoryMeter.MeterName))          // metrics
    .WithTracing(t => t.AddSource(SqlFactoryActivitySource.SourceName)); // traces (opt-in)

// ─── Without OTel SDK — direct MeterListener ──────────────────────────
using var listener = OpenTelemetryExtensions.CreateDirectListener(
    (name, value, tags) => Console.WriteLine($"{name}={value}"));

// ─── Enriched health check ─────────────────────────────────────────────
var options = new HealthCheckOptions
{
    KnownMigrations = MyApp.Migrations.All,  // optional: enables pending_migrations_count
    Logger = msg => logger.LogDebug(msg)     // optional: logs per-field enrichment failures
};

HealthCheckResult result = await db.CheckHealthAsync(options);
// result.Details contains:
//   connection_open, provider, query_ok, query_latency_ms, total_latency_ms
//   schema_version, pending_migrations_count, last_backup_at,
//   disk_free_pct (SQLite only), uptime_seconds, replication_lag_seconds,
//   migrations_lock_held_by
Console.WriteLine(result.Details["schema_version"]); // e.g. 7

Emitted metrics (OTel semantic conventions):

Instrument	Name	Tags
Histogram	db.client.operation.duration	db.system, db.operation, db.sql.table
Counter	db.client.connections.usage	state
UpDownCounter	db.client.connections.idle.max	db.system
UpDownCounter	db.client.connections.max	db.system
Counter	db.client.errors.count	error.type, db.system
Counter	db.client.bulk.rows	db.system, db.sql.table
Histogram	db.client.migration.duration	db.system, migration.version, migration.direction

Prometheus scrape example (with OpenTelemetry.Exporter.Prometheus.AspNetCore):

services.AddOpenTelemetry()
    .WithMetrics(m => m
        .AddMeter(SqlFactoryMeter.MeterName)
        .AddPrometheusExporter());
app.MapPrometheusScrapingEndpoint(); // GET /metrics

Design constraints:

• ✅ Zero OTel hard dependency — System.Diagnostics.Metrics is BCL, no NuGet package required.
• ✅ Zero overhead when idle — instruments emit nothing until a MeterListener is subscribed.
• ✅ Graceful health enrichment — each detail field is individually try/catch-guarded; one failing field never fails the whole health check.
• ✅ 28 tests passing — meter + enrichment unit tests + cross-provider compliance tests.

See docs/UserGuide_Observability.md for the full operator guide including Grafana dashboard setup.

---

20. Distributed Locks — Application-Level Coordination NEW!

Provider-aware distributed locking with TTL + heartbeat, built on top of each database engine's native advisory-lock API (or a lease table for engines without one).

// Extension method — simplest usage:
await db.WithDistributedLockAsync("invoice:42", TimeSpan.FromSeconds(30), async ct =>
{
    await ProcessInvoiceAsync(42, ct);
});

// Non-throwing variant — returns false when lock unavailable:
bool ran = await db.TryWithDistributedLockAsync("invoice:42", TimeSpan.FromSeconds(5),
    async ct => { await ProcessAsync(ct); });

// Manual handle — with TTL and heartbeat:
var locker = DistributedLockFactory.Create(db);
var ttl    = TimeSpan.FromMinutes(2);
await using var handle = await locker.AcquireAsync("long-job", TimeSpan.FromSeconds(10), ttl);
// Heartbeat fires every ~40s; handle.IsValid stays true as long as DB connection is alive.

Provider mapping:

Engine	Mechanism
PostgreSQL	pg_try_advisory_lock / pg_advisory_unlock
SQL Server	sp_getapplock / sp_releaseapplock (Session)
MySQL / MariaDB	GET_LOCK / RELEASE_LOCK
Oracle	DBMS_LOCK.REQUEST / DBMS_LOCK.RELEASE
SQLite / DuckDB	OS file lock on .distributed.lock.<name> file
ClickHouse, CockroachDB, YugabyteDB, Firebird, unknown	Lease table (__distributed_locks)

OTel metrics: lock.acquisitions.count, lock.held.duration, lock.refreshes.count.

See docs/UserGuide_DistributedLocks.md for the complete guide.

21. Change Data Capture (CDC) — Real-time Row Change Streams NEW!

Stream row-level INSERT/UPDATE/DELETE events from watched tables as an IAsyncEnumerable<ChangeEvent>. Works across all supported engines via trigger-based polling or native change-tracking APIs.

// Watch two tables — events arrive in insertion order.
await using var feed = db.CreateChangeFeed("orders", "order_items");

await foreach (var ev in feed.ReadAsync(cancellationToken))
{
    Console.WriteLine($"{ev.Operation} on {ev.TableName} at {ev.Timestamp:O}");

    if (ev.Operation == ChangeOperation.Update)
        Console.WriteLine($"  status: {ev.Before["status"]} -> {ev.After["status"]}");
}

Custom options (poll interval, start-from timestamp, logger):

var opts = new ChangeFeedOptions
{
    Tables      = new[] { "orders" },
    PollInterval = TimeSpan.FromMilliseconds(200),
    Logger       = msg => Console.Error.WriteLine($"[CDC] {msg}")
};
await using var feed = db.CreateChangeFeed(opts);

Provider mapping:

Engine	Mechanism	Before-image
SQLite	Trigger → __cdc_log	Yes
PostgreSQL / CockroachDB / YugabyteDB	Trigger → __cdc_log	Yes
MySQL / MariaDB	Trigger → __cdc_log	Yes
SQL Server	Native Change Tracking (CHANGETABLE)	Keys only
Firebird	Trigger → __cdc_log	No (NULL payload)
Oracle / ClickHouse / DuckDB	Not supported	—

Custom implementations registered via ChangeFeedFactory.Register<TDialect>(factory). PostgreSQL logical replication (WAL-based, push-mode) available in Core/Cdc/Providers/.

OTel metrics: cdc.events.consumed, cdc.poll.duration, cdc.errors.count.

See docs/UserGuide_Cdc.md for the complete guide.

---

22. Column-level AES-256-GCM Encryption NEW!

Transparent application-layer encryption for sensitive string columns using System.Security.Cryptography.AesGcm (FIPS-compliant, no external NuGet dependency).

Quick start

// 1. Annotate the entity
[Table(Name = "Employee")]
public class Employee
{
    [Column(IsPrimaryKey = true, IsDbGenerated = true)]
    public int Id { get; set; }

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

    [Column]
    [Encrypted(KeyName = "default", Mode = EncryptionMode.Randomized)]
    public string TaxId { get; set; }   // stored as AES-256-GCM ciphertext
}

// 2. Configure a key provider
var keyProvider = new InMemoryKeyProvider();
keyProvider.AddKey("default", 1, Convert.FromBase64String("<32-byte base64 key>"));
keyProvider.SetCurrentVersion("default", 1);

// 3. Register the interceptor at startup
var interceptor = new EncryptionCrudInterceptor<Employee>(keyProvider);
InterceptorManager.Register(interceptor);

// 4. Insert — TaxId is encrypted transparently
var emp = new Employee { Name = "Alice", TaxId = "123-45-6789" };
db.Table<Employee>().Add(emp);
// emp.TaxId is restored to "123-45-6789" after the INSERT

// 5. Query and decrypt
var loaded = db.Table<Employee>().Where("Id = {0}", emp.Id).First();
// loaded.TaxId == ciphertext — interceptors do not fire on SELECT
interceptor.Decrypt(loaded);
// loaded.TaxId == "123-45-6789"

Wire format

Every encrypted value is a Base64url string (no padding) encoding:

[1B version=0x01][1B mode][2B key-version BE uint16][12B nonce][16B GCM tag][N bytes ciphertext]

Total header = 32 bytes. The key version encoded in the header makes online key rotation possible without schema changes.

Encryption modes

Mode	Nonce	Use case
Randomized	12 random bytes (CSPRNG)	Default; IND-CPA — distinct ciphertexts per encryption, non-searchable
Deterministic	HMAC-SHA256(key, plaintext)[:12]	Equality search (at cost of weaker IND-CPA); index-friendly

For searchable columns use SearchableHmac.Compute(value, key) to store a deterministic HMAC fingerprint in a separate indexed column without exposing the plaintext.

Key providers

Provider	Use case
InMemoryKeyProvider	Unit tests, development
EnvironmentVariableKeyProvider	Docker / k8s secrets via env vars
MacOSKeychainKeyProvider	macOS developer machines (Rule 1)
VaultKeyProvider	HashiCorp Vault KV v2 (production)
KeyCache	TTL wrapper around any provider
Custom IKeyProvider	Any secret manager

Online key rotation

// Register new key version — existing rows are unaffected until rotation
keyProvider.AddKey("default", 2, newKeyMaterial);

var rotator = new KeyRotator(keyProvider);
long reEncrypted = await rotator.RotateAsync<Employee>(db, nameof(Employee.TaxId), newKeyVersion: 2);
// Each row is decrypted (old key from wire header) and re-encrypted (new key)
// NULL columns are skipped. Already-rotated rows (version == target) are skipped.

See docs/UserGuide_Encryption.md for the complete operator guide.

---

23. Data Masking + PII Discovery — GDPR/RODO Compliance NEW!

Dynamic attribute-driven data masking and automated PII discovery for GDPR Article 30 compliance.

Masking quick start

// 1. Annotate the entity
[Table(Name = "Customer")]
public class Customer
{
    [Column(IsPrimaryKey = true, IsDbGenerated = true)]
    public int Id { get; set; }

    [Column]
    [Pii(PiiKind.Email, Description = "Data subject email — GDPR Art. 4(1)")]
    [Mask("xx@####.###")]           // pattern: x=passthrough, #=mask, literal chars emitted as-is
    public string Email { get; set; }

    [Column]
    [Pii(PiiKind.Pesel, Description = "Polish national ID — GDPR Art. 87")]
    [Mask("xxxxx######")]
    public string Pesel { get; set; }
}

// 2. Register masking at startup
db.UseMasking<Customer>();

// 3. Query outside admin scope — values are masked automatically
var c = db.Table<Customer>().Where("Id = {0}", 1).First();
db.ApplyMasking(c);
// c.Email == "jo@####.###"   c.Pesel == "44051######"

// 4. Admin scope — plaintext
using (MaskingScope.EnterAdmin())
{
    var admin = db.Table<Customer>().Where("Id = {0}", 1).First();
    db.ApplyMasking(admin);
    // admin.Email == "john@example.com"  (unmasked)
}

Mask pattern grammar

Character	Meaning
x	Passthrough — emit original character at this position
#	Mask — replace with MaskChar (default '#')
any other	Literal — emit this character from the pattern regardless of original value

Length mismatch between value and pattern → all characters masked with MaskChar.

PII Discovery Scanner

// Scan the whole database for PII — no plaintext stored in the report
var report = await db.DiscoverPiiAsync(sampleSize: 200);

// Summary
Console.WriteLine(report.ToMarkdown());   // GDPR Article 30 Markdown table
Console.WriteLine(report.ToJson());       // Machine-readable JSON

// Attribute-declared findings = 100% confidence
// Column name + value pattern match = 90% confidence
// Single signal only = 70% confidence

PiiKind taxonomy (GDPR Art. 4 + NIST SP 800-122)

Email, Phone, Pesel, Ssn, Iban, CreditCard, Address, FullName, DateOfBirth, IpAddress, GpsCoordinate, NationalId, PassportNumber, DriverLicense, MedicalRecord, GeneticInfo, BiometricInfo, Salary, BankAccount, TaxId, Custom

Validators

Method	Description
PiiClassifier.IsValidPesel(string)	PESEL checksum (weighted mod-10)
PiiClassifier.IsValidLuhn(string)	Luhn algorithm for credit card numbers
PiiClassifier.IsValidIban(string)	IBAN MOD-97 check (ISO 13616)

Compliance properties

• Sample values in the report are always masked — plaintext PII is never stored or emitted (Rule 1/3a).
• Scanner is read-only — source data is not modified.
• MaskingScope uses AsyncLocal<T> — scopes are isolated per async call chain.
• Admin elevation: MaskingScope.EnterAdmin() → IDisposable scope; auto-resets on Dispose().
• User scope: MaskingScope.EnterUserScope(userId, roles) for auditable role-aware policies.
• OTel metrics: masking.applied.count, pii.discovery.runs.count, pii.discovery.findings.count.

See docs/UserGuide_Masking.md and docs/UserGuide_PiiDiscovery.md for the full operator guide.

---

26. Native Temporal Tables NEW!

Unified AsOf / Between / History fluent API that maps to native system-versioned tables on SQL Server and MariaDB, falls back to sys_period polyfill on PostgreSQL, and uses ValidFrom/ValidTo WHERE-clause polyfill on SQLite. Trigger-based manual history via ManualHistoryHelper for engines without native support.

using AnubisWorks.SQLFactory.Temporal;

// Point-in-time snapshot (SQL Server: FOR SYSTEM_TIME AS OF)
var ordersAtMidnight = db.Table<Order>()
    .AsOf(new DateTimeOffset(2024, 3, 15, 0, 0, 0, TimeSpan.Zero));

// Overlapping time range
var ordersInQ1 = db.Table<Order>()
    .Between(
        new DateTimeOffset(2024, 1, 1, 0, 0, 0, TimeSpan.Zero),
        new DateTimeOffset(2024, 3, 31, 23, 59, 59, TimeSpan.Zero));

// All historical versions
var allVersions = db.Table<Order>().History();

Engine	Native support	Polyfill
SQL Server 2016+	FOR SYSTEM_TIME AS OF / BETWEEN	—
MariaDB 10.3+	FOR SYSTEM_TIME AS OF TIMESTAMP / BETWEEN TIMESTAMP	—
PostgreSQL + temporal_tables	—	sys_period @> / && WHERE-clause
SQLite	—	ValidFrom / ValidTo WHERE-clause
MySQL / Oracle / DuckDB / ClickHouse / Firebird	—	NotSupportedException + ManualHistoryHelper
CockroachDB / YugabyteDB	Inherits PostgreSQL	PostgreSQL polyfill

DDL generation for system-versioned tables:

ITemporalDialect temporal = SqlServerDialect.Instance;
string ddl = temporal.CreateSystemVersionedTableSql(tableDefinition);
db.Execute(ddl);

Manual trigger-based history for SQLite / MySQL:

db.Execute(ManualHistoryHelper.CreateHistoryTableSql("Orders", columns));
// Execute each trigger individually (split on standalone semicolons)
foreach (string stmt in Regex.Split(
    ManualHistoryHelper.CreateSqliteTriggersSql("Orders", columnNames),
    @"(?m)^\s*;\s*$"))
{
    string t = stmt.Trim();
    if (!string.IsNullOrEmpty(t)) db.Execute(t);
}

See docs/UserGuide_TemporalTables.md for the full guide including DDL examples, [Temporal] attribute configuration, ManualHistoryHelper setup, and OTel metrics.

---

28. REST CRUD Scaffold NEW!

Automatic ASP.NET Core REST API generation from your SqlFactory entities — OData v4 filtering, RFC 5988 pagination, RFC 7396 PATCH, OpenAPI 3.0.3 spec, and OTel metrics. Zero boilerplate: decorate the entity, register in Program.cs, done.

Quickstart

// 1. Annotate entity
[ExposeAsApi(Route = "products", DefaultPageSize = 50, MaxPageSize = 500)]
[Table(Name = "Products")]
public class Product
{
    [Column(IsPrimaryKey = true, IsDbGenerated = true)] public int Id { get; set; }
    [Column] public string Name { get; set; } = string.Empty;
    [Column] public decimal Price { get; set; }
}

// 2. Program.cs
builder.Services.AddSqlFactoryRest(db, options =>
{
    options.AssembliesToScan.Add(typeof(Product).Assembly);
    options.EnableOpenApiEndpoint = true;    // default
    options.OpenApiPath = "/openapi.json";   // default
});
// ...
app.MapSqlFactoryRest();

Generated endpoints (per entity)

Method	Path	Description
GET	/products	List with OData $filter, $orderby, $top, $skip, $select
POST	/products	Create — returns 201 with Location header
GET	/products/{id}	Fetch by primary key
PUT	/products/{id}	Full replace
PATCH	/products/{id}	RFC 7396 JSON Merge Patch (partial update)
DELETE	/products/{id}	Delete — returns 204

OData filtering

GET /products?$filter=Price gt 100 and contains(Name,'Lap')&$orderby=Price desc&$top=20&$skip=0

Supported: eq, ne, lt, le, gt, ge, and, or, not, contains, startswith, endswith, null literal, true/false literals. All values parameterized — SQL injection impossible.

RFC 5988 Link-header pagination

Link: </products?$top=20&$skip=0>; rel="first",
      </products?$top=20&$skip=20>; rel="next"

first, prev, next, last rels generated automatically. last only emitted when total count is known.

OpenAPI 3.0.3

GET /openapi.json

Returns a valid OpenAPI 3.0.3 document with all entity paths, CRUD operations, OData query parameters, schema components, and standard response codes (200/201/204/400/404).

ExposeAsApiAttribute reference

Property	Default	Description
Route	entity name (lowercase)	URL path prefix
AuthorizePolicy	null	ASP.NET Core authorization policy name
EnableCreate	true	POST endpoint
EnableUpdate	true	PUT + PATCH endpoints
EnableDelete	true	DELETE endpoint
EnableList	true	Collection GET endpoint
EnableGetById	true	Item GET endpoint
DefaultPageSize	100	Default $top
MaxPageSize	1000	Maximum $top cap

OTel metrics

Metric	Tags
rest.requests.count	entity, operation, outcome
rest.request.duration	(histogram, milliseconds)
rest.errors.count	entity, operation, error.type

See docs/UserGuide_RestApi.md for the complete guide including authorization, multi-entity OpenAPI, testing patterns, and provider notes.

---

27. GraphQL Endpoint via HotChocolate NEW!

Production-ready GraphQL API layer built on HotChocolate v16 (MIT). Exposes any set of SqlFactory entities as a typed GraphQL schema with queries, mutations, subscriptions, field-level authorization, and N+1-safe batch loading — all driven by SqlFactory raw SQL (zero Dapper/EF).

Quickstart

// Program.cs (ASP.NET Core)
var options = new SqlFactoryGraphQLOptions
{
    ExposedEntities = { typeof(Order), typeof(Product) },
    DefaultPageSize = 100,
    MaxPageSize = 500,
    EnableMutations = true,
    EnableSubscriptions = true
};

builder.Services.AddSqlFactoryGraphQL(db, options);
// ...
app.MapSqlFactoryGraphQL("/graphql");

Query examples

# List with filter, sort, and pagination
query {
  orders(where: "\"Status\" = 'Pending'", orderBy: "\"CreatedAt\" DESC", first: 20) {
    Id
    Status
    TotalAmount
  }
}

# Fetch by primary key
query {
  order(id: "42") {
    Id
    Status
    TotalAmount
  }
}

Mutation examples

mutation {
  createOrder(input: { Status: "Pending", TotalAmount: "99.99" }) {
    Id
    Status
  }
}

mutation {
  updateOrder(id: "42", input: { Status: "Shipped" }) {
    Id
    Status
  }
}

mutation {
  deleteOrder(id: "42")
}

Field-level authorization

public class Order
{
    [Column(IsPrimaryKey = true)] public int Id { get; set; }
    [Column] public string Status { get; set; }

    [FieldAuth("finance")]         // omitted unless caller has role "finance"
    [Column] public decimal TotalAmount { get; set; }

    [FieldAuth("admin")]           // omitted unless caller has role "admin"
    [Column] public string InternalNotes { get; set; }
}

N+1 prevention

RelationResolver.LoadBatchAsync<TKey> accumulates all FK keys during a GraphQL execution and issues a single IN (...) query per related type — not one query per parent row.

var loader = new RelationResolver.BatchLoader<int, IReadOnlyDictionary<string, object?>>(
    async (keys, ct) => await relationResolver.LoadBatchAsync<int>(typeof(Product), keys, ct));

// All calls to LoadAsync accumulate before dispatching
var product = await loader.LoadAsync(order.ProductId);

OTel metrics

Metric	Tags
graphql.queries.count	operation, outcome
graphql.mutations.count	operation, outcome
graphql.subscription.events	operation
graphql.errors.count	error.type

See docs/UserGuide_GraphQL.md for the complete guide including subscription setup, cursor-based pagination, schema SDL generation, and provider-specific notes.

---

25. Hierarchical CTE Queries NEW!

Fluent tree-traversal API generating dialect-aware WITH [RECURSIVE] SQL for Descendants, Ancestors, Siblings, and Subtree operations against self-referencing tables.

using AnubisWorks.SQLFactory.Cte;

IHierarchyQuery<Category> hier = db.HierarchyQuery<Category>(
    c => c.ParentId,
    new HierarchyQueryOptions { TableName = "Categories" });

// All descendants (up to 10 levels by default)
IReadOnlyList<Category> descendants = hier.Descendants(rootId: 1);

// Only direct children
IReadOnlyList<Category> children = hier.Descendants(rootId: 1, maxDepth: 1);

// Parent chain up to root
IReadOnlyList<Category> ancestors = hier.Ancestors(nodeId: 42);

// Nodes sharing the same parent
IReadOnlyList<Category> siblings = hier.Siblings(nodeId: 42);

// Node itself + all descendants
IReadOnlyList<Category> subtree = hier.Subtree(nodeId: 42);

Provider	WITH keyword	Native CYCLE clause	Cycle detection
SQLite	WITH RECURSIVE	No	path-tracking (INSTR)
PostgreSQL 14+	WITH RECURSIVE	Yes	native CYCLE clause
MySQL 8+ / MariaDB	WITH RECURSIVE	No	path-tracking (LOCATE)
SQL Server	WITH	No	path-tracking (CHARINDEX)
Oracle 11g2+	WITH	No	path-tracking (INSTR)
DuckDB	WITH RECURSIVE	No	path-tracking (INSTR)
Firebird 2.5+	WITH RECURSIVE	No	path-tracking (POSITION)
CockroachDB / YugabyteDB	WITH RECURSIVE	Yes	native CYCLE clause
ClickHouse	Not supported	N/A	NotSupportedException

See docs/UserGuide_HierarchyQueries.md for the full guide including cycle detection strategies, custom table/column names, and per-dialect SQL examples.

---

24. Cross-Provider JSON Path Queries NEW!

Unified WhereJson* API that translates standard JSONPath to dialect-correct native SQL across all 11 supported database providers.

using AnubisWorks.SQLFactory.JsonQuery;

// Filter by a JSON scalar value
var premiumUsers = db.From<User>("Users")
    .WhereJsonScalar(u => u.Metadata, "$.tier", "premium")
    .ToList();

// Filter to rows where a JSON key exists
var usersWithAddress = db.From<User>("Users")
    .WhereJsonPathExists(u => u.Metadata, "$.address.city")
    .ToList();

// Filter by JSON array length (use "$" when the column IS the array)
var multiTagUsers = db.From<User>("Users")
    .WhereJsonArrayLength(u => u.Tags, "$", ">=", 2)
    .ToList();

// Containment check
var admins = db.From<User>("Users")
    .WhereJsonContains(u => u.Roles, "admin")
    .ToList();

// Chain with standard Where
var premiumAlice = db.From<User>("Users")
    .WhereJsonScalar(u => u.Metadata, "$.tier", "premium")
    .Where("Name = {0}", "Alice")
    .ToList();

Provider	JsonExtractScalar("col","$.tier")
SQLite	json_extract(col,'$.tier')
PostgreSQL / CockroachDB / YugabyteDB	col->>'tier'
MySQL / MariaDB	JSON_UNQUOTE(JSON_EXTRACT(col,'$.tier'))
SQL Server	JSON_VALUE(col,'$.tier')
Oracle	JSON_VALUE(col,'$.tier')
DuckDB	json_extract_string(col,'$.tier')
ClickHouse	JSONExtractString(col,'tier')
Firebird	NotSupportedException (Firebird 3.x has no JSON path API)

See docs/UserGuide_JsonQuery.md for the full operator guide including Firebird workarounds.

---

Quick Start

Installation

dotnet add package SQLFactory

Basic Usage

using AnubisWorks.SQLFactory;

// Connect to database
var db = new Database("YourConnectionString");

// Create a table accessor
var customers = db.GetTable<Customer>();

// Insert
var newCustomer = new Customer 
{ 
    Name = "John Doe", 
    Email = "john@example.com" 
};
customers.Insert(newCustomer);

// Query
var customer = customers.FirstOrDefault(c => c.Id == 1);

// Update
customer.Email = "newemail@example.com";
customers.Update(customer);

// Delete
customers.Delete(customer);

---

🛠️ CLI Tool — sqlfactory

The sqlfactory dotnet global tool provides command-line parity with EF Core CLI, Flyway, and DbUp.

# Install from NuGet
dotnet tool install -g SQLFactory.Migrate.Cli

# Or install from a local package
dotnet tool install -g SQLFactory.Migrate.Cli --add-source ./Tools/SQLFactory.Migrate.Cli/bin/Release

Commands:

Command	Description
sqlfactory migrate up	Apply pending migrations. Options: --target <N>, --dry-run
sqlfactory migrate down	Roll back to version N. Options: --target <N> (required), --confirm
sqlfactory migrate info	Show current version and applied/pending migration table
sqlfactory migrate preview	Print pending SQL to stdout. Options: --all
sqlfactory migrate baseline	Record existing schema at version N. Options: --version <N>, --description, --confirm
sqlfactory migrate cross-engine	Stream data from source to target DB across engine types

Global options: --connection/-c, --provider/-p, --connection-from-keychain (macOS Keychain), --assembly/-a, --quiet/-q, --verbose/-v, --no-color, --lock-timeout.

See Tools/SQLFactory.Migrate.Cli/README.md for full documentation.

---

📦 Installation

NuGet Package Manager

Install-Package SQLFactory

.NET CLI

dotnet add package SQLFactory

Package Reference

<PackageReference Include="SQLFactory" Version="1.0.0" />

---

📚 Documentation

Comprehensive documentation is available in the docs directory:

• Getting Started
• REST CRUD Scaffold - OData filtering, RFC 5988 pagination, OpenAPI 3.0.3
• Read/Write Splitting - Horizontal scaling guide
• Lazy Loading - Navigation properties
• Soft Delete - Data retention patterns
• Optimistic Concurrency - Conflict handling
• API Reference (XML documentation included in package)

📖 Comprehensive Examples

The examples/ directory contains 12 complete, runnable projects (3,870+ lines) demonstrating ALL SQLFactory features:

🟢 Beginner Examples (Start Here)

1. BasicCRUD - INSERT, SELECT, UPDATE, DELETE, Transactions
2. AdvancedQuerying - SqlBuilder, JOINs, GROUP BY, Pagination, Subqueries
3. EagerLoading - Include(), ThenInclude(), N+1 prevention

🟡 Intermediate Examples

4. GlobalFilters - Multi-tenancy, Soft delete filtering
5. ChangeTracking - DetectChanges(), SaveChanges(), Unit of Work
6. SoftDelete - ISoftDeletable, Restore(), data retention
7. Caching - Query result caching, LRU eviction

🔴 Advanced Examples

8. ReadWriteSplitting - Master-replica, load balancing, sticky sessions
9. BulkOperations - BulkInsert/Update/Delete with benchmarks
10. LazyLoading - Castle.DynamicProxy, navigation properties
11. FullStackApp - Complete e-commerce application (600 LOC)

🛠️ Code Generation

12. CodeGeneration - CLI tool for scaffolding entities and contexts

Quick Start:

cd examples/BasicCRUD
dotnet run

Run All Examples:

cd examples
./run-all-examples.sh

Each example is standalone, uses SQLite (no setup), and includes comprehensive inline documentation.

---

💡 Examples

POCO Mapping

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public string? Email { get; set; }
}

var db = new Database(connectionString);
var customers = db.GetTable<Customer>();
var allCustomers = customers.ToList();

Annotated Models

[Table("Customers")]
public class Customer
{
    [Column(IsPrimaryKey = true, IsDbGenerated = true)]
    public int CustomerId { get; set; }
    
    [Column("CustomerName")]
    public string Name { get; set; } = string.Empty;
}

SQL Builder

var query = new SqlBuilder()
    .Select("c.Id", "c.Name", "o.OrderDate")
    .From("Customers c")
    .Join("Orders o", "c.Id = o.CustomerId")
    .Where("c.Active = @active")
    .OrderBy("o.OrderDate DESC");

var results = db.Query<CustomerOrder>(
    query.ToString(), 
    new { active = true }
);

Dynamic Queries

var sqlSet = new SqlSet<Product>(db);
var products = sqlSet
    .Where(p => p.Category == "Electronics")
    .Where(p => p.Price < 1000)
    .OrderBy(p => p.Name)
    .Skip(0)
    .Take(10)
    .ToList();

Eager Loading 🆕

using AnubisWorks.SQLFactory.Include;

// Load single reference navigation property
var products = db.Table<Product>()
    .Include(p => p.Category)
    .ToList();
// Each product.Category is loaded (eliminates N+1 queries)

// Load collection navigation property
var categories = db.Table<Category>()
    .Include(c => c.Products)
    .ToList();
// Each category.Products contains all related products

// Multi-level nesting with ThenInclude
var products = db.Table<Product>()
    .Include(p => p.Category)
    .ThenInclude<Product, Category, Region>(c => c.Region)
    .ThenInclude<Product, Region, Country>(r => r.Country)
    .ToList();
// Product → Category → Region → Country all loaded

// Async eager loading
var products = await db.Table<Product>()
    .Include(p => p.Category)
    .ThenInclude<Product, Category, Category>(c => c.Parent)
    .ToListAsync();

// Combine with queries
var expensiveProducts = db.Table<Product>()
    .Where("Price > 1000")
    .OrderBy("Name")
    .Include(p => p.Category)
    .Take(10)
    .ToList();

Global Query Filters 🆕

// Register a soft delete filter (applies globally)
GlobalFilterManager.Register(new SoftDeleteFilter<Product>());

// Define your filter
public class SoftDeleteFilter<TEntity> : IGlobalFilter<TEntity>
    where TEntity : ISoftDeletable
{
    public string FilterName => "SoftDelete";
    public bool IsEnabled => true;
    
    public Expression<Func<TEntity, bool>> GetFilter() {
        return entity => !entity.IsDeleted;  // Automatically added to WHERE clause
    }
}

// All queries automatically exclude deleted records
var products = db.From<Product>().ToList();
// SELECT * FROM Products WHERE IsDeleted = 0

// Admin view - bypass filters
var allProducts = db.From<Product>()
    .IgnoreQueryFilters()
    .ToList();
// SELECT * FROM Products (includes deleted)

// Soft delete operation
db.Table<Product>().Extension("SoftDelete").SoftDelete(product);
// UPDATE Products SET IsDeleted = 1 WHERE ProductID = @id

Optimistic Concurrency 🆕

public class Product {
    public int ProductID { get; set; }
    public string ProductName { get; set; }
    
    [RowVersion]
    public byte[] RowVersion { get; set; }  // Auto-checked on UPDATE
}

// Update with concurrency check
try {
    product.ProductName = "Updated Name";
    db.Table<Product>().Update(product);
    // UPDATE Products SET ... WHERE ProductID = @id AND RowVersion = @rowVersion
} catch (DbConcurrencyException ex) {
    // Another user modified the record
    Console.WriteLine("Conflict! Reload and merge changes.");
}

Query Result Caching 🆕

// Cache for 5 minutes
var products = db.From<Product>()
    .Where(p => p.CategoryID == 1)
    .Cacheable(TimeSpan.FromMinutes(5))
    .ToList();
// First call: hits database
// Subsequent calls within 5min: returns cached result

// Clear cache for entity type
db.Cache.Clear<Product>();

// Clear all cache
db.Cache.ClearAll();

CRUD Interceptors (AOP) 🆕

// Define an audit interceptor
public class AuditInterceptor<TEntity> : ICrudInterceptor<TEntity>
    where TEntity : IAuditable
{
    public string InterceptorName => "Audit";
    public int Order => 0;
    
    public void OnInserting(TEntity entity, CrudContext context) {
        entity.CreatedAt = DateTime.UtcNow;
        entity.CreatedBy = context.CurrentUserId;
    }
    
    public void OnUpdating(TEntity entity, CrudContext context) {
        entity.UpdatedAt = DateTime.UtcNow;
        entity.UpdatedBy = context.CurrentUserId;
    }
}

// Register interceptor
InterceptorManager.Register(new AuditInterceptor<Product>());

// All CRUD operations automatically invoke interceptors
db.Table<Product>().Add(product);
// CreatedAt and CreatedBy set automatically before INSERT

Read/Write Splitting 🆕 🔥

using SQLFactory.ReadWriteSplitting;

// Configure master-replica setup
var config = new ReadWriteConfiguration
{
    PrimaryConnectionString = "Server=primary;Database=MyDb;...",
    ReadReplicaConnectionStrings = new[]
    {
        "Server=replica1;Database=MyDb;...",
        "Server=replica2;Database=MyDb;..."
    },
    LoadBalancingStrategy = LoadBalancingStrategy.RoundRobin,
    UseStickySessions = true,
    StickSessionWindowSeconds = 5
};

using (var db = new Database())
{
    // Enable Read/Write splitting
    db.WithReadWriteSplitting(config);
    
    // Write queries automatically go to PRIMARY
    db.Execute("INSERT INTO Users (Name) VALUES (@0)", "John");
    
    // Read queries automatically go to REPLICAS (round-robin)
    var users = db.From<User>("SELECT * FROM Users").ToList();
    
    // Force critical read to PRIMARY (most recent data)
    var account = db.UsePrimary()
                   .From<Account>("SELECT * FROM Accounts WHERE Id = @0", 123)
                   .FirstOrDefault();
    
    // Reset sticky session after transaction
    db.ResetStickySession();
}

// Learn more: docs/ReadWriteSplitting.md

---

🔨 Building

Prerequisites

• .NET 10 SDK or later
• Linux, macOS, or Windows

Build from Source

# Clone the repository
git clone https://github.com/anubisworks/sqlfactory.git
cd sqlfactory

# Restore dependencies
dotnet restore

# Build the solution
dotnet build --configuration Release

# Run tests
dotnet test --configuration Release --collect:"XPlat Code Coverage"

Using Build Scripts

# Full build with versioning
./scripts_dotnet/_buildDotnetSolution.sh

# Build and run tests
./scripts_dotnet/_localTest.sh

# Create NuGet package
dotnet pack --configuration Release

---

🧪 Testing

SQLFactory includes comprehensive unit and integration tests using NUnit:

# Run all tests
dotnet test

# Run with coverage
dotnet test --collect:"XPlat Code Coverage"

# Run specific test category
dotnet test --filter "Category=Integration"

Test Coverage (Production Ready)

• Overall Coverage: 58.83% (steadily improving)
• Core Coverage: 60.21% (main library components)
• Test Count: 743 passing tests (0 failures, 0 skipped)
• Framework: NUnit 3.14 with async test support
• Mocking: Moq 4.20 for unit tests
• Databases: SQLite (in-memory) and SQL Server LocalDB

Coverage by Component

Component	Coverage	Tests	Status
GlobalFilterManager	97.5%	18	✅ Production
InterceptorManager	92.5%	25	✅ Production
QueryCache	80.8%	17	✅ Production
SqlBuilder	66.0%	27	✅ Stable
SQLFactory Extensions	63.1%	33	✅ Stable
Query Filters Integration	-	6	✅ Production
Optimistic Concurrency	-	3	✅ Production
Soft Delete	-	6	✅ Production
Include/ThenInclude	-	15	✅ Production
Pagination	-	8	✅ Production

Coverage Analysis Tools

SQLFactory includes Python-based coverage analysis tools to help improve test coverage:

# Run basic coverage analysis
python Tools/coverage-analysis/analyze_coverage.py

# Focus on Core library only
python Tools/coverage-analysis/analyze_core_coverage.py

# Identify high-priority targets (< 70% coverage)
python Tools/coverage-analysis/priority_targets.py

# Interactive menu with all options
./Tools/coverage-analysis/run-analysis.sh

Available Tools:

• analyze_coverage.py - Overall coverage with per-class breakdown
• analyze_core_coverage.py - Core library specific analysis
• priority_targets.py - Classes needing test coverage (< 70%)
• check_latest_coverage.py - Quick summary of latest results
• compare_coverage.py - Compare two coverage reports
• run-analysis.sh - Interactive menu for all tools

See Tools/README.md for detailed usage instructions.

---

🛠️ Development

Project Structure

sqlfactory/
├── Core/                       # Main library
│   ├── SQLFactory.cs          # Core database class
│   ├── SqlBuilder.cs          # Query builder
│   ├── SqlSet.cs              # LINQ-like operations
│   ├── Mapper.cs              # Object mapping
│   └── Metadata/              # Mapping metadata
├── Tests/                      # Unit tests
├── examples/                   # 12 comprehensive example projects
├── samples/                    # Sample code
├── benchmarks/                 # Performance benchmarks
├── DatabaseRealExample/        # Example application
├── RealLifeExample/           # Real-world scenario
├── ObjectDumper/              # Utility tool
├── Tools/                     # Development tools
│   ├── CodeGenerator/         # SQLFactory-CodeGen CLI
│   ├── coverage-analysis/     # Coverage analysis scripts
│   └── code-tools/            # Code modification utilities
├── docs/                      # Documentation
└── scripts_dotnet/            # Build scripts

Code Quality

• Nullable reference types enforced
• Code analyzers enabled (Microsoft + StyleCop)
• XML documentation required
• EditorConfig for consistent styling
• Continuous integration ready

---

🤝 Contributing

Contributions are welcome! Please follow these guidelines:

1. Fork the repository
2. Create a feature branch (git checkout -b feature/amazing-feature)
3. Commit your changes (git commit -m 'Add amazing feature')
4. Push to the branch (git push origin feature/amazing-feature)
5. Open a Pull Request

Development Guidelines

• Follow existing code style
• Add unit tests for new features
• Update documentation as needed
• Ensure all tests pass
• Maintain or improve code coverage

---

📄 License

This project is licensed under the GNU Lesser General Public License v3.0 or later (LGPL-3.0-or-later).

See LICENSE file for details.

What this means:

• ✅ Use in commercial applications
• ✅ Modify the library
• ✅ Distribute modifications
• ✅ Private use
• ⚠️ Disclose source if you modify SQLFactory itself
• ⚠️ Use same license for SQLFactory modifications
• ✅ Your application can use any license

---

📚 Complete Documentation Index

User Guides

• Walkthrough - Complete getting-started walkthrough
• Snowflake ID Generator - Distributed unique ID generation
• Multi-Tenant Support - Database-per-tenant isolation
• Unit of Work - Transaction coordination
• AOP Events - Lifecycle event hooks
• Table Sharding - Horizontal partitioning (date, hash-modulo, range, consistent-hash, geo, lookup-table strategies)
• Online Resharding - Zero-downtime migration between sharding topologies (expand/backfill/cutover with operator gate)
• CodeFirst - Schema management from entities
• DbFirst - Code generation from database
• Storageable - Smart collection diff/merge
• JsonQuery - Dynamic SQL from JSON specs
• Hierarchy Queries - WITH RECURSIVE tree traversal: Descendants, Ancestors, Siblings, Subtree; cycle detection; 11 providers
• Online Migrations - Zero-downtime expand-contract column add/rename
• Transactional Outbox Dispatcher - Reliable event publishing: HTTP, Kafka, RabbitMQ, in-memory sinks; DLQ; OTel metrics
• Observability - OTel metrics, enriched health endpoint
• Temporal Tables - Point-in-time snapshots, time-range queries, manual trigger-based history
• GraphQL Endpoint - HotChocolate v16 integration: queries, mutations, subscriptions, field-level auth, N+1 batch loading

Feature Documentation

• Eager Loading & Split Query - Read/Write splitting
• Lazy Loading - On-demand navigation loading
• Optimistic Concurrency - Version-based conflict handling
• Soft Delete - Logical deletion pattern
• Upsert - Insert-or-update operations
• Dynamic Expression Builder - Fluent predicate construction
• Provider Optimizations - SqlBulkCopy, PostgreSQL COPY, Oracle ArrayBindCount
• Oracle Provider - Oracle Database 12c+ setup, DBMS_LOCK advisory locks, expdp backup
• DuckDB Provider - DuckDB 1.x setup, in-process OLAP, Appender bulk-insert, Parquet export backup
• ClickHouse Provider - ClickHouse 21.12+ setup, MergeTree engine, Nullable(T) types, native backup
• Firebird Provider - Firebird 3.0+ setup, gbak backup, EXECUTE BLOCK idempotent DDL, RETURNING clause
• CockroachDB Provider - CockroachDB v24+ setup, pg_dump export backup, COPY FROM STDIN bulk-load, AS OF SYSTEM TIME / follower reads
• YugabyteDB Provider - YugabyteDB YSQL setup, ysql_dump backup, COPY FROM STDIN bulk-load, colocation hints

Performance & Benchmarks

• Benchmark Results - Performance measurements
• Performance Characteristics - Feature-level performance analysis
• Test Coverage Guide - Coverage analysis and tools

Release & Production

• Production Release v2.0.0 - v2.0.0 release notes
• Production Readiness - Production checklist

Architecture & Planning

• Notes - Development notes

---

🆕 v3.0 — 30 New Enterprise Features

Performance & AOT

• Compiled Expression Mapper - Zero-reflection mapping via Expression trees
• AOT-Compatible Mapping - Pre-registered factories for native AOT
• Compiled Queries - Skip SQL generation on re-execution
• IAsyncEnumerable Streaming - Stream results without full materialization
• Memory-Pooled Mapping - ArrayPool-backed result collections

Query Engine

• Full LINQ Provider - IQueryable<T> with expression-to-SQL translation
• Cross-Database Query - Join across different database instances
• Temporal Table Queries - FOR SYSTEM_TIME AS OF / BETWEEN
• Full-Text Search - Cross-provider: CONTAINS, to_tsvector, FTS5
• Window Functions - ROW_NUMBER, RANK, LAG, LEAD, SUM OVER
• CTE / Recursive Queries - Fluent Common Table Expressions
• SQL Injection Analyzer - Runtime heuristic detection (12 patterns)

Enterprise & Cloud-Native

• Connection Resilience - Retry with exponential backoff + circuit breaker
• OpenTelemetry - ActivitySource-based distributed tracing
• Schema Migration Engine - Forward-only, transactional migrations
• Database Health Checks - Connection test + latency reporting
• Distributed Saga - Multi-step orchestration with compensation
• Event Outbox - Reliable event publishing within transactions
• Audit Trail - Automatic change history with JSON snapshots
• Row-Level Security - Declarative, enforced query-level filters
• Operational Pack - AuditShipper (JSONL/Kafka/RabbitMQ/ClickHouse), ReadOnlyMode, IsolationLevelEnforcer, SlowQueryArchiver

Developer Experience

• CLI Helpers - Schema inspection, diff generation
• Query Profiler / EXPLAIN - Execution plan + timing capture
• Seed Data API - Idempotent seeding from code, JSON, CSV
• Smart Query Warnings - SELECT *, cartesian join, N+1 detection
• Snapshot Testing - Deterministic JSON snapshots with diff
• Interactive REPL - .tables, .schema, SQL execution

Cutting-Edge

• GraphQL Endpoint - HotChocolate v16 runtime: queries, mutations, subscriptions, field-level auth, N+1 batch loading
• GraphQL Schema Generator - Auto-generate SDL from entities
• Spatial Data - Haversine, PostGIS, SQL Server STDistance
• Vector/Embedding Column - pgvector + cosine similarity
• Plugin Architecture - ISqlFactoryPlugin with lifecycle management
• WASM Support - SQLite WASM configuration helpers
• Schema Compare - Snapshot, diff, migration SQL generation

---

📞 Support

• 🐛 Issue Tracker
• 📧 Email: kontakt@anubisworks.net
• 📖 Documentation

---

🙏 Acknowledgments

• Built by Michael Agata / AnubisWorks
• Inspired by Dapper, LINQ to SQL, and Entity Framework

---

Made by AnubisWorks