Noundry.Tuxedo.Exceptions 0.10.0

Noundry.Tuxedo

A performant, modern drop-in replacement for Dapper with EF Core-like features but no foot-guns.

Noundry.Tuxedo unifies Dapper and Dapper.Contrib into a single, modern package for .NET 8, 9, and 10. It provides high-performance ADO.NET mapping, CRUD helpers, migrations, auditing, and more—all working seamlessly with SQL Server, PostgreSQL, MySQL, and SQLite.

For AI/LLM Code Generation: See LLM.md for comprehensive code generation guidance with detailed examples for all libraries and databases.

---

Why Tuxedo?

Feature	Dapper	EF Core	Tuxedo
Performance	Excellent	Good	Excellent
Learning Curve	Low	High	Low
CRUD Helpers	Contrib (separate)	Built-in	Built-in
Migrations	None	Built-in	Bowtie
Query Builder	None	LINQ	Fluent API
Bulk Operations	None	EF.BulkExtensions	Built-in
Audit Logging	None	Interceptors	Auditor
Exception Types	Generic	Specific	Specific
Test Data Gen	None	None	Cufflink
Foot-guns	Few	Many	None

Migration from Dapper is a one-line change:

// Before
using Dapper;
using Dapper.Contrib.Extensions;

// After
using Noundry.Tuxedo;
using Noundry.Tuxedo.Contrib;

---

Noundry.Tuxedo.Bowtie - Database migrations and schema synchronization. See Bowtie documentation.

Noundry.Tuxedo.Cufflink - Fake data generation with Bogus. See Cufflink documentation.

Features

Noundry.Tuxedo ORM Core

• High performance query/execute APIs (Query, Execute, async variants)
• Dynamic Query Builder: Fluent LINQ-style query building with expression support
• Contrib‑style CRUD: Get/GetAll, Insert, Update, Delete (+ async)
• Partial Updates: Enhanced Update/UpdateAsync methods support selective column updates
• Bulk Operations: High-performance bulk insert, update, delete, and merge operations
• Resiliency: Built-in retry policies and circuit breakers with Polly integration
• Exception Translation: Strongly-typed database exceptions for precise error handling
• Audit Logging: Automatic CRUD change tracking with configurable user providers
• Diagnostics & Monitoring: Comprehensive event tracking, health checks, and performance analysis
• Attribute mapping: [Table], [Key], [ExplicitKey], [Computed], [Column]
• Works with any ADO.NET provider (SqlClient, Npgsql, MySqlConnector, Sqlite)
• Optional DI helpers for registering a connection, including in‑memory SQLite

🎭 Noundry.Tuxedo.Bowtie Database Migrations

• Schema Synchronization: Automatic database table creation and updates from POCO models
• Multi-Database DDL: Generate SQL for SQL Server, PostgreSQL, MySQL, SQLite
• Advanced Indexing: GIN, Hash, Clustered, ColumnStore, FullText, Spatial indexes
• Extended Attributes: [Index], [Unique], [ForeignKey], [CheckConstraint], [Column]
• CLI Tool: Command-line interface for build automation (bowtie sync, generate, validate)
• ASP.NET Core Integration: Seamless startup integration with AddBowtie()
• Database Introspection: Read existing schema for intelligent migrations
• MSBuild Integration: Automated build targets and CI/CD pipeline support

🔗 Noundry.Tuxedo.Cufflink Fake Data Generator

• Intelligent Data Generation: Uses Bogus for realistic fake data based on property names and types
• FK/PK Relationship Aware: Automatically detects and honors foreign key relationships
• Topological Sort: Determines correct insert order based on dependencies
• Bulk Insert Performance: Uses Tuxedo bulk operations for efficient data loading
• Multi-Database Support: Works with SQL Server, PostgreSQL, MySQL, SQLite
• Dual Usage: Available as both a library and installable CLI tool (cufflink)
• Seed & Fake Modes: Generate completely fake data or load predefined seed data
• Auto-Discovery: Scans assemblies for entity models and connection strings

Installation

• Noundry.Tuxedo ORM: dotnet add package Noundry.Tuxedo
• Noundry.Tuxedo.Exceptions: dotnet add package Noundry.Tuxedo.Exceptions (strongly-typed database exceptions)
• Noundry.Tuxedo.Auditor: dotnet add package Noundry.Tuxedo.Auditor (automatic CRUD audit logging)
• Noundry.Tuxedo.Bowtie Migrations: dotnet add package Noundry.Tuxedo.Bowtie (see Bowtie documentation)
• Noundry.Tuxedo.Cufflink Data Generator: dotnet add package Noundry.Tuxedo.Cufflink (see Cufflink documentation)
• Cufflink CLI Tool: dotnet tool install --global Noundry.Tuxedo.Cufflink.CLI
• Providers as needed: Microsoft.Data.SqlClient, Npgsql, MySqlConnector, Microsoft.Data.Sqlite

Quick Start

using System.Data;
using Noundry.Tuxedo;              // Dapper APIs
using Noundry.Tuxedo.Contrib;      // CRUD helpers
using Noundry.Tuxedo.QueryBuilder; // Dynamic query building

Configuration-based helpers

using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Noundry.Tuxedo.DependencyInjection;

// Build configuration (example uses in-memory for simplicity)
var config = new ConfigurationBuilder()
    .AddInMemoryCollection(new Dictionary<string, string?>
    {
        ["TuxedoSqlServer:ConnectionString"] = "Server=.;Database=Demo;Integrated Security=true;TrustServerCertificate=true;",
        ["TuxedoSqlServer:MultipleActiveResultSets"] = "true",
        ["TuxedoSqlServer:ConnectTimeout"] = "60",

        ["TuxedoPostgres:ConnectionString"] = "Host=localhost;Database=demo;Username=demo;Password=pass",
        ["TuxedoPostgres:Pooling"] = "true",
        ["TuxedoPostgres:MinPoolSize"] = "5",
        ["TuxedoPostgres:MaxPoolSize"] = "100",

        ["TuxedoMySql:ConnectionString"] = "Server=localhost;Database=demo;Uid=demo;Pwd=pass;",
        ["TuxedoMySql:AllowUserVariables"] = "true",
        ["TuxedoMySql:UseCompression"] = "true",
        ["TuxedoMySql:ConnectionLifeTime"] = "300",
        ["TuxedoMySql:CommandTimeout"] = "90"
    })
    .Build();

var services = new ServiceCollection();
services.AddTuxedoSqlServerWithOptions(config);         // section "TuxedoSqlServer"
// services.AddTuxedoPostgresWithOptions(config);       // section "TuxedoPostgres"
// services.AddTuxedoMySqlWithOptions(config);          // section "TuxedoMySql"

// Or use a custom section name
services.AddTuxedoPostgresWithOptions(config, sectionName: "CustomPostgres");

Model

using Noundry.Tuxedo.Contrib;

[Table("Products")]
public class Product
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public decimal Price { get; set; }
    public string? Category { get; set; }
    [Computed]
    public DateTime LastModified { get; set; }
}

Attributes

Noundry.Tuxedo supports several attributes to customize how your models map to database tables and columns:

[Table] - Specify Table Name

Maps a class to a specific database table:

[Table("Products")]
public class Product { ... }

[Key] - Auto-Increment Primary Key

Marks a property as an auto-incrementing primary key. The database generates the value:

[Key]
public int Id { get; set; }

[ExplicitKey] - Non-Auto-Increment Primary Key

Marks a property as a primary key where you provide the value:

[ExplicitKey]
public Guid Id { get; set; }

[Computed] - Exclude from Insert/Update

Marks a property as database-generated (timestamps, calculated fields). Excluded from INSERT and UPDATE operations:

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

[Computed]
public decimal TotalWithTax { get; set; }  // Calculated column

[Column] - Custom Column Name Mapping

Maps a property to a different column name in the database. Useful for naming convention mismatches:

public class User
{
    [Key]
    [Column("user_id")]
    public int Id { get; set; }

    [Column("full_name")]
    public string Name { get; set; } = string.Empty;

    [Column("email_address")]
    public string Email { get; set; } = string.Empty;

    // Property name matches column name - no attribute needed
    public int Age { get; set; }
}

The [Column] attribute is particularly useful when:

• Working with legacy databases with different naming conventions (e.g., snake_case vs PascalCase)
• Following database naming standards different from C# conventions
• Avoiding reserved keywords in your database

Example with mixed conventions:

[Table("user_accounts")]
public class UserAccount
{
    [Key]
    [Column("id")]
    public int UserId { get; set; }

    [Column("username")]
    public string UserName { get; set; } = string.Empty;

    [Column("created_at")]
    [Computed]
    public DateTime CreatedAt { get; set; }

    // No Column attribute - matches database column name
    public string Email { get; set; } = string.Empty;
}

Backward Compatibility: Properties without a [Column] attribute work exactly as before - the property name is used as the column name. This ensures 100% backward compatibility with existing code.

Using a connection

using Microsoft.Data.SqlClient;   // or Npgsql/MySqlConnector/Microsoft.Data.Sqlite

var cs = "Server=localhost;Database=Demo;Integrated Security=true;TrustServerCertificate=true";
using IDbConnection db = new SqlConnection(cs);
db.Open();

// Dapper query
var products = db.Query<Product>("SELECT * FROM Products WHERE Category = @cat", new { cat = "Books" });

// Contrib CRUD (sync)
var id = db.Insert(new Product { Name = "Gizmo", Price = 19.99m, Category = "Gadgets" });
var p  = db.Get<Product>(id);
p.Price = 17.99m;
db.Update(p);
db.Delete(p);

// Contrib CRUD (async)
var newId = await db.InsertAsync(new Product { Name = "Async", Price = 10m });
var item  = await db.SelectAsync<Product>(newId); // alias for GetAsync
await db.UpdateAsync(item);
await db.DeleteAsync(item);

// Partial Updates (sync)
var product = db.Get<Product>(id);
product.Name = "Updated Name";
product.Price = 25.99m; // This won't be updated
db.Update(product, propertiesToUpdate: new[] { "Name" }); // Only updates Name column

// Partial Updates with key/value objects
db.Update<Product>(
    keyValues: new { Id = id },
    updateValues: new { Name = "New Name", Price = 29.99m }
);

// Partial Updates (async)
await db.UpdateAsync(product, propertiesToUpdate: new[] { "Name", "Category" });

// Dynamic Query Builder
var expensiveProducts = await QueryBuilder.Query<Product>()
    .Where(p => p.Price > 100)
    .Where(p => p.Category == "Electronics")
    .OrderByDescending(p => p.Price)
    .Take(10)
    .ToListAsync(db);

// Complex queries with joins and aggregations
var categoryCount = await QueryBuilder.Query<Product>()
    .Where(p => p.Active == true)
    .GroupBy(p => p.Category)
    .CountAsync(db);

Contrib CRUD Operations (Complete Reference)

The Noundry.Tuxedo.Contrib namespace provides strongly-typed CRUD operations that work with your entity models. All methods support both sync and async variants.

Entity Model Examples

using Noundry.Tuxedo.Contrib;

// Basic entity with auto-increment key
[Table("Products")]
public class Product
{
    [Key]  // Auto-increment primary key
    public int Id { get; set; }

    [Column("product_name")]  // Custom column name mapping
    public string Name { get; set; } = string.Empty;

    public decimal Price { get; set; }
    public string? Category { get; set; }
    public int Stock { get; set; }
    public bool IsActive { get; set; } = true;

    [Computed]  // Excluded from INSERT/UPDATE
    public DateTime CreatedAt { get; set; }

    [Computed]
    public DateTime? UpdatedAt { get; set; }
}

// Entity with explicit (non-auto-increment) key
[Table("Countries")]
public class Country
{
    [ExplicitKey]  // You provide the key value
    public string Code { get; set; } = string.Empty;  // e.g., "US", "UK"

    public string Name { get; set; } = string.Empty;
    public string Continent { get; set; } = string.Empty;
}

// Entity with GUID key
[Table("Sessions")]
public class Session
{
    [ExplicitKey]
    public Guid Id { get; set; }

    public int UserId { get; set; }
    public string Token { get; set; } = string.Empty;
    public DateTime ExpiresAt { get; set; }
}

// Entity with composite key
[Table("OrderItems")]
public class OrderItem
{
    [ExplicitKey]
    public int OrderId { get; set; }

    [ExplicitKey]
    public int ProductId { get; set; }

    public int Quantity { get; set; }
    public decimal UnitPrice { get; set; }
}

Insert Operations

using Noundry.Tuxedo.Contrib;

// ═══════════════════════════════════════════════════════════════════════════════
// INSERT SINGLE ENTITY (with auto-increment key)
// ═══════════════════════════════════════════════════════════════════════════════

// Sync - returns the new identity value
var product = new Product
{
    Name = "Wireless Mouse",
    Price = 29.99m,
    Category = "Electronics",
    Stock = 100,
    IsActive = true
};
long newId = db.Insert(product);
Console.WriteLine($"Inserted product with ID: {newId}");

// Async version
long newIdAsync = await db.InsertAsync(product);

// ═══════════════════════════════════════════════════════════════════════════════
// INSERT WITH EXPLICIT KEY (GUID)
// ═══════════════════════════════════════════════════════════════════════════════

var session = new Session
{
    Id = Guid.NewGuid(),  // You provide the GUID
    UserId = 123,
    Token = "abc123token",
    ExpiresAt = DateTime.UtcNow.AddHours(24)
};
db.Insert(session);  // Returns 1 (rows affected)

// ═══════════════════════════════════════════════════════════════════════════════
// INSERT WITH EXPLICIT KEY (String)
// ═══════════════════════════════════════════════════════════════════════════════

var country = new Country
{
    Code = "US",
    Name = "United States",
    Continent = "North America"
};
db.Insert(country);

// ═══════════════════════════════════════════════════════════════════════════════
// INSERT MULTIPLE ENTITIES (Batch Insert)
// ═══════════════════════════════════════════════════════════════════════════════

var products = new List<Product>
{
    new Product { Name = "Keyboard", Price = 49.99m, Category = "Electronics", Stock = 50 },
    new Product { Name = "Monitor", Price = 299.99m, Category = "Electronics", Stock = 25 },
    new Product { Name = "USB Cable", Price = 9.99m, Category = "Accessories", Stock = 200 }
};

// Returns number of rows inserted
int rowsInserted = (int)db.Insert(products);
Console.WriteLine($"Inserted {rowsInserted} products");

// Async batch insert
int rowsInsertedAsync = (int)await db.InsertAsync(products);

// ═══════════════════════════════════════════════════════════════════════════════
// INSERT WITH TRANSACTION
// ═══════════════════════════════════════════════════════════════════════════════

using var transaction = db.BeginTransaction();
try
{
    var order = new Order { CustomerId = 1, OrderDate = DateTime.UtcNow };
    var orderId = db.Insert(order, transaction);

    var items = new List<OrderItem>
    {
        new OrderItem { OrderId = (int)orderId, ProductId = 1, Quantity = 2, UnitPrice = 29.99m },
        new OrderItem { OrderId = (int)orderId, ProductId = 2, Quantity = 1, UnitPrice = 49.99m }
    };
    db.Insert(items, transaction);

    transaction.Commit();
}
catch
{
    transaction.Rollback();
    throw;
}

// ═══════════════════════════════════════════════════════════════════════════════
// INSERT AND RETRIEVE FULL ENTITY
// ═══════════════════════════════════════════════════════════════════════════════

var newProduct = new Product { Name = "Headphones", Price = 79.99m, Category = "Electronics" };
var id = await db.InsertAsync(newProduct);
var insertedProduct = await db.GetAsync<Product>(id);  // Get with computed columns populated
Console.WriteLine($"Created at: {insertedProduct.CreatedAt}");

Get/Select Operations

// ═══════════════════════════════════════════════════════════════════════════════
// GET SINGLE ENTITY BY ID
// ═══════════════════════════════════════════════════════════════════════════════

// By integer ID
Product? product = db.Get<Product>(1);
if (product != null)
{
    Console.WriteLine($"Found: {product.Name} - ${product.Price}");
}

// Async version
Product? productAsync = await db.GetAsync<Product>(1);

// SelectAsync is an alias for GetAsync
Product? productAlt = await db.SelectAsync<Product>(1);

// By GUID
Session? session = await db.GetAsync<Session>(Guid.Parse("550e8400-e29b-41d4-a716-446655440000"));

// By string key
Country? country = await db.GetAsync<Country>("US");

// ═══════════════════════════════════════════════════════════════════════════════
// GET ALL ENTITIES
// ═══════════════════════════════════════════════════════════════════════════════

// Get all products
IEnumerable<Product> allProducts = db.GetAll<Product>();
foreach (var p in allProducts)
{
    Console.WriteLine($"{p.Id}: {p.Name}");
}

// Async version
IEnumerable<Product> allProductsAsync = await db.GetAllAsync<Product>();

// ═══════════════════════════════════════════════════════════════════════════════
// GET WITH TRANSACTION
// ═══════════════════════════════════════════════════════════════════════════════

using var transaction = db.BeginTransaction();
var product1 = await db.GetAsync<Product>(1, transaction);
var product2 = await db.GetAsync<Product>(2, transaction);
// ... use products within transaction context
transaction.Commit();

// ═══════════════════════════════════════════════════════════════════════════════
// GET WITH TIMEOUT
// ═══════════════════════════════════════════════════════════════════════════════

// 5 second timeout
var slowProduct = await db.GetAsync<Product>(1, commandTimeout: 5);

Update Operations

// ═══════════════════════════════════════════════════════════════════════════════
// FULL UPDATE (all non-computed, non-key properties)
// ═══════════════════════════════════════════════════════════════════════════════

var product = await db.GetAsync<Product>(1);
product.Name = "Updated Wireless Mouse";
product.Price = 24.99m;
product.Stock = 75;

bool updated = db.Update(product);
Console.WriteLine(updated ? "Updated successfully" : "No rows affected");

// Async version
bool updatedAsync = await db.UpdateAsync(product);

// ═══════════════════════════════════════════════════════════════════════════════
// PARTIAL UPDATE (only specified properties)
// ═══════════════════════════════════════════════════════════════════════════════

var product = await db.GetAsync<Product>(1);
product.Price = 19.99m;        // Will be updated
product.Stock = 50;            // Will NOT be updated (not in list)
product.Name = "Changed";      // Will NOT be updated (not in list)

// Only update the Price column
db.Update(product, propertiesToUpdate: new[] { "Price" });

// Update multiple specific columns
db.Update(product, propertiesToUpdate: new[] { "Price", "IsActive" });

// Async partial update
await db.UpdateAsync(product, propertiesToUpdate: new[] { "Name", "Category" });

// ═══════════════════════════════════════════════════════════════════════════════
// UPDATE BY KEY/VALUE OBJECTS (without loading entity first)
// ═══════════════════════════════════════════════════════════════════════════════

// Update product ID 1 - set Price and Stock
db.Update<Product>(
    keyValues: new { Id = 1 },
    updateValues: new { Price = 29.99m, Stock = 100 }
);

// Update with composite key
db.Update<OrderItem>(
    keyValues: new { OrderId = 1, ProductId = 5 },
    updateValues: new { Quantity = 3, UnitPrice = 24.99m }
);

// Async version
await db.UpdateAsync<Product>(
    keyValues: new { Id = 1 },
    updateValues: new { IsActive = false }
);

// ═══════════════════════════════════════════════════════════════════════════════
// UPDATE MULTIPLE ENTITIES
// ═══════════════════════════════════════════════════════════════════════════════

var products = await db.GetAllAsync<Product>();
var productsToUpdate = products.Where(p => p.Category == "Electronics").ToList();

foreach (var p in productsToUpdate)
{
    p.Price *= 0.9m;  // 10% discount
}

foreach (var p in productsToUpdate)
{
    await db.UpdateAsync(p);
}

// Or with transaction for atomicity
using var transaction = db.BeginTransaction();
foreach (var p in productsToUpdate)
{
    await db.UpdateAsync(p, transaction);
}
transaction.Commit();

// ═══════════════════════════════════════════════════════════════════════════════
// CONDITIONAL UPDATE PATTERNS
// ═══════════════════════════════════════════════════════════════════════════════

// Update only if changed (using dirty tracking with interfaces)
public interface IProduct
{
    int Id { get; set; }
    string Name { get; set; }
    decimal Price { get; set; }
}

var trackedProduct = db.Get<IProduct>(1);  // Returns tracked proxy
trackedProduct.Price = 19.99m;  // Marks as dirty
db.Update(trackedProduct);  // Only updates if IsDirty == true

// Manual conditional update
var product = await db.GetAsync<Product>(1);
var originalPrice = product.Price;
product.Price = CalculateNewPrice(product);

if (product.Price != originalPrice)
{
    await db.UpdateAsync(product, propertiesToUpdate: new[] { "Price" });
}

// ═══════════════════════════════════════════════════════════════════════════════
// SOFT DELETE PATTERN (using Update)
// ═══════════════════════════════════════════════════════════════════════════════

// Instead of deleting, mark as inactive
await db.UpdateAsync<Product>(
    keyValues: new { Id = 1 },
    updateValues: new { IsActive = false, DeletedAt = DateTime.UtcNow }
);

Delete Operations

// ═══════════════════════════════════════════════════════════════════════════════
// DELETE SINGLE ENTITY
// ═══════════════════════════════════════════════════════════════════════════════

var product = await db.GetAsync<Product>(1);
if (product != null)
{
    bool deleted = db.Delete(product);
    Console.WriteLine(deleted ? "Deleted" : "Not found");
}

// Async version
bool deletedAsync = await db.DeleteAsync(product);

// ═══════════════════════════════════════════════════════════════════════════════
// DELETE BY CREATING ENTITY WITH KEY ONLY
// ═══════════════════════════════════════════════════════════════════════════════

// Don't need to load the entity first - just need the key
var productToDelete = new Product { Id = 5 };
await db.DeleteAsync(productToDelete);

// With explicit key
var sessionToDelete = new Session { Id = Guid.Parse("...") };
await db.DeleteAsync(sessionToDelete);

// With string key
var countryToDelete = new Country { Code = "XX" };
await db.DeleteAsync(countryToDelete);

// ═══════════════════════════════════════════════════════════════════════════════
// DELETE ALL ENTITIES OF A TYPE
// ═══════════════════════════════════════════════════════════════════════════════

// WARNING: Deletes ALL rows in the table!
bool anyDeleted = db.DeleteAll<TempData>();

// Async version
bool anyDeletedAsync = await db.DeleteAllAsync<TempData>();

// ═══════════════════════════════════════════════════════════════════════════════
// DELETE MULTIPLE ENTITIES
// ═══════════════════════════════════════════════════════════════════════════════

var expiredSessions = (await db.GetAllAsync<Session>())
    .Where(s => s.ExpiresAt < DateTime.UtcNow)
    .ToList();

using var transaction = db.BeginTransaction();
foreach (var session in expiredSessions)
{
    await db.DeleteAsync(session, transaction);
}
transaction.Commit();

Console.WriteLine($"Deleted {expiredSessions.Count} expired sessions");

// ═══════════════════════════════════════════════════════════════════════════════
// DELETE WITH TRANSACTION
// ═══════════════════════════════════════════════════════════════════════════════

using var transaction = db.BeginTransaction();
try
{
    // Delete order items first (child records)
    var items = await GetOrderItemsAsync(orderId);
    foreach (var item in items)
    {
        await db.DeleteAsync(item, transaction);
    }

    // Then delete the order
    var order = new Order { Id = orderId };
    await db.DeleteAsync(order, transaction);

    transaction.Commit();
}
catch
{
    transaction.Rollback();
    throw;
}

Complete Service Example

public class ProductService
{
    private readonly IDbConnection _db;

    public ProductService(IDbConnection db) => _db = db;

    // CREATE
    public async Task<Product> CreateProductAsync(CreateProductRequest request)
    {
        var product = new Product
        {
            Name = request.Name,
            Price = request.Price,
            Category = request.Category,
            Stock = request.InitialStock,
            IsActive = true
        };

        var id = await _db.InsertAsync(product);
        return await _db.GetAsync<Product>(id);
    }

    // READ
    public async Task<Product?> GetProductAsync(int id)
    {
        return await _db.GetAsync<Product>(id);
    }

    public async Task<IEnumerable<Product>> GetAllProductsAsync()
    {
        return await _db.GetAllAsync<Product>();
    }

    public async Task<IEnumerable<Product>> GetProductsByCategoryAsync(string category)
    {
        return await new QueryBuilder<Product>()
            .Where(p => p.Category == category)
            .Where(p => p.IsActive == true)
            .OrderBy(p => p.Name)
            .ToListAsync(_db);
    }

    // UPDATE
    public async Task<bool> UpdateProductAsync(int id, UpdateProductRequest request)
    {
        var product = await _db.GetAsync<Product>(id);
        if (product == null) return false;

        product.Name = request.Name ?? product.Name;
        product.Price = request.Price ?? product.Price;
        product.Category = request.Category ?? product.Category;

        return await _db.UpdateAsync(product);
    }

    public async Task<bool> UpdatePriceAsync(int id, decimal newPrice)
    {
        // Partial update - only changes price
        return await _db.UpdateAsync<Product>(
            keyValues: new { Id = id },
            updateValues: new { Price = newPrice }
        );
    }

    public async Task<bool> DeactivateProductAsync(int id)
    {
        return await _db.UpdateAsync<Product>(
            keyValues: new { Id = id },
            updateValues: new { IsActive = false }
        );
    }

    // DELETE
    public async Task<bool> DeleteProductAsync(int id)
    {
        var product = new Product { Id = id };
        return await _db.DeleteAsync(product);
    }

    // BULK OPERATIONS
    public async Task<int> CreateProductsAsync(IEnumerable<CreateProductRequest> requests)
    {
        var products = requests.Select(r => new Product
        {
            Name = r.Name,
            Price = r.Price,
            Category = r.Category,
            Stock = r.InitialStock,
            IsActive = true
        }).ToList();

        return (int)await _db.InsertAsync(products);
    }

    public async Task ApplyDiscountAsync(string category, decimal discountPercent)
    {
        var products = (await GetProductsByCategoryAsync(category)).ToList();

        using var transaction = _db.BeginTransaction();
        foreach (var product in products)
        {
            product.Price *= (1 - discountPercent / 100);
            await _db.UpdateAsync(product, transaction, propertiesToUpdate: new[] { "Price" });
        }
        transaction.Commit();
    }
}

Method Reference

Method	Description	Returns
Get<T>(id)	Get entity by primary key	T?
GetAsync<T>(id)	Get entity by primary key (async)	Task<T?>
GetAll<T>()	Get all entities	IEnumerable<T>
GetAllAsync<T>()	Get all entities (async)	Task<IEnumerable<T>>
Insert<T>(entity)	Insert entity, returns identity	long
InsertAsync<T>(entity)	Insert entity (async)	Task<int>
Insert<T>(entities)	Insert multiple, returns row count	long
Update<T>(entity)	Full update of entity	bool
UpdateAsync<T>(entity)	Full update (async)	Task<bool>
Update<T>(entity, propertiesToUpdate)	Partial update	bool
Update<T>(keyValues, updateValues)	Update by key/value objects	bool
Delete<T>(entity)	Delete entity	bool
DeleteAsync<T>(entity)	Delete entity (async)	Task<bool>
DeleteAll<T>()	Delete all entities of type	bool

Partial Updates

Noundry.Tuxedo supports efficient partial updates that only modify specified columns using the same Update/UpdateAsync methods:

// Update only specific properties by name
var product = db.Get<Product>(1);
product.Name = "Updated Product";
product.LastModified = DateTime.Now;
// Price remains unchanged in database
db.Update(product, propertiesToUpdate: new[] { "Name", "LastModified" });

// Update using separate key and value objects
db.Update<Product>(
    keyValues: new { Id = 1 },
    updateValues: new { Name = "Direct Update", Price = 39.99m }
);

// Async versions available
await db.UpdateAsync(product, propertiesToUpdate: new[] { "Name" });
await db.UpdateAsync<Product>(
    keyValues: new { Id = 1 },
    updateValues: new { Category = "Updated Category" }
);

Partial Update Features:

• Uses the same Update/UpdateAsync methods with optional parameters
• Updates only specified columns, leaving others unchanged
• Supports both property name arrays and key/value object patterns
• Case-insensitive property name matching
• Validates property names and prevents key column updates
• Ignores [Computed] properties automatically
• Backward compatible - existing Update calls continue to work unchanged

Dynamic Query Builder

Noundry.Tuxedo includes a powerful fluent query builder with LINQ-style expressions and multi-database dialect support.

Key Features:

• Full [Column] attribute support - uses mapped column names, not property names
• Dialect-aware SQL generation (SQL Server, PostgreSQL, MySQL, SQLite)
• Type-safe expressions with parameterized queries
• String method support (Contains, StartsWith, EndsWith)
• SQL injection protection via identifier validation

using Noundry.Tuxedo.QueryBuilder;

// Basic query building
var query = QueryBuilder.Query<Product>()
    .Where(p => p.Category == "Electronics")
    .Where(p => p.Price > 100)
    .OrderBy(p => p.Name)
    .Skip(10)
    .Take(20);

var products = await query.ToListAsync(db);

// Works correctly with [Column] attribute mappings
[Table("products")]
public class Product
{
    [Key]
    [Column("product_id")]
    public int Id { get; set; }

    [Column("product_name")]
    public string Name { get; set; }
}

// Generates: SELECT * FROM products WHERE product_name = @p0
var query = new QueryBuilder<Product>(TuxedoDialect.Postgres)
    .Where(p => p.Name == "Widget");

WHERE Clause Operations

The QueryBuilder supports comprehensive WHERE conditions with full expression tree support:

// Basic equality and comparison
var query1 = QueryBuilder.Query<Product>()
    .Where(p => p.Category == "Electronics")
    .Where(p => p.Price > 100);

// Complex boolean expressions
var query2 = QueryBuilder.Query<Product>()
    .Where(p => p.Price > 50 && p.Price < 500)
    .Where(p => p.InStock == true || p.PreOrder == true);

// IN and NOT IN operations
var categoryIds = new[] { 1, 2, 3, 4, 5 };
var query3 = QueryBuilder.Query<Product>()
    .WhereIn(p => p.CategoryId, categoryIds)
    .WhereNotIn(p => p.StatusId, new[] { 99, 100 });

// BETWEEN ranges
var query4 = QueryBuilder.Query<Product>()
    .WhereBetween(p => p.Price, 10.00m, 99.99m)
    .WhereBetween(p => p.CreatedDate, startDate, endDate);

// NULL checks
var query5 = QueryBuilder.Query<Product>()
    .WhereNotNull(p => p.Description)
    .WhereNull(p => p.DeletedAt);

// Combining with OR logic
var query6 = QueryBuilder.Query<Product>()
    .Where(p => p.Category == "Electronics")
    .Or(p => p.Featured == true)
    .Or(p => p.Price < 20);

// Using NOT for negation
var query7 = QueryBuilder.Query<Product>()
    .Not(p => p.Category == "Discontinued")
    .Not(p => p.Price > 1000);

// Raw SQL conditions when needed
var query8 = QueryBuilder.Query<Product>()
    .Where("Price * Quantity > @threshold", new { threshold = 1000 })
    .Where("LOWER(Name) LIKE @pattern", new { pattern = "%phone%" });

String Methods (Dialect-Aware)

The QueryBuilder supports common string methods with automatic dialect-specific SQL generation:

// Contains - generates LIKE with wildcards
var searchResults = QueryBuilder.Query<Product>()
    .Where(p => p.Name.Contains("phone"));
// SQL Server: Name LIKE '%' + @p0 + '%'
// PostgreSQL/SQLite: Name LIKE '%' || @p0 || '%'
// MySQL: Name LIKE CONCAT('%', @p0, '%')

// StartsWith - prefix matching
var startsWithQuery = QueryBuilder.Query<Product>()
    .Where(p => p.Name.StartsWith("Apple"));
// SQL Server: Name LIKE @p0 + '%'
// PostgreSQL/SQLite: Name LIKE @p0 || '%'
// MySQL: Name LIKE CONCAT(@p0, '%')

// EndsWith - suffix matching
var endsWithQuery = QueryBuilder.Query<Product>()
    .Where(p => p.Name.EndsWith("Pro"));
// SQL Server: Name LIKE '%' + @p0
// PostgreSQL/SQLite: Name LIKE '%' || @p0
// MySQL: Name LIKE CONCAT('%', @p0)

// IsNullOrEmpty - null or empty string check
var emptyCheck = QueryBuilder.Query<Product>()
    .Where(p => !string.IsNullOrEmpty(p.Description));
// Generates: (Description IS NULL OR Description = '')

// IsNullOrWhiteSpace - null, empty, or whitespace check
var whitespaceCheck = QueryBuilder.Query<Product>()
    .Where(p => !string.IsNullOrWhiteSpace(p.Notes));
// SQL Server: (Notes IS NULL OR LTRIM(RTRIM(Notes)) = '')
// PostgreSQL/MySQL/SQLite: (Notes IS NULL OR TRIM(Notes) = '')

JOIN Operations

Support for INNER, LEFT, and RIGHT joins with type-safe expressions:

// INNER JOIN
var innerJoin = QueryBuilder.Query<Product>()
    .InnerJoin<Category>((p, c) => p.CategoryId == c.Id)
    .Where(p => p.Price > 100)
    .SelectRaw("p.*, c.Name as CategoryName");

// LEFT JOIN (include products even without categories)
var leftJoin = QueryBuilder.Query<Product>()
    .LeftJoin<Category>((p, c) => p.CategoryId == c.Id)
    .Where(p => p.Active == true)
    .OrderBy(p => p.Name);

// RIGHT JOIN (include all categories even without products)
var rightJoin = QueryBuilder.Query<Category>()
    .RightJoin<Product>((c, p) => c.Id == p.CategoryId)
    .GroupBy(c => c.Name)
    .SelectRaw("c.Name, COUNT(p.Id) as ProductCount");

// Multiple JOINs
var multiJoin = QueryBuilder.Query<Order>()
    .InnerJoin<Customer>((o, c) => o.CustomerId == c.Id)
    .InnerJoin<Product>((o, p) => o.ProductId == p.Id)
    .Where(o => o.OrderDate > DateTime.Today.AddDays(-30))
    .SelectRaw("o.*, c.Name as CustomerName, p.Name as ProductName");

// Complex JOIN with additional conditions
var complexJoin = QueryBuilder.Query<Product>()
    .InnerJoin<Category>((p, c) => p.CategoryId == c.Id)
    .InnerJoin<Supplier>((p, s) => p.SupplierId == s.Id)
    .Where(p => p.Price > 50)
    .Where(c => c.Active == true)
    .OrderBy(p => p.Name);

Aggregations and Grouping

// COUNT operations
var totalProducts = await QueryBuilder.Query<Product>()
    .CountAsync(db);

var activeCount = await QueryBuilder.Query<Product>()
    .Where(p => p.Active == true)
    .CountAsync(db);

var categoryCount = await QueryBuilder.Query<Product>()
    .Count(p => p.CategoryId)  // COUNT(CategoryId)
    .ToListAsync(db);

// SUM operations
var totalRevenue = QueryBuilder.Query<Order>()
    .Where(o => o.OrderDate >= DateTime.Today.AddMonths(-1))
    .Sum(o => o.TotalAmount);

// AVERAGE operations
var avgPrice = QueryBuilder.Query<Product>()
    .Where(p => p.Category == "Electronics")
    .Average(p => p.Price);

// MIN/MAX operations
var cheapestPrice = QueryBuilder.Query<Product>()
    .Where(p => p.InStock == true)
    .Min(p => p.Price);

var mostExpensive = QueryBuilder.Query<Product>()
    .Where(p => p.Category == "Luxury")
    .Max(p => p.Price);

// GROUP BY with HAVING
var categoryStats = QueryBuilder.Query<Product>()
    .GroupBy(p => p.Category)
    .Having(p => p.Price > 50)
    .SelectRaw("Category, COUNT(*) as ProductCount, AVG(Price) as AvgPrice, MAX(Price) as MaxPrice");

// Multiple GROUP BY columns
var salesByRegionAndCategory = QueryBuilder.Query<Sale>()
    .GroupBy(s => s.Region, s => s.ProductCategory)
    .Having(s => s.Amount > 1000)
    .SelectRaw("Region, ProductCategory, SUM(Amount) as TotalSales, COUNT(*) as SaleCount")
    .OrderByDescending(s => s.TotalSales);

// Complex aggregation with JOINs
var categoryPerformance = QueryBuilder.Query<Product>()
    .InnerJoin<OrderItem>((p, oi) => p.Id == oi.ProductId)
    .InnerJoin<Category>((p, c) => p.CategoryId == c.Id)
    .GroupBy(c => c.Name)
    .SelectRaw(@"c.Name as CategoryName,
              COUNT(DISTINCT p.Id) as UniqueProducts,
              SUM(oi.Quantity) as TotalSold,
              AVG(oi.Price) as AvgSellingPrice")
    .OrderByDescending(x => x.TotalSold);

Pagination

Full support for pagination with dialect-specific SQL generation:

// Basic Skip and Take
var page = await QueryBuilder.Query<Product>()
    .Where(p => p.Active == true)
    .OrderBy(p => p.Name)
    .Skip(20)      // Skip first 20 records
    .Take(10)      // Take next 10 records
    .ToListAsync(db);

// Page-based pagination (convenience method)
var pagedResults = await QueryBuilder.Query<Product>()
    .Where(p => p.Category == "Electronics")
    .OrderByDescending(p => p.CreatedDate)
    .Page(pageIndex: 2, pageSize: 25)  // Gets page 3 (0-indexed), 25 items per page
    .ToListAsync(db);

// First N records only
var top10 = await QueryBuilder.Query<Product>()
    .Where(p => p.Featured == true)
    .OrderByDescending(p => p.Rating)
    .Take(10)
    .ToListAsync(db);

// Skip without Take (returns all remaining)
var afterFirst100 = await QueryBuilder.Query<Product>()
    .OrderBy(p => p.Id)
    .Skip(100)
    .ToListAsync(db);

// Combining with complex queries
var complexPaged = await QueryBuilder.Query<Product>()
    .InnerJoin<Category>((p, c) => p.CategoryId == c.Id)
    .Where(p => p.Price > 50)
    .Where(c => c.Active == true)
    .OrderByDescending(p => p.CreatedDate)
    .ThenBy(p => p.Name)
    .Page(1, 20)  // Page 2, 20 items
    .ToListAsync(db);

Dialect-Specific SQL Generation

// SQL Server (uses OFFSET/FETCH)
var sqlServerQuery = new QueryBuilder<Product>(TuxedoDialect.SqlServer)
    .OrderBy(p => p.Name)
    .Skip(20).Take(10);
// Generates: ORDER BY Name ASC OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

// PostgreSQL (uses LIMIT/OFFSET)
var postgresQuery = new QueryBuilder<Product>(TuxedoDialect.Postgres)
    .OrderBy(p => p.Name)
    .Skip(20).Take(10);
// Generates: ORDER BY Name ASC LIMIT 10 OFFSET 20

// MySQL (uses LIMIT/OFFSET)
var mysqlQuery = new QueryBuilder<Product>(TuxedoDialect.MySql)
    .OrderBy(p => p.Name)
    .Skip(20).Take(10);
// Generates: ORDER BY Name ASC LIMIT 10 OFFSET 20

// SQLite (uses LIMIT/OFFSET)
var sqliteQuery = new QueryBuilder<Product>(TuxedoDialect.Sqlite)
    .OrderBy(p => p.Name)
    .Skip(20).Take(10);
// Generates: ORDER BY Name ASC LIMIT 10 OFFSET 20

Sorting and Ordering

// Simple ordering
var sorted1 = QueryBuilder.Query<Product>()
    .OrderBy(p => p.Name);

var sorted2 = QueryBuilder.Query<Product>()
    .OrderByDescending(p => p.Price);

// Multiple sort columns with ThenBy
var multiSort = QueryBuilder.Query<Product>()
    .OrderBy(p => p.Category)
    .ThenByDescending(p => p.Price)
    .ThenBy(p => p.Name);

// Combining with other operations
var complexSort = QueryBuilder.Query<Product>()
    .InnerJoin<Category>((p, c) => p.CategoryId == c.Id)
    .Where(p => p.Active == true)
    .OrderBy(c => c.Name)           // Sort by category name first
    .ThenByDescending(p => p.Price)  // Then by price descending
    .ThenBy(p => p.Name)             // Finally by product name
    .Take(50);

SQL Generation and Inspection

The QueryBuilder allows you to build and inspect SQL before execution:

// Build complex query
var builder = QueryBuilder.Query<Product>()
    .InnerJoin<Category>((p, c) => p.CategoryId == c.Id)
    .Where(p => p.Price > 100)
    .Where(p => p.Active == true)
    .WhereIn(p => p.StatusId, new[] { 1, 2, 3 })
    .OrderByDescending(p => p.CreatedDate)
    .Skip(10)
    .Take(20);

// Get generated SQL
string sql = builder.BuildSql();
// Result: SELECT * FROM Products 
//         INNER JOIN Categories ON Products.CategoryId = Categories.Id
//         WHERE Price > @p1 AND Active = @p2 AND StatusId IN (@p3, @p4, @p5)
//         ORDER BY CreatedDate DESC
//         LIMIT 20 OFFSET 10

// Get parameters dictionary
object parameters = builder.GetParameters();
// Result: { p1: 100, p2: true, p3: 1, p4: 2, p5: 3 }

// Execute manually with Dapper/Tuxedo
var results = await db.QueryAsync<Product>(sql, parameters);

// Or use built-in execution
var directResults = await builder.ToListAsync(db);

SELECT Projections

Control what columns are selected:

// Select all columns (default)
var all = QueryBuilder.Query<Product>()
    .SelectAll();

// Select specific columns by name
var specific = QueryBuilder.Query<Product>()
    .Select("Id", "Name", "Price");

// Select with expressions
var withExpression = QueryBuilder.Query<Product>()
    .Select(p => new { p.Id, p.Name, p.Price });

// Select with calculations and aliases
var calculated = QueryBuilder.Query<Product>()
    .SelectRaw(@"Id, Name, Price,
              Price * 1.1 as PriceWithTax,
              CASE WHEN Price > 100 THEN 'Expensive' ELSE 'Affordable' END as PriceCategory");

// Select with aggregate functions
var aggregated = QueryBuilder.Query<Product>()
    .GroupBy(p => p.Category)
    .SelectRaw("Category, COUNT(*) as Count, AVG(Price) as AvgPrice, MAX(Price) as MaxPrice");

Execution Methods

Multiple ways to execute and retrieve data:

// Get list of results
var products = await QueryBuilder.Query<Product>()
    .Where(p => p.Active == true)
    .ToListAsync(db);

// Get first or default
var firstProduct = await QueryBuilder.Query<Product>()
    .Where(p => p.Featured == true)
    .OrderByDescending(p => p.Rating)
    .FirstOrDefaultAsync(db);

// Get single result (throws if multiple)
var singleProduct = await QueryBuilder.Query<Product>()
    .Where(p => p.Id == 123)
    .SingleAsync(db);

// Get count
var count = await QueryBuilder.Query<Product>()
    .Where(p => p.Category == "Electronics")
    .CountAsync(db);

// Check if any exist
var hasProducts = await QueryBuilder.Query<Product>()
    .Where(p => p.Price < 10)
    .AnyAsync(db);

Raw SQL Integration

When you need complete control:

// Use raw SQL for complex queries
var rawQuery = QueryBuilder.Query<Product>()
    .Raw(@"SELECT p.*, c.Name as CategoryName
           FROM Products p
           LEFT JOIN Categories c ON p.CategoryId = c.Id
           WHERE p.Price > @minPrice
           AND EXISTS (
               SELECT 1 FROM OrderItems oi 
               WHERE oi.ProductId = p.Id 
               AND oi.OrderDate > @startDate
           )", new { minPrice = 100, startDate = DateTime.Today.AddMonths(-1) });

var results = await rawQuery.ToListAsync(db);

// Combine raw WHERE conditions with fluent API
var mixed = QueryBuilder.Query<Product>()
    .Where(p => p.Active == true)
    .Where("LOWER(Name) LIKE @pattern", new { pattern = "%phone%" })
    .Where("Price BETWEEN @min AND @max", new { min = 100, max = 1000 })
    .OrderBy(p => p.Name);

Advanced Scenarios

// Subqueries using raw SQL
var withSubquery = QueryBuilder.Query<Product>()
    .Where(@"CategoryId IN (
        SELECT Id FROM Categories 
        WHERE Active = 1 AND ParentId = @parentId
    )", new { parentId = 5 });

// Complex business logic
var businessQuery = QueryBuilder.Query<Order>()
    .InnerJoin<Customer>((o, c) => o.CustomerId == c.Id)
    .Where(o => o.Status == "Completed")
    .Where(o => o.TotalAmount > 1000)
    .Where(c => c.LoyaltyTier == "Gold")
    .WhereBetween(o => o.OrderDate, startOfMonth, endOfMonth)
    .GroupBy(c => c.Region)
    .Having(o => o.TotalAmount > 5000)
    .SelectRaw(@"c.Region,
              COUNT(DISTINCT o.CustomerId) as UniqueCustomers,
              COUNT(o.Id) as OrderCount,
              SUM(o.TotalAmount) as Revenue,
              AVG(o.TotalAmount) as AvgOrderValue")
    .OrderByDescending(x => x.Revenue);

// Dynamic query building
var dynamicQuery = QueryBuilder.Query<Product>();

if (!string.IsNullOrEmpty(searchTerm))
    dynamicQuery.Where(p => p.Name.Contains(searchTerm));

if (minPrice.HasValue)
    dynamicQuery.Where(p => p.Price >= minPrice.Value);

if (maxPrice.HasValue)
    dynamicQuery.Where(p => p.Price <= maxPrice.Value);

if (categoryIds?.Any() == true)
    dynamicQuery.WhereIn(p => p.CategoryId, categoryIds);

dynamicQuery.OrderBy(p => p.Name)
            .Page(pageIndex, pageSize);

var results = await dynamicQuery.ToListAsync(db);

Real-World QueryBuilder Patterns

E-Commerce Product Search

public class ProductSearchService
{
    private readonly IDbConnection _db;

    public async Task<ProductSearchResult> SearchProductsAsync(ProductSearchRequest request)
    {
        var query = new QueryBuilder<Product>(TuxedoDialect.SqlServer)
            .SelectAll();

        // Text search with dialect-aware LIKE
        if (!string.IsNullOrEmpty(request.SearchTerm))
        {
            query.Where(p => p.Name.Contains(request.SearchTerm) ||
                            p.Description.Contains(request.SearchTerm));
        }

        // Category filter
        if (request.CategoryIds?.Any() == true)
            query.WhereIn(p => p.CategoryId, request.CategoryIds);

        // Price range
        if (request.MinPrice.HasValue)
            query.Where(p => p.Price >= request.MinPrice.Value);
        if (request.MaxPrice.HasValue)
            query.Where(p => p.Price <= request.MaxPrice.Value);

        // Availability
        if (request.InStockOnly)
            query.Where(p => p.StockQuantity > 0);

        // Rating filter
        if (request.MinRating.HasValue)
            query.Where(p => p.AverageRating >= request.MinRating.Value);

        // Sorting
        query = request.SortBy switch
        {
            "price_asc" => query.OrderBy(p => p.Price),
            "price_desc" => query.OrderByDescending(p => p.Price),
            "rating" => query.OrderByDescending(p => p.AverageRating),
            "newest" => query.OrderByDescending(p => p.CreatedAt),
            "name" => query.OrderBy(p => p.Name),
            _ => query.OrderByDescending(p => p.CreatedAt)
        };

        // Get total count for pagination
        var countQuery = query.Count();
        var totalCount = await countQuery.ExecuteScalarAsync<int>(_db);

        // Apply pagination
        var products = await query
            .Page(request.PageIndex, request.PageSize)
            .ToListAsync(_db);

        return new ProductSearchResult
        {
            Products = products,
            TotalCount = totalCount,
            PageIndex = request.PageIndex,
            PageSize = request.PageSize
        };
    }
}

Dashboard Analytics Query

public class DashboardService
{
    public async Task<DashboardStats> GetDashboardStatsAsync(DateTime startDate, DateTime endDate)
    {
        // Sales by category
        var salesByCategory = await new QueryBuilder<Order>(TuxedoDialect.Postgres)
            .InnerJoin<OrderItem>((o, oi) => o.Id == oi.OrderId)
            .InnerJoin<Product>((oi, p) => oi.ProductId == p.Id)
            .InnerJoin<Category>((p, c) => p.CategoryId == c.Id)
            .WhereBetween(o => o.OrderDate, startDate, endDate)
            .Where(o => o.Status == "Completed")
            .GroupBy(c => c.Name)
            .SelectRaw(@"c.Name as CategoryName,
                      COUNT(DISTINCT o.Id) as OrderCount,
                      SUM(oi.Quantity * oi.UnitPrice) as Revenue")
            .OrderByDescending(x => x.Revenue)
            .ToListAsync<CategorySalesDto>(_db);

        // Top customers
        var topCustomers = await new QueryBuilder<Order>(TuxedoDialect.Postgres)
            .InnerJoin<Customer>((o, c) => o.CustomerId == c.Id)
            .WhereBetween(o => o.OrderDate, startDate, endDate)
            .Where(o => o.Status == "Completed")
            .GroupBy(c => c.Id, c => c.Name, c => c.Email)
            .SelectRaw(@"c.Id, c.Name, c.Email,
                      COUNT(o.Id) as OrderCount,
                      SUM(o.TotalAmount) as TotalSpent")
            .Having("SUM(o.TotalAmount) > 1000")
            .OrderByDescending(x => x.TotalSpent)
            .Take(10)
            .ToListAsync<TopCustomerDto>(_db);

        // Daily sales trend
        var dailySales = await new QueryBuilder<Order>(TuxedoDialect.Postgres)
            .WhereBetween(o => o.OrderDate, startDate, endDate)
            .Where(o => o.Status == "Completed")
            .GroupBy("DATE(OrderDate)")
            .SelectRaw(@"DATE(OrderDate) as Date,
                      COUNT(*) as OrderCount,
                      SUM(TotalAmount) as Revenue")
            .OrderBy("DATE(OrderDate)")
            .ToListAsync<DailySalesDto>(_db);

        return new DashboardStats
        {
            SalesByCategory = salesByCategory,
            TopCustomers = topCustomers,
            DailySales = dailySales
        };
    }
}

User Activity Report

public async Task<IEnumerable<UserActivityReport>> GetUserActivityAsync(
    DateTime? since = null,
    string[]? roles = null,
    bool includeInactive = false)
{
    var query = new QueryBuilder<User>(TuxedoDialect.MySql)
        .LeftJoin<UserLogin>((u, l) => u.Id == l.UserId)
        .LeftJoin<UserAction>((u, a) => u.Id == a.UserId);

    if (since.HasValue)
        query.Where(u => u.CreatedAt >= since.Value);

    if (roles?.Any() == true)
        query.WhereIn(u => u.Role, roles);

    if (!includeInactive)
        query.Where(u => u.IsActive == true);

    return await query
        .GroupBy(u => u.Id, u => u.Email, u => u.Name, u => u.Role)
        .SelectRaw(@"u.Id, u.Email, u.Name, u.Role,
                  COUNT(DISTINCT l.Id) as LoginCount,
                  MAX(l.LoginTime) as LastLogin,
                  COUNT(DISTINCT a.Id) as ActionCount")
        .OrderByDescending(x => x.LastLogin)
        .ToListAsync<UserActivityReport>(_db);
}

Inventory Management Query

public async Task<IEnumerable<InventoryAlert>> GetLowStockAlertsAsync()
{
    return await new QueryBuilder<Product>(TuxedoDialect.SqlServer)
        .InnerJoin<Category>((p, c) => p.CategoryId == c.Id)
        .InnerJoin<Supplier>((p, s) => p.SupplierId == s.Id)
        .Where(p => p.StockQuantity <= p.ReorderLevel)
        .Where(p => p.IsActive == true)
        .WhereNotNull(p => p.SupplierId)
        .SelectRaw(@"p.Id, p.Name, p.SKU, p.StockQuantity, p.ReorderLevel,
                  c.Name as CategoryName, s.Name as SupplierName, s.Email as SupplierEmail,
                  CASE
                      WHEN p.StockQuantity = 0 THEN 'Critical'
                      WHEN p.StockQuantity <= p.ReorderLevel / 2 THEN 'Low'
                      ELSE 'Warning'
                  END as AlertLevel")
        .OrderBy("AlertLevel")
        .ThenBy(p => p.StockQuantity)
        .ToListAsync<InventoryAlert>(_db);
}

Multi-Tenant Data Access

using Noundry.Tuxedo.Pagination; // For GetDialect() extension method

public class TenantAwareRepository<T> where T : class, ITenantEntity
{
    private readonly IDbConnection _db;
    private readonly ITenantContext _tenantContext;

    public async Task<IEnumerable<T>> GetAllAsync()
    {
        return await new QueryBuilder<T>(_db.GetDialect())
            .Where(e => e.TenantId == _tenantContext.TenantId)
            .Where(e => e.IsDeleted == false)
            .OrderByDescending(e => e.CreatedAt)
            .ToListAsync(_db);
    }

    public async Task<T?> GetByIdAsync(int id)
    {
        return await new QueryBuilder<T>(_db.GetDialect())
            .Where(e => e.Id == id)
            .Where(e => e.TenantId == _tenantContext.TenantId)
            .Where(e => e.IsDeleted == false)
            .FirstOrDefaultAsync(_db);
    }

    public async Task<PagedResult<T>> GetPagedAsync(
        Expression<Func<T, bool>>? filter = null,
        int pageIndex = 0,
        int pageSize = 20)
    {
        var query = new QueryBuilder<T>(_db.GetDialect())
            .Where(e => e.TenantId == _tenantContext.TenantId)
            .Where(e => e.IsDeleted == false);

        if (filter != null)
            query.Where(filter);

        var totalCount = await query.CountAsync(_db);
        var items = await query
            .OrderByDescending(e => e.CreatedAt)
            .Page(pageIndex, pageSize)
            .ToListAsync(_db);

        return new PagedResult<T>(items, totalCount, pageIndex, pageSize);
    }
}

Audit Trail Query

public async Task<IEnumerable<AuditEntry>> GetAuditTrailAsync(AuditSearchCriteria criteria)
{
    var query = new QueryBuilder<AuditLog>(TuxedoDialect.Postgres)
        .SelectAll();

    // Entity filter
    if (!string.IsNullOrEmpty(criteria.TableName))
        query.Where(a => a.TableName == criteria.TableName);

    if (!string.IsNullOrEmpty(criteria.EntityId))
        query.Where(a => a.EntityId == criteria.EntityId);

    // User filter
    if (!string.IsNullOrEmpty(criteria.Username))
        query.Where(a => a.Username.Contains(criteria.Username));

    // Event type filter
    if (criteria.EventTypes?.Any() == true)
        query.WhereIn(a => a.EventType, criteria.EventTypes);

    // Date range
    if (criteria.StartDate.HasValue)
        query.Where(a => a.Timestamp >= criteria.StartDate.Value);
    if (criteria.EndDate.HasValue)
        query.Where(a => a.Timestamp <= criteria.EndDate.Value);

    // Search in changed values (PostgreSQL JSONB)
    if (!string.IsNullOrEmpty(criteria.ValueSearch))
    {
        query.Where(@"new_values::text ILIKE @search OR old_values::text ILIKE @search",
            new { search = $"%{criteria.ValueSearch}%" });
    }

    return await query
        .OrderByDescending(a => a.Timestamp)
        .Page(criteria.PageIndex, criteria.PageSize)
        .ToListAsync<AuditEntry>(_db);
}

Recursive/Hierarchical Data (with CTE via Raw SQL)

public async Task<IEnumerable<CategoryHierarchy>> GetCategoryTreeAsync(int? parentId = null)
{
    // Use raw SQL for recursive CTE, then filter with QueryBuilder
    var cte = @"
        WITH RECURSIVE CategoryTree AS (
            SELECT Id, Name, ParentId, 0 as Level, CAST(Name as VARCHAR(1000)) as Path
            FROM Categories
            WHERE ParentId IS NULL

            UNION ALL

            SELECT c.Id, c.Name, c.ParentId, ct.Level + 1,
                   CAST(ct.Path || ' > ' || c.Name as VARCHAR(1000))
            FROM Categories c
            INNER JOIN CategoryTree ct ON c.ParentId = ct.Id
        )
        SELECT * FROM CategoryTree";

    return await new QueryBuilder<CategoryHierarchy>(TuxedoDialect.Postgres)
        .Raw(cte)
        .OrderBy(c => c.Path)
        .ToListAsync(_db);
}

QueryBuilder with Specification Pattern

The Noundry.Tuxedo QueryBuilder becomes even more powerful when combined with the Specification Pattern, enabling you to build complex, reusable, and testable business logic. This pattern helps organize domain rules and makes queries more maintainable.

Setting Up the Specification Pattern

// Base specification interface and implementation
public interface ISpecification<T>
{
    Expression<Func<T, bool>> ToExpression();
    bool IsSatisfiedBy(T entity);
}

public abstract class Specification<T> : ISpecification<T>
{
    public abstract Expression<Func<T, bool>> ToExpression();
    
    public bool IsSatisfiedBy(T entity)
    {
        var predicate = ToExpression().Compile();
        return predicate(entity);
    }

    // Logical operators for combining specifications
    public Specification<T> And(Specification<T> specification)
    {
        return new AndSpecification<T>(this, specification);
    }

    public Specification<T> Or(Specification<T> specification)
    {
        return new OrSpecification<T>(this, specification);
    }

    public Specification<T> Not()
    {
        return new NotSpecification<T>(this);
    }
}

Domain-Specific Specifications

Create reusable business rules as specifications:

// Product specifications
public class ActiveProductSpecification : Specification<Product>
{
    public override Expression<Func<Product, bool>> ToExpression()
    {
        return product => product.Active;
    }
}

public class ProductPriceRangeSpecification : Specification<Product>
{
    private readonly decimal _minPrice;
    private readonly decimal _maxPrice;

    public ProductPriceRangeSpecification(decimal minPrice, decimal maxPrice)
    {
        _minPrice = minPrice;
        _maxPrice = maxPrice;
    }

    public override Expression<Func<Product, bool>> ToExpression()
    {
        return product => product.Price >= _minPrice && product.Price <= _maxPrice;
    }
}

public class ProductCategorySpecification : Specification<Product>
{
    private readonly string _category;

    public ProductCategorySpecification(string category)
    {
        _category = category;
    }

    public override Expression<Func<Product, bool>> ToExpression()
    {
        return product => product.Category == _category;
    }
}

public class RecentProductSpecification : Specification<Product>
{
    private readonly int _daysSinceCreated;

    public RecentProductSpecification(int daysSinceCreated = 30)
    {
        _daysSinceCreated = daysSinceCreated;
    }

    public override Expression<Func<Product, bool>> ToExpression()
    {
        var cutoffDate = DateTime.Now.AddDays(-_daysSinceCreated);
        return product => product.CreatedDate >= cutoffDate;
    }
}

Repository with Specification Pattern

Integrate specifications with QueryBuilder in your repositories:

public class ProductRepository
{
    private readonly IDbConnection _connection;

    public ProductRepository(IDbConnection connection)
    {
        _connection = connection;
    }

    public async Task<IEnumerable<Product>> GetBySpecificationAsync(ISpecification<Product> specification)
    {
        return await QueryBuilder.Query<Product>()
            .Where(specification.ToExpression())
            .ToListAsync(_connection);
    }

    public async Task<IEnumerable<Product>> GetBySpecificationAsync(
        ISpecification<Product> specification,
        Func<IQueryBuilder<Product>, IQueryBuilder<Product>> configure)
    {
        var query = QueryBuilder.Query<Product>()
            .Where(specification.ToExpression());

        query = configure(query);

        return await query.ToListAsync(_connection);
    }
}

Business Logic with Combined Specifications

Create complex business queries by combining simple specifications:

// Example 1: Available products in price range
public async Task<IEnumerable<Product>> GetAvailableProductsInPriceRangeAsync(
    decimal minPrice, decimal maxPrice, string category)
{
    var specification = new ActiveProductSpecification()
        .And(new InStockProductSpecification())
        .And(new ProductPriceRangeSpecification(minPrice, maxPrice))
        .And(new ProductCategorySpecification(category));

    return await QueryBuilder.Query<Product>()
        .Where(specification.ToExpression())
        .OrderBy(p => p.Name)
        .ToListAsync(_connection);
}

// Example 2: Featured products using complex business logic
public async Task<IEnumerable<Product>> GetFeaturedProductsAsync()
{
    // Business rule: Featured products are recent, in-stock, active products under $500
    var specification = new ActiveProductSpecification()
        .And(new InStockProductSpecification())
        .And(new RecentProductSpecification(60)) // Created in last 60 days
        .And(new ProductPriceRangeSpecification(0, 500));

    return await QueryBuilder.Query<Product>()
        .Where(specification.ToExpression())
        .OrderByDescending(p => p.CreatedDate)
        .Take(20)
        .ToListAsync(_connection);
}

// Example 3: Products with extreme pricing (cheap or expensive)
public async Task<IEnumerable<Product>> GetExtremePricingProductsAsync()
{
    var cheapSpec = new ProductPriceRangeSpecification(0, 20);
    var expensiveSpec = new ProductPriceRangeSpecification(500, decimal.MaxValue);
    var priceExtremeSpec = cheapSpec.Or(expensiveSpec);

    return await QueryBuilder.Query<Product>()
        .Where(priceExtremeSpec.ToExpression())
        .Where(p => p.Active)
        .OrderBy(p => p.Price)
        .ToListAsync(_connection);
}

// Example 4: Products NOT in specific category
public async Task<IEnumerable<Product>> GetNonElectronicsProductsAsync()
{
    var notElectronicsSpec = new ProductCategorySpecification("Electronics").Not();
    
    return await QueryBuilder.Query<Product>()
        .Where(notElectronicsSpec.ToExpression())
        .Where(p => p.Active)
        .OrderBy(p => p.Name)
        .Take(50)
        .ToListAsync(_connection);
}

Dynamic Query Building with Specifications

Build queries dynamically based on user input:

public async Task<IEnumerable<Product>> BuildDynamicProductQueryAsync(
    string? category = null,
    decimal? minPrice = null,
    decimal? maxPrice = null,
    string? searchTerm = null,
    bool? activeOnly = true,
    bool? inStockOnly = true,
    int pageIndex = 0,
    int pageSize = 20)
{
    var query = QueryBuilder.Query<Product>();

    // Apply base specifications
    if (activeOnly == true)
    {
        var activeSpec = new ActiveProductSpecification();
        query = query.Where(activeSpec.ToExpression());
    }

    if (inStockOnly == true)
    {
        var inStockSpec = new InStockProductSpecification();
        query = query.Where(inStockSpec.ToExpression());
    }

    // Apply conditional filters
    if (!string.IsNullOrEmpty(category))
    {
        var categorySpec = new ProductCategorySpecification(category);
        query = query.Where(categorySpec.ToExpression());
    }

    if (minPrice.HasValue && maxPrice.HasValue)
    {
        var priceSpec = new ProductPriceRangeSpecification(minPrice.Value, maxPrice.Value);
        query = query.Where(priceSpec.ToExpression());
    }

    if (!string.IsNullOrEmpty(searchTerm))
    {
        var searchSpec = new ProductNameContainsSpecification(searchTerm);
        query = query.Where(searchSpec.ToExpression());
    }

    // Apply ordering and pagination
    return await query
        .OrderBy(p => p.Name)
        .Skip(pageIndex * pageSize)
        .Take(pageSize)
        .ToListAsync(_connection);
}

Advanced Specification Patterns

Parameterized Specifications

Create specifications that accept parameters for flexible reuse:

public class ProductCreatedAfterSpecification : Specification<Product>
{
    private readonly DateTime _date;

    public ProductCreatedAfterSpecification(DateTime date)
    {
        _date = date;
    }

    public override Expression<Func<Product, bool>> ToExpression()
    {
        return product => product.CreatedDate > _date;
    }
}

// Usage
var recentSpec = new ProductCreatedAfterSpecification(DateTime.Now.AddDays(-7));
var recentProducts = await QueryBuilder.Query<Product>()
    .Where(recentSpec.ToExpression())
    .ToListAsync(_connection);

Composite Business Specifications

Encapsulate complex business rules:

public class PromotionEligibleProductSpecification : Specification<Product>
{
    public override Expression<Func<Product, bool>> ToExpression()
    {
        // Business rule: Promotion eligible products are active, in-stock, 
        // priced between $10-$1000, and not discontinued
        var activeSpec = new ActiveProductSpecification();
        var inStockSpec = new InStockProductSpecification();
        var priceSpec = new ProductPriceRangeSpecification(10, 1000);
        
        return activeSpec.And(inStockSpec).And(priceSpec).ToExpression();
    }
}

// Usage
var promoProducts = await QueryBuilder.Query<Product>()
    .Where(new PromotionEligibleProductSpecification().ToExpression())
    .OrderByDescending(p => p.Price)
    .Take(100)
    .ToListAsync(_connection);

Specifications with Complex Relationships

Handle relationships and joins with specifications:

public async Task<IEnumerable<Product>> GetProductsInActiveCategoriesAsync()
{
    var activeProductSpec = new ActiveProductSpecification();
    
    return await QueryBuilder.Query<Product>()
        .InnerJoin<Category>((p, c) => p.CategoryId == c.Id)
        .Where(activeProductSpec.ToExpression()) // Product must be active
        .Where(c => c.Active) // Category must be active
        .OrderBy(p => p.Name)
        .ToListAsync(_connection);
}

Testing Specifications

Specifications are highly testable in isolation:

[Test]
public void ActiveProductSpecification_ShouldReturnTrueForActiveProduct()
{
    // Arrange
    var specification = new ActiveProductSpecification();
    var activeProduct = new Product { Active = true };
    var inactiveProduct = new Product { Active = false };

    // Act & Assert
    Assert.IsTrue(specification.IsSatisfiedBy(activeProduct));
    Assert.IsFalse(specification.IsSatisfiedBy(inactiveProduct));
}

[Test]
public void PriceRangeSpecification_ShouldCombineWithOtherSpecs()
{
    // Arrange
    var priceSpec = new ProductPriceRangeSpecification(10, 100);
    var activeSpec = new ActiveProductSpecification();
    var combinedSpec = priceSpec.And(activeSpec);
    
    var product = new Product { Price = 50, Active = true };

    // Act & Assert
    Assert.IsTrue(combinedSpec.IsSatisfiedBy(product));
}

Benefits of QueryBuilder + Specification Pattern

1. 🔧 Reusability: Specifications can be reused across different queries
2. 📋 Testability: Business rules are isolated and easily unit testable
3. 🏗️ Composability: Complex queries built from simple, understandable parts
4. 📚 Readability: Business intent is clear from specification names
5. ⚡ Performance: Expressions translate to efficient SQL
6. 🔒 Type Safety: Compile-time checking of all query logic
7. 🔄 Maintainability: Business rule changes are centralized

This combination of QueryBuilder's fluent API with the Specification pattern creates a powerful, maintainable approach to data access that keeps business logic organized and testable.

Query Builder Feature Summary

Core Capabilities:

• ✅ Fluent API with method chaining
• ✅ Expression tree to SQL conversion
• ✅ Type-safe column references
• ✅ Parameterized queries (SQL injection safe)
• ✅ Multi-database dialect support (SQL Server, PostgreSQL, MySQL, SQLite)
• ✅ Specification Pattern Integration

WHERE Operations:

• ✅ Complex boolean expressions (AND, OR, NOT)
• ✅ IN and NOT IN clauses
• ✅ BETWEEN ranges
• ✅ NULL/NOT NULL checks
• ✅ Raw SQL conditions
• ✅ Specification-based filtering

JOIN Support:

• ✅ INNER, LEFT, and RIGHT joins
• ✅ Multiple joins in single query
• ✅ Type-safe join conditions
• ✅ Specifications with relationships

Aggregations:

• ✅ COUNT, SUM, AVG, MIN, MAX
• ✅ GROUP BY with multiple columns
• ✅ HAVING clauses

Pagination & Sorting:

• ✅ Skip/Take operations
• ✅ Page-based navigation
• ✅ OrderBy/ThenBy chaining
• ✅ Dialect-specific SQL generation

Execution:

• ✅ Async-first design
• ✅ Multiple result types (List, Single, First, Count, Any)
• ✅ SQL inspection before execution
• ✅ Raw SQL integration
• ✅ Repository pattern support

Stored Procedure Support

Noundry.Tuxedo provides comprehensive stored procedure support for SQL Server, MySQL, and PostgreSQL functions.

SQL Server / MySQL Stored Procedures

using Noundry.Tuxedo;

// ═══════════════════════════════════════════════════════════════════════════════
// EXECUTE PROCEDURE WITH RESULT SET
// ═══════════════════════════════════════════════════════════════════════════════

// Returns IEnumerable<T>
var products = await db.ExecuteProcedureAsync<Product>(
    "sp_GetProductsByCategory",
    new { CategoryId = 5, Active = true });

// Sync version
var productsSync = db.ExecuteProcedure<Product>(
    "sp_GetProductsByCategory",
    new { CategoryId = 5 });

// ═══════════════════════════════════════════════════════════════════════════════
// EXECUTE PROCEDURE WITH SINGLE RESULT
// ═══════════════════════════════════════════════════════════════════════════════

// Returns single T (throws if none)
var product = await db.ExecuteProcedureSingleAsync<Product>(
    "sp_GetProductById",
    new { ProductId = 123 });

// Sync version
var productSync = db.ExecuteProcedureSingle<Product>(
    "sp_GetProductById",
    new { ProductId = 123 });

// ═══════════════════════════════════════════════════════════════════════════════
// EXECUTE NON-QUERY PROCEDURE
// ═══════════════════════════════════════════════════════════════════════════════

// Returns rows affected
int rowsAffected = await db.ExecuteProcedureNonQueryAsync(
    "sp_UpdateProductPrices",
    new { CategoryId = 5, DiscountPercent = 10.0m });

// Sync version
int rowsSync = db.ExecuteProcedureNonQuery(
    "sp_ArchiveOldOrders",
    new { CutoffDate = DateTime.Today.AddYears(-1) });

// ═══════════════════════════════════════════════════════════════════════════════
// EXECUTE PROCEDURE WITH OUTPUT PARAMETERS
// ═══════════════════════════════════════════════════════════════════════════════

var parameters = new DynamicParameters();
parameters.Add("@CustomerId", 123);
parameters.Add("@TotalOrders", dbType: DbType.Int32, direction: ParameterDirection.Output);
parameters.Add("@TotalSpent", dbType: DbType.Decimal, direction: ParameterDirection.Output);

await db.ExecuteProcedureWithOutputAsync("sp_GetCustomerStats", parameters);

int totalOrders = parameters.Get<int>("@TotalOrders");
decimal totalSpent = parameters.Get<decimal>("@TotalSpent");
Console.WriteLine($"Customer has {totalOrders} orders totaling ${totalSpent}");

// Sync version
db.ExecuteProcedureWithOutput("sp_GetCustomerStats", parameters);

PostgreSQL Functions

PostgreSQL uses functions instead of stored procedures. Tuxedo provides dedicated methods:

// ═══════════════════════════════════════════════════════════════════════════════
// EXECUTE FUNCTION WITH TABLE RESULT (RETURNS TABLE)
// ═══════════════════════════════════════════════════════════════════════════════

// For functions that RETURNS TABLE or RETURNS SETOF
var products = await db.ExecutePostgresFunctionAsync<Product>(
    "get_products_by_category",
    new { p_category_id = 5 });

// Sync version
var productsSync = db.ExecutePostgresFunction<Product>(
    "get_products_by_category",
    new { p_category_id = 5 });

// ═══════════════════════════════════════════════════════════════════════════════
// EXECUTE FUNCTION WITH SCALAR RESULT
// ═══════════════════════════════════════════════════════════════════════════════

// For functions that return a single value
int productCount = await db.ExecutePostgresFunctionScalarAsync<int>(
    "count_products_in_category",
    new { p_category_id = 5 });

decimal totalRevenue = await db.ExecutePostgresFunctionScalarAsync<decimal>(
    "calculate_total_revenue",
    new { p_start_date = startDate, p_end_date = endDate });

// Sync versions
int countSync = db.ExecutePostgresFunctionScalar<int>("count_active_users", null);

Stored Procedure Method Reference

Method	Database	Returns	Description
ExecuteProcedure<T>	SQL Server/MySQL	IEnumerable<T>	Execute with result set
ExecuteProcedureAsync<T>	SQL Server/MySQL	Task<IEnumerable<T>>	Async result set
ExecuteProcedureSingle<T>	SQL Server/MySQL	T	Single result
ExecuteProcedureSingleAsync<T>	SQL Server/MySQL	Task<T>	Async single result
ExecuteProcedureNonQuery	SQL Server/MySQL	int	Rows affected
ExecuteProcedureNonQueryAsync	SQL Server/MySQL	Task<int>	Async rows affected
ExecuteProcedureWithOutput	SQL Server/MySQL	int	With output params
ExecuteProcedureWithOutputAsync	SQL Server/MySQL	Task<int>	Async output params
ExecutePostgresFunction<T>	PostgreSQL	IEnumerable<T>	Function table result
ExecutePostgresFunctionAsync<T>	PostgreSQL	Task<IEnumerable<T>>	Async table result
ExecutePostgresFunctionScalar<T>	PostgreSQL	T	Function scalar result
ExecutePostgresFunctionScalarAsync<T>	PostgreSQL	Task<T>	Async scalar result

Complete Service Example with Stored Procedures

public class ReportService
{
    private readonly IDbConnection _db;

    public ReportService(IDbConnection db) => _db = db;

    // SQL Server stored procedure
    public async Task<SalesReport> GetSalesReportAsync(DateTime startDate, DateTime endDate)
    {
        var parameters = new DynamicParameters();
        parameters.Add("@StartDate", startDate);
        parameters.Add("@EndDate", endDate);
        parameters.Add("@TotalSales", dbType: DbType.Decimal, direction: ParameterDirection.Output);
        parameters.Add("@OrderCount", dbType: DbType.Int32, direction: ParameterDirection.Output);

        var details = await _db.ExecuteProcedureAsync<SalesDetail>(
            "sp_GenerateSalesReport", parameters);

        return new SalesReport
        {
            Details = details.ToList(),
            TotalSales = parameters.Get<decimal>("@TotalSales"),
            OrderCount = parameters.Get<int>("@OrderCount")
        };
    }

    // PostgreSQL function
    public async Task<IEnumerable<InventoryItem>> GetLowStockItemsAsync(int threshold)
    {
        return await _db.ExecutePostgresFunctionAsync<InventoryItem>(
            "get_low_stock_items",
            new { p_threshold = threshold });
    }
}

Resiliency and Fault Tolerance

Tuxedo provides enterprise-grade resiliency features powered by Polly, including automatic retries, circuit breakers, and timeout handling for transient database failures.

Basic Resiliency Setup

using Noundry.Tuxedo.Resiliency;
using Noundry.Tuxedo.DependencyInjection;

// Method 1: Configure with options
services.AddTuxedoResiliency(options =>
{
    options.MaxRetryAttempts = 3;
    options.BaseDelay = TimeSpan.FromSeconds(1);
    options.EnableCircuitBreaker = true;
    options.CircuitBreakerThreshold = 5;
    options.CircuitBreakerTimeout = TimeSpan.FromSeconds(30);
});

// Method 2: Configure from appsettings.json
services.AddTuxedoResiliency(configuration, "TuxedoResiliency");

// Method 3: Use default settings (3 retries with exponential backoff)
services.AddTuxedoResiliency();

Configuration Options

// appsettings.json
{
  "TuxedoResiliency": {
    "MaxRetryAttempts": 3,
    "BaseDelay": "00:00:01",
    "EnableCircuitBreaker": true,
    "CircuitBreakerThreshold": 5,
    "CircuitBreakerTimeout": "00:00:30"
  }
}

Automatic Resiliency with Dependency Injection

When resiliency is configured, all IDbConnection instances are automatically wrapped with retry policies:

// Configure services
services.AddTuxedoSqlServer(connectionString);
services.AddTuxedoResiliency(options =>
{
    options.MaxRetryAttempts = 3;
    options.BaseDelay = TimeSpan.FromMilliseconds(500);
});

// Use in your service - retries are automatic
public class ProductService
{
    private readonly IDbConnection _db;
    
    public ProductService(IDbConnection db) => _db = db;
    
    public async Task<Product> GetProductAsync(int id)
    {
        // This query will automatically retry on transient failures
        return await _db.GetAsync<Product>(id);
    }
    
    public async Task<IEnumerable<Product>> SearchProductsAsync(string term)
    {
        // Complex queries also benefit from automatic retries
        return await _db.QueryAsync<Product>(
            "SELECT * FROM Products WHERE Name LIKE @term",
            new { term = $"%{term}%" });
    }
}

Manual Retry Execution

For fine-grained control, use the extension methods directly:

// Async with retry
var products = await connection.ExecuteWithRetryAsync(
    async conn => await conn.QueryAsync<Product>("SELECT * FROM Products"),
    retryPolicy: new ExponentialBackoffRetryPolicy(maxAttempts: 5)
);

// Sync with retry
var count = connection.ExecuteWithRetry(
    conn => conn.ExecuteScalar<int>("SELECT COUNT(*) FROM Products"),
    retryPolicy: new ExponentialBackoffRetryPolicy()
);

// Custom retry logic for specific operations
var result = await connection.ExecuteWithRetryAsync(
    async conn =>
    {
        using var transaction = conn.BeginTransaction();
        try
        {
            await conn.ExecuteAsync("UPDATE Inventory SET Quantity = Quantity - @qty WHERE ProductId = @id",
                new { qty = 1, id = productId }, transaction);
            
            await conn.ExecuteAsync("INSERT INTO Orders (ProductId, Quantity) VALUES (@id, @qty)",
                new { id = productId, qty = 1 }, transaction);
            
            transaction.Commit();
            return true;
        }
        catch
        {
            transaction.Rollback();
            throw;
        }
    }
);

Using Polly Provider Directly

For advanced scenarios, use the PollyResiliencyProvider with full Polly features:

// Register with DI
services.AddSingleton<IResiliencyProvider, PollyResiliencyProvider>();

// Use in your code
public class ResilientRepository
{
    private readonly IDbConnection _connection;
    private readonly IResiliencyProvider _resiliency;
    
    public ResilientRepository(IDbConnection connection, IResiliencyProvider resiliency)
    {
        _connection = connection;
        _resiliency = resiliency;
    }
    
    public async Task<T> GetWithResiliencyAsync<T>(int id) where T : class
    {
        // Wrap connection with resiliency
        using var resilientConnection = _resiliency.WrapConnection(_connection);
        
        // All operations through this connection have retry/circuit breaker
        return await resilientConnection.GetAsync<T>(id);
    }
    
    public async Task<IEnumerable<T>> QueryWithFallbackAsync<T>(string sql, object param)
    {
        try
        {
            // Primary query with resiliency
            return await _resiliency.ExecuteAsync(
                async () => await _connection.QueryAsync<T>(sql, param)
            );
        }
        catch (Exception ex) when (IsCircuitOpen(ex))
        {
            // Fallback to cache or alternative data source
            return GetFromCache<T>() ?? Enumerable.Empty<T>();
        }
    }
}

Transient Error Detection

Tuxedo automatically detects and retries transient errors for all major databases:

SQL Server Transient Errors:

• Connection timeouts (Error: -2, 121)
• Deadlocks (Error: 1205)
• Database unavailable (Error: 4060)
• Resource throttling (Error: 49918, 49919, 49920)
• Network issues (Error: 10053, 10054, 10060, 10061)

PostgreSQL Transient Errors:

• Connection failures (SQLSTATE: 08000, 08001, 08003, 08004, 08006)
• Serialization failures (SQLSTATE: 40001, 40P01)
• System errors (SQLSTATE: 57P01, 57P02, 57P03, 58000, 58030)

MySQL Transient Errors:

• Deadlocks (Error: 1213)
• Lock wait timeouts (Error: 1205)
• Too many connections (Error: 1040, 1041)
• Lost connection (Error: 2002, 2003, 2006, 2013)

Circuit Breaker Pattern

Prevent cascading failures with circuit breakers:

services.AddTuxedoResiliency(options =>
{
    options.EnableCircuitBreaker = true;
    options.CircuitBreakerThreshold = 5;        // Open after 5 consecutive failures
    options.CircuitBreakerTimeout = TimeSpan.FromSeconds(30); // Try again after 30 seconds
});

// Circuit breaker states:
// - Closed: Normal operation, requests pass through
// - Open: Requests fail immediately without attempting database call
// - Half-Open: Test with single request to check if service recovered

// Monitor circuit breaker state
public class HealthCheckService
{
    private readonly IResiliencyProvider _resiliency;
    private readonly ILogger<HealthCheckService> _logger;
    
    public async Task<bool> CheckDatabaseHealthAsync()
    {
        try
        {
            await _resiliency.ExecuteAsync(async () =>
            {
                using var conn = new SqlConnection(connectionString);
                await conn.OpenAsync();
                await conn.ExecuteScalarAsync("SELECT 1");
            });
            
            _logger.LogInformation("Database is healthy");
            return true;
        }
        catch (CircuitBreakerOpenException)
        {
            _logger.LogWarning("Circuit breaker is open - database is unhealthy");
            return false;
        }
    }
}

Combining with Other Patterns

// Resiliency + Repository Pattern
public class ResilientProductRepository : IProductRepository
{
    private readonly IDbConnection _connection;
    
    public ResilientProductRepository(IDbConnection connection)
    {
        // Connection is already wrapped with resiliency from DI
        _connection = connection;
    }
    
    public async Task<Product?> GetByIdAsync(int id)
    {
        // Automatic retry on transient failures
        return await _connection.GetAsync<Product>(id);
    }
    
    public async Task<IEnumerable<Product>> GetByCategoryAsync(string category)
    {
        // Query builder with automatic resiliency
        return await QueryBuilder.Query<Product>()
            .Where(p => p.Category == category)
            .OrderBy(p => p.Name)
            .ToListAsync(_connection);
    }
}

// Resiliency + Unit of Work
public class ResilientUnitOfWork : IUnitOfWork
{
    private readonly IDbConnection _connection;
    private IDbTransaction? _transaction;
    
    public async Task<bool> ExecuteInTransactionAsync(Func<Task> operation)
    {
        // Connection open will retry on failure
        if (_connection.State != ConnectionState.Open)
            _connection.Open();
        
        _transaction = _connection.BeginTransaction();
        
        try
        {
            await operation();
            _transaction.Commit();
            return true;
        }
        catch (Exception ex) when (IsTransient(ex))
        {
            _transaction.Rollback();
            // Let retry policy handle it
            throw;
        }
        catch
        {
            _transaction.Rollback();
            return false;
        }
    }
}

Testing with Resiliency

[Fact]
public async Task Should_Retry_On_Transient_Failure()
{
    var attempts = 0;
    var mockConnection = new Mock<IDbConnection>();
    
    // Fail twice, then succeed
    mockConnection
        .Setup(c => c.ExecuteScalar(It.IsAny<string>(), It.IsAny<object>()))
        .Returns(() =>
        {
            attempts++;
            if (attempts < 3)
                throw new SqlException("Timeout expired");
            return 42;
        });
    
    var resilientConnection = new ResilientDbConnection(
        mockConnection.Object,
        new ExponentialBackoffRetryPolicy(maxAttempts: 3));
    
    var result = resilientConnection.ExecuteScalar<int>("SELECT COUNT(*) FROM Products");
    
    Assert.Equal(42, result);
    Assert.Equal(3, attempts); // Should have retried twice
}

Resiliency Best Practices

1. Configure Appropriate Delays: Use exponential backoff to avoid overwhelming the database
2. Set Reasonable Retry Limits: 3-5 retries are usually sufficient
3. Enable Circuit Breakers: Prevent cascading failures in microservices
4. Log Retry Attempts: Monitor and alert on excessive retries
5. Test Failure Scenarios: Ensure your application handles database outages gracefully
6. Use Idempotent Operations: Ensure retried operations don't cause data inconsistencies
7. Combine with Caching: Use cache as fallback when circuit breaker is open

Exception Translation

🎯 NEW: Noundry.Tuxedo.Exceptions - Strongly-Typed Database Exceptions Transform generic database errors into specific, catchable exceptions with detailed constraint information. Supports SQL Server, PostgreSQL, MySQL, and SQLite.

Tuxedo.Exceptions translates generic DbException errors into strongly-typed exceptions that provide meaningful context about what went wrong, making error handling cleaner and more precise.

Installation

dotnet add package Noundry.Tuxedo.Exceptions

Exception Types

Tuxedo.Exceptions provides five strongly-typed exception classes:

• UniqueConstraintException - Unique constraint or primary key violations
• ReferenceConstraintException - Foreign key constraint violations
• CannotInsertNullException - NULL value in NOT NULL column
• MaxLengthExceededException - String/binary data exceeds maximum length
• NumericOverflowException - Numeric value out of range

Each exception includes detailed context:

try
{
    await connection.Execute("INSERT INTO Users (Email) VALUES (@Email)",
        new { Email = "duplicate@example.com" });
}
catch (UniqueConstraintException ex)
{
    Console.WriteLine($"Constraint: {ex.ConstraintName}");
    Console.WriteLine($"Properties: {string.Join(", ", ex.ConstraintProperties)}");
    Console.WriteLine($"Message: {ex.Message}");
}

Basic Usage

Wrap your connection with exception translation using the pipeline pattern:

using Noundry.Tuxedo.Exceptions;

// Auto-detect database provider
var connection = new SqliteConnection(connectionString)
    .WithExceptionTranslation();

// Or specify explicitly for better performance
var sqlConn = new SqlConnection(connectionString)
    .WithSqlServerExceptionTranslation();

var pgConn = new NpgsqlConnection(connectionString)
    .WithPostgreSqlExceptionTranslation();

var mysqlConn = new MySqlConnection(connectionString)
    .WithMySqlExceptionTranslation();

Pipeline Pattern - Combine with Resiliency

Exception translation integrates seamlessly with resiliency features:

using Noundry.Tuxedo.Resiliency;
using Noundry.Tuxedo.Exceptions;

// Exception translation + Retry policy
var connection = new SqlConnection(connectionString)
    .WithExceptionTranslation()
    .WithRetryPolicy(retryPolicy);

// Order doesn't matter - both work!
var connection2 = new SqlConnection(connectionString)
    .WithRetryPolicy(retryPolicy)
    .WithExceptionTranslation();

Comprehensive Error Handling

Handle specific database errors with precision:

public async Task<Result> CreateUserAsync(User user)
{
    try
    {
        var wrappedConn = _connection.WithExceptionTranslation();
        var id = await wrappedConn.InsertAsync(user);
        return Result.Success(id);
    }
    catch (UniqueConstraintException ex) when (ex.ConstraintName?.Contains("Email") == true)
    {
        return Result.Failure("Email address already exists");
    }
    catch (UniqueConstraintException ex)
    {
        return Result.Failure($"Duplicate value in {ex.ConstraintName}");
    }
    catch (CannotInsertNullException ex)
    {
        return Result.Failure($"Missing required field: {ex.ColumnName}");
    }
    catch (MaxLengthExceededException ex)
    {
        return Result.Failure($"Value too long for {ex.ColumnName}");
    }
    catch (ReferenceConstraintException ex)
    {
        return Result.Failure($"Invalid reference: {ex.ConstraintName}");
    }
}

Repository Pattern Integration

Use in repositories for clean error handling:

public class ProductRepository
{
    private readonly IDbConnection _connection;

    public ProductRepository(IDbConnection connection)
    {
        // Wrap connection once at construction
        _connection = connection.WithExceptionTranslation();
    }

    public async Task<Product?> CreateProductAsync(Product product)
    {
        try
        {
            var id = await _connection.InsertAsync(product);
            product.Id = id;
            return product;
        }
        catch (UniqueConstraintException)
        {
            // Product with same SKU already exists
            return null;
        }
        catch (ReferenceConstraintException ex) when (ex.ConstraintName?.Contains("Category") == true)
        {
            throw new InvalidOperationException("Invalid category ID", ex);
        }
    }

    public async Task<bool> UpdateProductPriceAsync(int id, decimal newPrice)
    {
        try
        {
            return await _connection.UpdateAsync<Product>(
                keyValues: new { Id = id },
                updateValues: new { Price = newPrice }
            );
        }
        catch (NumericOverflowException)
        {
            throw new ArgumentException("Price value is out of range");
        }
    }
}

Dependency Injection

Register wrapped connections in DI:

using Noundry.Tuxedo.DependencyInjection;
using Noundry.Tuxedo.Exceptions;

// Option 1: Wrap at registration
services.AddScoped<IDbConnection>(sp =>
{
    var conn = new SqlConnection(connectionString);
    return conn.WithSqlServerExceptionTranslation();
});

// Option 2: Factory pattern
services.AddScoped<IDbConnection>(sp =>
{
    var connectionString = sp.GetRequiredService<IConfiguration>()
        .GetConnectionString("DefaultConnection");
    var conn = new SqlConnection(connectionString);
    return conn.WithExceptionTranslation(); // Auto-detect
});

// Option 3: Combine with existing Tuxedo DI helpers
services.AddTuxedoSqlServer(connectionString);
services.AddScoped<IDbConnection>(sp =>
{
    var baseConn = sp.GetRequiredService<IDbConnection>();
    return baseConn.WithSqlServerExceptionTranslation();
});

Error Code Mappings

Tuxedo.Exceptions automatically detects errors based on database-specific error codes:

SQL Server Error Codes

Error Code	Exception Type
2601, 2627	UniqueConstraintException
547	ReferenceConstraintException
515	CannotInsertNullException
2628, 8152	MaxLengthExceededException
8115	NumericOverflowException

PostgreSQL SQLSTATE Codes

SQLSTATE	Exception Type
23505	UniqueConstraintException
23503	ReferenceConstraintException
23502	CannotInsertNullException
22001	MaxLengthExceededException
22003	NumericOverflowException

MySQL Error Codes

Error Code	Exception Type
1062	UniqueConstraintException
1452	ReferenceConstraintException
1048	CannotInsertNullException
1406	MaxLengthExceededException
1264	NumericOverflowException

SQLite Error Codes

Error Pattern	Exception Type
Error 19 + "UNIQUE"	UniqueConstraintException
Error 19 + "FOREIGN KEY"	ReferenceConstraintException
Error 19 + "NOT NULL"	CannotInsertNullException
Error 18	MaxLengthExceededException

Advanced Scenarios

User-Friendly Error Messages

Transform technical errors into user-friendly messages:

public class ErrorMessageMapper
{
    public string GetUserMessage(Exception ex)
    {
        return ex switch
        {
            UniqueConstraintException unique => unique.ConstraintName switch
            {
                var c when c?.Contains("Email") == true => "This email address is already registered",
                var c when c?.Contains("Username") == true => "This username is already taken",
                var c when c?.Contains("SKU") == true => "A product with this SKU already exists",
                _ => "This value already exists in the system"
            },
            ReferenceConstraintException => "The referenced item does not exist or has been deleted",
            CannotInsertNullException nullEx => $"{nullEx.ColumnName} is required",
            MaxLengthExceededException lengthEx => $"{lengthEx.ColumnName} is too long",
            NumericOverflowException => "The value provided is too large",
            _ => "An unexpected error occurred"
        };
    }
}

Validation with Exception Translation

Combine with validation for comprehensive error handling:

public async Task<ValidationResult> SaveProductAsync(Product product)
{
    var errors = new List<string>();

    // Business validation
    if (product.Price <= 0)
        errors.Add("Price must be greater than zero");

    if (string.IsNullOrWhiteSpace(product.Name))
        errors.Add("Name is required");

    if (errors.Any())
        return ValidationResult.Failed(errors);

    try
    {
        var wrappedConn = _connection.WithExceptionTranslation();
        await wrappedConn.InsertAsync(product);
        return ValidationResult.Success();
    }
    catch (UniqueConstraintException ex)
    {
        errors.Add($"Duplicate {ex.ConstraintName}");
        return ValidationResult.Failed(errors);
    }
    catch (MaxLengthExceededException ex)
    {
        errors.Add($"{ex.ColumnName} exceeds maximum length");
        return ValidationResult.Failed(errors);
    }
}

Logging and Monitoring

Track database constraint violations:

public class DatabaseErrorLogger
{
    private readonly ILogger _logger;
    private readonly IMetrics _metrics;

    public async Task<T> ExecuteWithLoggingAsync<T>(Func<Task<T>> operation, string operationName)
    {
        try
        {
            return await operation();
        }
        catch (UniqueConstraintException ex)
        {
            _logger.LogWarning("Unique constraint violation in {Operation}: {Constraint}",
                operationName, ex.ConstraintName);
            _metrics.Increment("db.constraint.unique_violation");
            throw;
        }
        catch (ReferenceConstraintException ex)
        {
            _logger.LogWarning("Foreign key violation in {Operation}: {Constraint}",
                operationName, ex.ConstraintName);
            _metrics.Increment("db.constraint.foreign_key_violation");
            throw;
        }
        catch (CannotInsertNullException ex)
        {
            _logger.LogWarning("NULL constraint violation in {Operation}: {Column}",
                operationName, ex.ColumnName);
            _metrics.Increment("db.constraint.null_violation");
            throw;
        }
    }
}

Exception Translation Architecture

Tuxedo.Exceptions uses a decorator pattern that wraps database connections and commands:

1. ExceptionTranslatingDbConnection - Wraps IDbConnection
2. ExceptionTranslatingDbCommand - Wraps IDbCommand
3. IDbExceptionClassifier - Database-specific exception detection
4. Exception Classes - Strongly-typed exceptions with metadata

The wrapper is transparent - all Tuxedo and Dapper methods work normally:

var wrappedConn = connection.WithExceptionTranslation();

// All these work with exception translation
await wrappedConn.Execute("INSERT INTO...");
await wrappedConn.Query<T>("SELECT...");
await wrappedConn.InsertAsync(entity);
await wrappedConn.UpdateAsync(entity);
await wrappedConn.GetAsync<T>(id);

// Works with QueryBuilder
var results = await QueryBuilder.Query<Product>()
    .Where(p => p.Active)
    .ToListAsync(wrappedConn);

Testing with Exception Translation

Write precise unit tests for error scenarios:

[Test]
public async Task CreateUser_DuplicateEmail_ReturnsError()
{
    // Arrange
    var connection = new SqliteConnection("Data Source=:memory:")
        .WithSQLiteExceptionTranslation();

    await connection.Execute(@"
        CREATE TABLE Users (
            Id INTEGER PRIMARY KEY,
            Email TEXT UNIQUE NOT NULL
        )");

    await connection.Execute("INSERT INTO Users (Email) VALUES ('test@example.com')");

    // Act & Assert
    var exception = Assert.ThrowsAsync<UniqueConstraintException>(() =>
        connection.Execute("INSERT INTO Users (Email) VALUES ('test@example.com')")
    );

    Assert.That(exception.Message, Does.Contain("UNIQUE"));
}

Best Practices

1. Wrap Early: Wrap connections at the outermost layer (DI, repository constructor)
2. Catch Specifically: Use specific exception types rather than broad catches
3. Extract Context: Use exception properties (ConstraintName, ColumnName) for better error messages
4. Combine Features: Use with resiliency and caching for robust data access
5. Log Patterns: Track which constraints are violated most frequently
6. User Messages: Map technical exceptions to user-friendly messages
7. Optional Usage: Exception translation is completely optional and doesn't affect existing code

Performance Considerations

Exception translation has minimal overhead:

• Zero cost when no exceptions occur (normal execution path)
• Adds ~0.1ms when exceptions are thrown (during translation)
• No memory overhead (thin wrapper classes)
• Compatible with connection pooling
• Works with all async/sync Tuxedo methods

📖 Complete Documentation: See Tuxedo.Exceptions/README.md for the full API reference and advanced scenarios.

Audit Logging

Tuxedo.Auditor provides automatic change tracking for Insert, Update, and Delete operations. Every CRUD operation creates a detailed audit log entry with old/new values, changed columns, user information, and timestamps.

Installation

dotnet add package Noundry.Tuxedo.Auditor

Audit Table Structure

The auditor automatically creates an audit_log table with the following schema:

Column	Type	Description
id	INT/BIGINT	Primary key (auto-increment)
table_name	VARCHAR(255)	Name of the audited table
event_type	VARCHAR(50)	Insert, Update, Delete, or SoftDelete
entity_id	VARCHAR(255)	Primary key value of the audited entity
username	VARCHAR(255)	User who performed the action
timestamp	DATETIME/TIMESTAMP	When the action occurred
old_values	JSON/JSONB/TEXT	Previous entity state (JSON)
new_values	JSON/JSONB/TEXT	New entity state (JSON)
changed_columns	TEXT	Comma-separated list of changed columns
success	BOOLEAN/BIT	Whether operation succeeded
error_message	TEXT	Error details if operation failed

Basic Usage

using Noundry.Tuxedo.Auditor;
using Noundry.Tuxedo.Auditor.UserProviders;

// Wrap connection with auditing
var auditedConnection = connection.WithAuditing(config =>
{
    config.UserProvider = new StaticAuditUserProvider("admin");
});

// All CRUD operations are automatically audited
var userId = await auditedConnection.InsertAsync(new User
{
    Email = "user@example.com",
    Name = "John Doe"
});
// Audit: event_type='Insert', new_values contains JSON of user

// Update with changed column detection
var user = await connection.GetAsync<User>(userId);
user.Email = "newemail@example.com";
await auditedConnection.UpdateAsync(user);
// Audit: event_type='Update', changed_columns='Email', old/new values stored

// Delete with audit trail
await auditedConnection.DeleteAsync(user);
// Audit: event_type='Delete', old_values contains JSON of deleted user

User Providers

Choose the appropriate user provider for your application:

// Static user (testing, background jobs)
config.UserProvider = new StaticAuditUserProvider("system");

// Delegate user (custom logic)
config.UserProvider = new DelegateAuditUserProvider(() =>
{
    return Thread.CurrentPrincipal?.Identity?.Name ?? "Anonymous";
});

// HTTP context user (ASP.NET Core - requires IHttpContextAccessor)
config.UserProvider = new HttpContextAuditUserProvider(httpContextAccessor);

Configuration Options

var auditedConnection = connection.WithAuditing(config =>
{
    // Required: User provider
    config.UserProvider = new StaticAuditUserProvider("admin");

    // Optional: Custom audit table name (default: "audit_log")
    config.AuditTableName = "my_audit_log";

    // Optional: Auto-create audit table if it doesn't exist (default: true)
    config.AutoCreateAuditTable = true;

    // Optional: Only audit specific tables
    config.IncludedTables = new[] { "Users", "Products", "Orders" };

    // Optional: Exclude specific tables from auditing
    config.ExcludedTables = new[] { "Logs", "TempData" };
});

Soft Delete Detection

The auditor automatically detects soft deletes:

public class User
{
    [Key]
    public int Id { get; set; }
    public string Email { get; set; }
    public bool IsDeleted { get; set; }
    public DateTime? DeletedAt { get; set; }
}

// Regular update
user.Email = "new@example.com";
await auditedConnection.UpdateAsync(user);
// Audit: event_type='Update', changed_columns='Email'

// Soft delete (IsDeleted=true + DeletedAt set)
user.IsDeleted = true;
user.DeletedAt = DateTime.UtcNow;
await auditedConnection.UpdateAsync(user);
// Audit: event_type='SoftDelete', changed_columns='IsDeleted,DeletedAt'

Pipeline Pattern - Combine with Other Features

using Noundry.Tuxedo.Auditor;
using Noundry.Tuxedo.Exceptions;
using Noundry.Tuxedo.Resiliency;

// Combine auditing with exception translation and retry policy
var connection = new SqlConnection(connectionString)
    .WithAuditing(config => config.UserProvider = new StaticAuditUserProvider("admin"))
    .WithExceptionTranslation()
    .WithRetryPolicy(retryPolicy);

// Now you have:
// - Automatic audit logging
// - Strongly-typed exceptions
// - Automatic retry on transient failures

Querying Audit Logs

// Get all audit logs for a table
var userAudits = connection.Query<dynamic>(
    @"SELECT * FROM audit_log
      WHERE table_name = @Table
      ORDER BY timestamp DESC",
    new { Table = "Users" }
);

// Get audit history for a specific entity
var entityHistory = connection.Query<dynamic>(
    @"SELECT * FROM audit_log
      WHERE table_name = @Table AND entity_id = @Id
      ORDER BY timestamp DESC",
    new { Table = "Users", Id = "123" }
);

// Get all changes by a user
var userChanges = connection.Query<dynamic>(
    "SELECT * FROM audit_log WHERE username = @User",
    new { User = "admin" }
);

// Get failed operations
var failures = connection.Query<dynamic>(
    "SELECT * FROM audit_log WHERE success = 0"
);

Database-Specific JSON Storage

Tuxedo.Auditor uses optimal JSON storage for each database:

Database	JSON Type	Benefits
PostgreSQL	JSONB	Binary format, indexable, efficient queries
SQL Server	NVARCHAR(MAX)	Standard JSON support with JSON_VALUE()
MySQL	JSON	Native JSON type with path queries
SQLite	TEXT	Stores JSON as text, parseable with json_extract()

Dependency Injection with Auditing

using Microsoft.Extensions.DependencyInjection;
using Noundry.Tuxedo.Auditor;
using Noundry.Tuxedo.Auditor.UserProviders;

// Register HTTP context accessor
services.AddHttpContextAccessor();

// Register audited connection
services.AddScoped<IDbConnection>(sp =>
{
    var httpContext = sp.GetRequiredService<IHttpContextAccessor>();
    var connection = new SqlConnection(connectionString);

    return connection.WithAuditing(config =>
    {
        config.UserProvider = new HttpContextAuditUserProvider(httpContext);
        config.IncludedTables = new[] { "Users", "Products", "Orders" };
    });
});

Repository Pattern with Auditing

public class AuditedUserRepository
{
    private readonly AuditedConnection _connection;

    public AuditedUserRepository(IDbConnection connection)
    {
        // Wrap connection with auditing
        _connection = connection.WithAuditing(config =>
        {
            config.UserProvider = new StaticAuditUserProvider("system");
        }) as AuditedConnection;
    }

    public async Task<int> CreateUserAsync(User user)
    {
        // Automatically audited
        return await _connection.InsertAsync(user);
    }

    public async Task UpdateUserAsync(User user)
    {
        // Changed columns tracked automatically
        await _connection.UpdateAsync(user);
    }

    public async Task<IEnumerable<dynamic>> GetUserAuditHistoryAsync(int userId)
    {
        return _connection.InnerConnection.Query<dynamic>(
            @"SELECT * FROM audit_log
              WHERE table_name = 'Users' AND entity_id = @Id
              ORDER BY timestamp DESC",
            new { Id = userId.ToString() }
        );
    }
}

Advanced Scenarios

Audit Log Analysis

// Find who made the most changes
var topUsers = connection.Query<dynamic>(
    @"SELECT username, COUNT(*) as change_count
      FROM audit_log
      WHERE timestamp >= @Since
      GROUP BY username
      ORDER BY change_count DESC
      LIMIT 10",
    new { Since = DateTime.UtcNow.AddDays(-30) }
);

// Find tables with most activity
var activeTable = connection.Query<dynamic>(
    @"SELECT table_name, COUNT(*) as event_count
      FROM audit_log
      WHERE timestamp >= @Since
      GROUP BY table_name
      ORDER BY event_count DESC",
    new { Since = DateTime.UtcNow.AddDays(-7) }
);

// Detect suspicious patterns (many deletes)
var suspiciousActivity = connection.Query<dynamic>(
    @"SELECT username, COUNT(*) as delete_count
      FROM audit_log
      WHERE event_type = 'Delete'
        AND timestamp >= @Since
      GROUP BY username
      HAVING COUNT(*) > 100",
    new { Since = DateTime.UtcNow.AddHours(-1) }
);

Audit Log Retention

// Archive old audit logs (older than 1 year)
await connection.ExecuteAsync(
    @"INSERT INTO audit_log_archive
      SELECT * FROM audit_log
      WHERE timestamp < @CutoffDate",
    new { CutoffDate = DateTime.UtcNow.AddYears(-1) }
);

await connection.ExecuteAsync(
    "DELETE FROM audit_log WHERE timestamp < @CutoffDate",
    new { CutoffDate = DateTime.UtcNow.AddYears(-1) }
);

Custom Audit Queries (PostgreSQL with JSONB)

// Find all email changes in PostgreSQL
var emailChanges = connection.Query<dynamic>(
    @"SELECT
        entity_id,
        timestamp,
        old_values->>'Email' as old_email,
        new_values->>'Email' as new_email
      FROM audit_log
      WHERE table_name = 'Users'
        AND changed_columns LIKE '%Email%'
      ORDER BY timestamp DESC"
);

Performance Considerations

• Sequential writes: Each CRUD operation writes one audit record (~1-5ms overhead)
• JSON serialization: Entity serialization typically < 1ms
• Changed column detection: Requires reading old values for updates
• Index recommendations: Add indexes on table_name, entity_id, username, timestamp
• JSONB performance: PostgreSQL JSONB provides best query performance for JSON data
• Archive old data: Implement retention policy to keep audit table performant

Best Practices

1. Use table filtering - Only audit tables that require tracking using IncludedTables or ExcludedTables
2. Choose appropriate user provider - Use HttpContextAuditUserProvider for web apps, DelegateAuditUserProvider for custom scenarios
3. Index audit tables - Add indexes on frequently queried columns
4. Implement retention policy - Archive or purge old audit logs to manage table size
5. Monitor performance - Measure impact in production and adjust table filtering
6. Use soft deletes - Prefer soft deletes to maintain referential integrity
7. Review audit logs regularly - Set up automated reports for security and compliance

📖 Complete Documentation: See Tuxedo.Auditor/README.md for the full API reference and detailed examples.

Bulk Operations

Tuxedo provides high-performance bulk operations for efficiently handling large datasets with minimal round trips to the database.

Bulk Insert

Insert thousands of records in optimized batches:

using Noundry.Tuxedo.BulkOperations;

// Create bulk operations instance
var bulkOps = new BulkOperations(TuxedoDialect.SqlServer);

// Insert large dataset
var products = GenerateProducts(10000); // 10,000 products
var inserted = await bulkOps.BulkInsertAsync(
    connection,
    products,
    tableName: "Products",
    batchSize: 1000  // Process in batches of 1000
);

Console.WriteLine($"Inserted {inserted} products");

// Using with dependency injection
services.AddSingleton<IBulkOperations>(provider =>
{
    var dialect = TuxedoDialect.SqlServer; // Or from configuration
    return new BulkOperations(dialect);
});

Bulk Update

Update multiple records efficiently:

// Update pricing for all products in a category
var productsToUpdate = await connection.QueryAsync<Product>(
    "SELECT * FROM Products WHERE Category = @category",
    new { category = "Electronics" });

// Apply 10% discount
foreach (var product in productsToUpdate)
{
    product.Price *= 0.9m;
    product.LastModified = DateTime.Now;
}

var updated = await bulkOps.BulkUpdateAsync(
    connection,
    productsToUpdate,
    batchSize: 500
);

Console.WriteLine($"Updated {updated} products with discount");

Bulk Delete

Remove multiple records in optimized batches:

// Delete discontinued products
var discontinuedProducts = await connection.QueryAsync<Product>(
    "SELECT * FROM Products WHERE Discontinued = 1");

var deleted = await bulkOps.BulkDeleteAsync(
    connection,
    discontinuedProducts,
    batchSize: 1000
);

Console.WriteLine($"Deleted {deleted} discontinued products");

// Or by IDs
var idsToDelete = new[] { 1, 2, 3, 4, 5 };
var productsToDelete = idsToDelete.Select(id => new Product { Id = id });

await bulkOps.BulkDeleteAsync(connection, productsToDelete);

Bulk Merge (Upsert)

Insert or update records based on key matching:

// Merge product catalog from external source
var externalProducts = await FetchExternalCatalog();

var merged = await bulkOps.BulkMergeAsync(
    connection,
    externalProducts,
    tableName: "Products",
    batchSize: 1000
);

Console.WriteLine($"Merged {merged} products (inserted or updated)");

// Database-specific UPSERT operations:
// - SQL Server: Uses MERGE statement
// - PostgreSQL/SQLite: Uses INSERT ... ON CONFLICT
// - MySQL: Uses INSERT ... ON DUPLICATE KEY UPDATE

Advanced Bulk Operations

// Bulk operations with transaction
using var transaction = connection.BeginTransaction();
try
{
    // Import new products
    await bulkOps.BulkInsertAsync(
        connection,
        newProducts,
        transaction: transaction,
        batchSize: 1000
    );
    
    // Update existing products
    await bulkOps.BulkUpdateAsync(
        connection,
        updatedProducts,
        transaction: transaction,
        batchSize: 1000
    );
    
    // Remove old products
    await bulkOps.BulkDeleteAsync(
        connection,
        oldProducts,
        transaction: transaction,
        batchSize: 1000
    );
    
    transaction.Commit();
}
catch
{
    transaction.Rollback();
    throw;
}

// Async with cancellation
var cts = new CancellationTokenSource();
cts.CancelAfter(TimeSpan.FromMinutes(5));

var result = await bulkOps.BulkInsertAsync(
    connection,
    largeDataset,
    batchSize: 2000,
    commandTimeout: 120,
    cancellationToken: cts.Token
);

// Custom table names
await bulkOps.BulkInsertAsync(
    connection,
    temporaryProducts,
    tableName: "Products_Staging",
    batchSize: 5000
);

Performance Considerations

Bulk Operations Performance Guide:

Records	Single Insert	Bulk Insert	Improvement
100	~500ms	~50ms	10x faster
1,000	~5,000ms	~200ms	25x faster
10,000	~50,000ms	~1,000ms	50x faster
100,000	~500,000ms	~8,000ms	62x faster

Best Practices:

• Use batch sizes between 500-5000 depending on record size
• Larger batches for simple schemas, smaller for complex ones
• Enable connection pooling for better throughput
• Use transactions for data consistency
• Consider disabling indexes/constraints during bulk loads

Caching

Tuxedo provides a comprehensive caching layer to improve query performance and reduce database load. The caching system supports both in-memory and distributed caching scenarios.

Setup and Configuration

Configure caching in your application:

using Noundry.Tuxedo.Caching;
using Noundry.Tuxedo.DependencyInjection;

// Basic setup with memory cache
services.AddTuxedoCaching(options =>
{
    options.DefaultCacheDuration = TimeSpan.FromMinutes(5);
    options.MaxCacheSize = 1000; // Maximum number of cached items
    options.EnableSlidingExpiration = true;
});

// With configuration
services.AddTuxedoCaching(configuration, "TuxedoCaching");

// appsettings.json
{
  "TuxedoCaching": {
    "DefaultCacheDuration": "00:05:00",
    "MaxCacheSize": 1000,
    "EnableSlidingExpiration": true
  }
}

// Complete enterprise setup
services.AddTuxedoEnterprise(options =>
{
    options.EnableCaching = true;
    options.DefaultCacheDuration = TimeSpan.FromMinutes(10);
    options.MaxCacheSize = 5000;
});

Basic Query Caching

Cache query results with automatic key generation:

using Noundry.Tuxedo.Caching;

// Cache query results
var products = await connection.QueryWithCacheAsync<Product>(
    "SELECT * FROM Products WHERE Category = @Category",
    new { Category = "Electronics" },
    cacheExpiration: TimeSpan.FromMinutes(10)
);

// Cache single result
var product = await connection.QuerySingleWithCacheAsync<Product>(
    "SELECT * FROM Products WHERE Id = @Id",
    new { Id = productId },
    cacheExpiration: TimeSpan.FromHours(1)
);

// Custom cache key
var topProducts = await connection.QueryWithCacheAsync<Product>(
    "SELECT TOP 10 * FROM Products ORDER BY Sales DESC",
    cacheKey: "top-products",
    cacheExpiration: TimeSpan.FromMinutes(15)
);

Advanced Caching Patterns

Cache with Tags

Organize cached items with tags for bulk invalidation:

public class ProductRepository
{
    private readonly IDbConnection _connection;
    private readonly IQueryCache _cache;
    private const string ProductTag = "products";
    
    public async Task<IEnumerable<Product>> GetByCategoryAsync(string category)
    {
        var key = $"products:category:{category}";
        
        return await _cache.GetOrAddAsync(key, async () =>
        {
            var products = await _connection.QueryAsync<Product>(
                "SELECT * FROM Products WHERE Category = @Category",
                new { Category = category }
            );
            
            // Tag this cache entry
            if (_cache is MemoryQueryCache memCache)
            {
                memCache.AddKeyToTag(key, ProductTag);
            }
            
            return products;
        },
        expiration: TimeSpan.FromMinutes(10));
    }
    
    public async Task InvalidateProductCacheAsync()
    {
        // Invalidate all product-related cache entries
        await _cache.InvalidateByTagAsync(ProductTag);
    }
}

Conditional Caching

Cache based on specific conditions:

public async Task<IEnumerable<Order>> GetOrdersAsync(
    DateTime startDate, 
    DateTime endDate,
    bool useCache = true)
{
    // Only cache if date range is reasonable
    var shouldCache = useCache && 
                     (endDate - startDate).TotalDays <= 30;
    
    if (shouldCache)
    {
        var cacheKey = $"orders:{startDate:yyyyMMdd}:{endDate:yyyyMMdd}";
        return await connection.QueryWithCacheAsync<Order>(
            "SELECT * FROM Orders WHERE OrderDate BETWEEN @Start AND @End",
            new { Start = startDate, End = endDate },
            cacheKey: cacheKey,
            cacheExpiration: TimeSpan.FromMinutes(30)
        );
    }
    
    // Direct query without caching
    return await connection.QueryAsync<Order>(
        "SELECT * FROM Orders WHERE OrderDate BETWEEN @Start AND @End",
        new { Start = startDate, End = endDate }
    );
}

Cache-Aside Pattern

Manual cache management for complex scenarios:

public class CustomerService
{
    private readonly IQueryCache _cache;
    private readonly IDbConnection _connection;
    
    public async Task<Customer> GetCustomerWithOrdersAsync(int customerId)
    {
        var cacheKey = $"customer:{customerId}:full";
        
        // Try to get from cache
        var cached = await _cache.GetAsync<CustomerWithOrders>(cacheKey);
        if (cached != null)
            return cached;
        
        // Load from database
        using var multi = await _connection.QueryMultipleAsync(@"
            SELECT * FROM Customers WHERE Id = @Id;
            SELECT * FROM Orders WHERE CustomerId = @Id;",
            new { Id = customerId });
        
        var customer = await multi.ReadSingleAsync<Customer>();
        var orders = await multi.ReadAsync<Order>();
        
        var result = new CustomerWithOrders
        {
            Customer = customer,
            Orders = orders.ToList()
        };
        
        // Cache the result
        await _cache.SetAsync(cacheKey, result, TimeSpan.FromMinutes(15));
        
        return result;
    }
    
    public async Task UpdateCustomerAsync(Customer customer)
    {
        // Update database
        await _connection.UpdateAsync(customer);
        
        // Invalidate cache
        await _cache.RemoveAsync($"customer:{customer.Id}:full");
    }
}

Cache Invalidation Strategies

Time-Based Expiration

// Absolute expiration
await _cache.SetAsync("key", value, TimeSpan.FromMinutes(30));

// Sliding expiration (via configuration)
services.AddTuxedoCaching(options =>
{
    options.EnableSlidingExpiration = true;
    options.DefaultCacheDuration = TimeSpan.FromMinutes(5);
});

Event-Based Invalidation

public class ProductService
{
    private readonly IQueryCache _cache;
    
    public async Task CreateProductAsync(Product product)
    {
        await _connection.InsertAsync(product);
        
        // Invalidate related caches
        await InvalidateProductCachesAsync(product.Category);
    }
    
    public async Task UpdateProductAsync(Product product)
    {
        await _connection.UpdateAsync(product);
        
        // Invalidate specific and related caches
        await _cache.RemoveAsync($"product:{product.Id}");
        await InvalidateProductCachesAsync(product.Category);
    }
    
    private async Task InvalidateProductCachesAsync(string category)
    {
        // Invalidate category cache
        await _cache.RemoveAsync($"products:category:{category}");
        
        // Invalidate aggregate caches
        await _cache.RemoveAsync("products:count");
        await _cache.RemoveAsync($"products:category:{category}:count");
        
        // Invalidate top products if exists
        await _cache.RemoveAsync("top-products");
    }
}

Dependency-Based Invalidation

public class CacheDependencyManager
{
    private readonly IQueryCache _cache;
    private readonly Dictionary<string, HashSet<string>> _dependencies;
    
    public async Task<T> GetWithDependenciesAsync<T>(
        string key,
        Func<Task<T>> factory,
        params string[] dependsOn) where T : class
    {
        // Track dependencies
        foreach (var dependency in dependsOn)
        {
            if (!_dependencies.ContainsKey(dependency))
                _dependencies[dependency] = new HashSet<string>();
            _dependencies[dependency].Add(key);
        }
        
        return await _cache.GetOrAddAsync(key, factory);
    }
    
    public async Task InvalidateDependenciesAsync(string dependency)
    {
        if (_dependencies.TryGetValue(dependency, out var keys))
        {
            foreach (var key in keys)
            {
                await _cache.RemoveAsync(key);
            }
            _dependencies.Remove(dependency);
        }
    }
}

Distributed Caching

Implement distributed caching with Redis or SQL Server:

// Custom distributed cache implementation
public class RedisQueryCache : IQueryCache
{
    private readonly IDistributedCache _distributedCache;
    private readonly ISerializer _serializer;
    
    public async Task<T?> GetAsync<T>(string key, CancellationToken cancellationToken = default) 
        where T : class
    {
        var bytes = await _distributedCache.GetAsync(key, cancellationToken);
        if (bytes == null) return null;
        
        return _serializer.Deserialize<T>(bytes);
    }
    
    public async Task SetAsync<T>(string key, T value, TimeSpan? expiration = null, 
        CancellationToken cancellationToken = default) where T : class
    {
        var bytes = _serializer.Serialize(value);
        var options = new DistributedCacheEntryOptions();
        
        if (expiration.HasValue)
            options.SetSlidingExpiration(expiration.Value);
        
        await _distributedCache.SetAsync(key, bytes, options, cancellationToken);
    }
    
    // ... other methods
}

// Register distributed cache
services.AddStackExchangeRedisCache(options =>
{
    options.Configuration = "localhost:6379";
    options.InstanceName = "TuxedoCache";
});

services.AddTuxedoCaching(options =>
{
    options.UseDistributedCache = true;
    options.DistributedCacheFactory = sp => 
        new RedisQueryCache(
            sp.GetRequiredService<IDistributedCache>(),
            sp.GetRequiredService<ISerializer>()
        );
});

Cache Performance Monitoring

Monitor cache performance and hit rates:

public class CacheMetrics
{
    private long _hits;
    private long _misses;
    private long _evictions;
    
    public double HitRate => _hits / (double)(_hits + _misses);
    
    public void RecordHit() => Interlocked.Increment(ref _hits);
    public void RecordMiss() => Interlocked.Increment(ref _misses);
    public void RecordEviction() => Interlocked.Increment(ref _evictions);
}

public class InstrumentedQueryCache : IQueryCache
{
    private readonly IQueryCache _innerCache;
    private readonly CacheMetrics _metrics;
    private readonly ILogger<InstrumentedQueryCache> _logger;
    
    public async Task<T?> GetAsync<T>(string key, CancellationToken cancellationToken = default) 
        where T : class
    {
        var stopwatch = Stopwatch.StartNew();
        var result = await _innerCache.GetAsync<T>(key, cancellationToken);
        
        if (result != null)
        {
            _metrics.RecordHit();
            _logger.LogDebug("Cache hit for {Key} in {ElapsedMs}ms", 
                key, stopwatch.ElapsedMilliseconds);
        }
        else
        {
            _metrics.RecordMiss();
            _logger.LogDebug("Cache miss for {Key}", key);
        }
        
        return result;
    }
    
    // ... other methods with instrumentation
}

Caching Best Practices

1. Cache Key Strategy

   • Use consistent, predictable key patterns
   • Include version numbers for cache busting
   • Avoid sensitive data in cache keys

2. Expiration Policies

   • Use sliding expiration for frequently accessed data
   • Shorter TTL for volatile data
   • Longer TTL for reference data

3. Memory Management

   • Set appropriate cache size limits
   • Monitor memory usage
   • Implement cache eviction policies

4. Invalidation Strategy

   • Invalidate on write operations
   • Use tags for bulk invalidation
   • Consider eventual consistency

5. Performance Considerations

   • Cache serializable data only
   • Avoid caching large objects
   • Use compression for large values
   • Monitor cache hit rates

Cache Configuration Examples

// Development environment
services.AddTuxedoCaching(options =>
{
    options.DefaultCacheDuration = TimeSpan.FromSeconds(30);
    options.MaxCacheSize = 100;
    options.EnableSlidingExpiration = false;
});

// Production environment
services.AddTuxedoCaching(options =>
{
    options.DefaultCacheDuration = TimeSpan.FromMinutes(15);
    options.MaxCacheSize = 10000;
    options.EnableSlidingExpiration = true;
});

// High-traffic scenarios
services.AddTuxedoCaching(options =>
{
    options.DefaultCacheDuration = TimeSpan.FromHours(1);
    options.MaxCacheSize = 50000;
    options.EnableSlidingExpiration = true;
    options.UseDistributedCache = true;
});

Diagnostics and Monitoring

Noundry.Tuxedo provides comprehensive diagnostics and health monitoring capabilities for production environments.

Health Checks

Monitor database connectivity and performance:

using Microsoft.Extensions.Diagnostics.HealthChecks;
using Noundry.Tuxedo.DependencyInjection;

// Add health checks using standard ASP.NET Core health check pattern
services.AddHealthChecks()
    .AddCheck("database", () =>
    {
        // Use Tuxedo connection to check database
        return HealthCheckResult.Healthy();
    }, tags: new[] { "db", "sql" });

// Configure in ASP.NET Core
app.MapHealthChecks("/health");
app.MapHealthChecks("/health/db", new HealthCheckOptions
{
    Predicate = check => check.Tags.Contains("db")
});

// Custom health check implementation
public class DetailedDbHealthCheck : IHealthCheck
{
    private readonly IDbConnection _connection;
    
    public async Task<HealthCheckResult> CheckHealthAsync(
        HealthCheckContext context,
        CancellationToken cancellationToken)
    {
        try
        {
            var stopwatch = Stopwatch.StartNew();
            
            // Test connection
            if (_connection.State != ConnectionState.Open)
                await Task.Run(() => _connection.Open(), cancellationToken);
            
            // Test query execution
            var result = await _connection.ExecuteScalarAsync<int>("SELECT 1");
            
            stopwatch.Stop();
            
            if (stopwatch.ElapsedMilliseconds > 1000)
            {
                return HealthCheckResult.Degraded(
                    $"Database responding slowly: {stopwatch.ElapsedMilliseconds}ms");
            }
            
            return HealthCheckResult.Healthy(
                $"Database responding normally: {stopwatch.ElapsedMilliseconds}ms");
        }
        catch (Exception ex)
        {
            return HealthCheckResult.Unhealthy(
                "Database connection failed", 
                exception: ex);
        }
    }
}

Diagnostics Events

Track and monitor all database operations:

using Noundry.Tuxedo.Diagnostics;

// Register diagnostics
services.AddSingleton<ITuxedoDiagnostics, TuxedoDiagnostics>();

// Subscribe to events
public class DatabaseMonitor
{
    private readonly ITuxedoDiagnostics _diagnostics;
    
    public DatabaseMonitor(ITuxedoDiagnostics diagnostics)
    {
        _diagnostics = diagnostics;
        
        // Subscribe to events
        _diagnostics.QueryExecuted += OnQueryExecuted;
        _diagnostics.CommandExecuted += OnCommandExecuted;
        _diagnostics.ErrorOccurred += OnError;
        _diagnostics.ConnectionOpened += OnConnectionOpened;
        _diagnostics.TransactionCommitted += OnTransactionCommitted;
        _diagnostics.TransactionRolledBack += OnTransactionRolledBack;
    }
    
    private void OnQueryExecuted(object sender, QueryExecutedEventArgs e)
    {
        if (e.Duration.TotalSeconds > 5)
        {
            // Log slow query
            _logger.LogWarning("Slow query detected: {Query} ({Duration}ms)", 
                e.Query, e.Duration.TotalMilliseconds);
            
            // Send metric to monitoring system
            _metrics.RecordSlowQuery(e.Query, e.Duration);
        }
        
        // Track query patterns
        _metrics.IncrementQueryCount(e.QueryType);
    }
    
    private void OnError(object sender, ErrorEventArgs e)
    {
        // Log error with context
        _logger.LogError(e.Exception, 
            "Database error in {Context}: {Query}",
            e.Context, e.Query);
        
        // Alert if critical
        if (IsCriticalError(e.Exception))
        {
            _alerting.SendDatabaseAlert(e.Exception);
        }
    }
    
    private void OnTransactionRolledBack(object sender, TransactionEventArgs e)
    {
        _logger.LogWarning(
            "Transaction rolled back: {TransactionId} after {Duration}ms",
            e.TransactionId, e.Duration?.TotalMilliseconds);
        
        _metrics.IncrementRollbackCount();
    }
}

Performance Metrics Collection

// Integrate with Application Insights, Prometheus, or custom metrics
public class TuxedoMetricsCollector
{
    private readonly ITuxedoDiagnostics _diagnostics;
    private readonly IMetricsClient _metrics;
    
    public TuxedoMetricsCollector(ITuxedoDiagnostics diagnostics, IMetricsClient metrics)
    {
        _diagnostics = diagnostics;
        _metrics = metrics;
        
        _diagnostics.QueryExecuted += (s, e) =>
        {
            _metrics.RecordHistogram("db.query.duration", e.Duration.TotalMilliseconds,
                new[] { ("query_type", e.QueryType), ("table", e.TableName) });
        };
        
        _diagnostics.CommandExecuted += (s, e) =>
        {
            _metrics.RecordHistogram("db.command.duration", e.Duration.TotalMilliseconds,
                new[] { ("command_type", e.CommandType.ToString()) });
            _metrics.RecordGauge("db.rows_affected", e.RowsAffected);
        };
        
        _diagnostics.ConnectionOpened += (s, e) =>
        {
            _metrics.Increment("db.connections.opened");
            if (e.OpenDuration.HasValue)
            {
                _metrics.RecordHistogram("db.connection.open_time", 
                    e.OpenDuration.Value.TotalMilliseconds);
            }
        };
        
        _diagnostics.ErrorOccurred += (s, e) =>
        {
            _metrics.Increment("db.errors",
                new[] { ("error_type", e.Exception.GetType().Name) });
        };
    }
}

Query Performance Analysis

// Automatic slow query detection
services.Configure<DiagnosticsOptions>(options =>
{
    options.SlowQueryThreshold = TimeSpan.FromSeconds(1); // Queries over 1 second
    options.EnableQueryLogging = true;
    options.EnablePerformanceMetrics = true;
    options.EnableErrorLogging = true;
});

// Custom query analyzer
public class QueryAnalyzer
{
    private readonly ConcurrentDictionary<string, QueryStats> _queryStats = new();
    
    public QueryAnalyzer(ITuxedoDiagnostics diagnostics)
    {
        diagnostics.QueryExecuted += AnalyzeQuery;
    }
    
    private void AnalyzeQuery(object sender, QueryExecutedEventArgs e)
    {
        var stats = _queryStats.AddOrUpdate(
            e.Query,
            new QueryStats { Query = e.Query },
            (key, existing) =>
            {
                existing.ExecutionCount++;
                existing.TotalDuration += e.Duration;
                existing.MaxDuration = Math.Max(existing.MaxDuration, e.Duration.TotalMilliseconds);
                existing.MinDuration = Math.Min(existing.MinDuration, e.Duration.TotalMilliseconds);
                return existing;
            });
        
        // Alert on problematic patterns
        if (stats.ExecutionCount > 1000 && stats.AverageDuration > 500)
        {
            _logger.LogWarning(
                "Frequent slow query detected: {Query} " +
                "Executions: {Count}, Avg: {Avg}ms, Max: {Max}ms",
                e.Query, stats.ExecutionCount, 
                stats.AverageDuration, stats.MaxDuration);
        }
    }
    
    public IEnumerable<QueryStats> GetTopSlowQueries(int count = 10)
    {
        return _queryStats.Values
            .OrderByDescending(q => q.AverageDuration)
            .Take(count);
    }
}

Connection Pool Monitoring

// Monitor connection pool health
public class ConnectionPoolMonitor
{
    private int _activeConnections;
    private int _pooledConnections;
    
    public ConnectionPoolMonitor(ITuxedoDiagnostics diagnostics)
    {
        diagnostics.ConnectionOpened += (s, e) =>
        {
            Interlocked.Increment(ref _activeConnections);
            UpdateMetrics();
        };
        
        diagnostics.ConnectionClosed += (s, e) =>
        {
            Interlocked.Decrement(ref _activeConnections);
            UpdateMetrics();
        };
    }
    
    private void UpdateMetrics()
    {
        _metrics.RecordGauge("db.connections.active", _activeConnections);
        
        // Alert if connection leak detected
        if (_activeConnections > 100)
        {
            _logger.LogError("Potential connection leak: {Count} active connections",
                _activeConnections);
        }
    }
}

Diagnostics Best Practices

1. Enable in Production: Use diagnostics to monitor real-world performance
2. Set Thresholds: Configure appropriate slow query thresholds
3. Sanitize Logs: Never log passwords or sensitive data
4. Use Sampling: For high-traffic apps, sample diagnostics to reduce overhead
5. Integrate Monitoring: Connect to APM tools (Application Insights, DataDog, New Relic)
6. Track Trends: Monitor query performance over time
7. Alert on Anomalies: Set up alerts for unusual patterns

Dependency Injection

Noundry.Tuxedo includes lightweight DI helpers. Register a connection factory:

using Microsoft.Extensions.DependencyInjection;
using System.Data;
using Microsoft.Data.SqlClient;
using Noundry.Tuxedo.DependencyInjection;

var services = new ServiceCollection();
services.AddTuxedo(_ => new SqlConnection(cs));          // scoped by default

// With options
services.AddSingleton(new DbOptions { ConnectionString = cs });
services.AddTuxedoWithOptions<DbOptions>((sp, opt) => new SqlConnection(opt.ConnectionString));

// In-memory SQLite for testing
services.AddTuxedoSqliteInMemory("TestDb");

var provider = services.BuildServiceProvider();
var conn = provider.GetRequiredService<IDbConnection>();

public record DbOptions { public string ConnectionString { get; init; } = string.Empty; }

Provider helpers

using Noundry.Tuxedo.DependencyInjection;

// SQL Server
services.AddTuxedoSqlServer("Server=.;Database=Demo;Integrated Security=true;TrustServerCertificate=true");

// PostgreSQL
services.AddTuxedoPostgres("Host=localhost;Database=demo;Username=demo;Password=pass");

// MySQL
services.AddTuxedoMySql("Server=localhost;Database=demo;Uid=demo;Pwd=pass;");

// SQLite (file-based)
services.AddTuxedoSqlite("demo.db");

// SQLite (in-memory shared)
services.AddTuxedoSqliteInMemory("TestDb");

Notes

• Use provider‑specific connection strings and types (SqlConnection, NpgsqlConnection, MySqlConnection, SqliteConnection).
• [ExplicitKey] marks non‑identity keys; [Computed] properties are ignored on write.
• Async CRUD helpers are available as GetAsync/SelectAsync, GetAllAsync, InsertAsync, UpdateAsync, DeleteAsync, DeleteAllAsync.
• Partial update functionality is built into Update and UpdateAsync methods with optional property filtering.

🎭 Noundry.Tuxedo.Bowtie Database Migrations

Noundry.Tuxedo.Bowtie is a companion library that extends Noundry.Tuxedo with comprehensive database migration and schema synchronization capabilities.

Key Features

• Multi-Database DDL Generation: SQL Server, PostgreSQL, MySQL, SQLite
• Advanced Indexing: GIN (PostgreSQL), Clustered (SQL Server), FullText, Spatial
• Extended Attributes: [Index], [Unique], [ForeignKey], [CheckConstraint], [Column]
• CLI Tool: bowtie sync, bowtie generate, bowtie validate
• ASP.NET Core Integration: Automatic schema synchronization

Quick Start with Noundry.Tuxedo.Bowtie

// 1. Install Noundry.Tuxedo.Bowtie
// dotnet add package Noundry.Tuxedo.Bowtie

// 2. Define models with extended attributes
[Table("Products")]
public class Product
{
    [Key]
    public int Id { get; set; }

    [Column(MaxLength = 200)]
    [Index("IX_Products_Name")]
    public string Name { get; set; } = string.Empty;

    [Index("IX_Products_Content_GIN", IndexType = IndexType.GIN)] // PostgreSQL
    [Column(TypeName = "jsonb")]
    public string Metadata { get; set; } = "{}";

    [ForeignKey("Categories", OnDelete = ReferentialAction.Cascade)]
    public int CategoryId { get; set; }

    [CheckConstraint("Price > 0")]
    [Column(Precision = 18, Scale = 2)]
    public decimal Price { get; set; }
}

// 3. Configure in ASP.NET Core
builder.Services.AddNoundryTuxedoBowtie();

// 4. Auto-synchronize database (development)
if (app.Environment.IsDevelopment())
{
    await app.Services.SynchronizeDatabaseAsync(
        connectionString: "Data Source=app.db",
        provider: DatabaseProvider.SQLite
    );
}

CLI Usage

# Generate DDL scripts
noundry-tuxedo-bowtie generate --assembly MyApp.dll --provider SqlServer --output schema.sql

# Synchronize database
noundry-tuxedo-bowtie sync --assembly MyApp.dll --connection-string "..." --provider PostgreSQL

# Validate models
noundry-tuxedo-bowtie validate --assembly MyApp.dll --provider MySQL

📖 Complete Documentation: See Bowtie/README.md for full documentation, examples, and integration guides.

Examples

Noundry.Tuxedo Examples

• Tuxedo/examples/Console/ - Console application demonstrating ORM features, QueryBuilder, and bulk operations
• Tuxedo/examples/WebApi/ - REST API example with dependency injection and async operations
• Tuxedo/examples/RazorWeb/ - Razor Pages web application with full CRUD operations

Noundry.Tuxedo.Bowtie Examples

• Bowtie/examples/Console/ - Console application showing DDL generation and schema synchronization
• Bowtie/examples/WebApi/ - ASP.NET Core Web API with automatic database migration
• Bowtie/examples/MSBuild/ - MSBuild integration examples for CI/CD pipelines

Status

This README documents the APIs present in the codebase today. The library includes comprehensive query building, CRUD operations, partial updates, dependency injection helpers, multi-database support, and database migration capabilities through Noundry.Tuxedo.Bowtie.