Forge.Repository.SqlServer 10.0.1

Forge.Repository

A flexible, database-agnostic repository pattern implementation for .NET 10 using Entity Framework Core. Forge.Repository provides a comprehensive abstraction layer for data access with support for SQL Server, PostgreSQL, MySQL, and Oracle databases.

Table of Contents

• Overview
• Features
• Supported Databases
• Installation
• Quick Start
• Core Concepts
• Modules
• API Reference
• Usage Examples
• Contributing
• License

Overview

Forge.Repository is a robust repository pattern implementation built on top of Entity Framework Core 10.*. It abstracts database operations and provides a clean, fluent interface for data access operations. The library supports both synchronous and asynchronous operations, with full support for cancellation tokens and transaction management.

Key Benefits

• Database Agnostic: Write once, deploy to multiple database engines
• Async First: Built with async/await patterns for scalable applications
• Type Safe: Full support for generic repositories with compile-time type checking
• Unit of Work Pattern: Coordinate multiple operations within a single transaction
• Performance Tuned: Built-in support for connection pooling and retry logic
• Auditable: Built-in support for tracking entity changes with timestamps and user information
• Extensible: Easy to extend and customize for specific application needs

Features

Core Features

• Generic Repository Pattern: IRepositoryAsync<TEntity> interface for CRUD operations
• Unit of Work Pattern: IUnitOfWork for coordinating multiple repository operations
• Async Operations: Full support for async/await programming model with cancellation tokens
• Criteria-Based Queries: Advanced querying with custom criteria objects
• Bulk Operations: Methods for adding, updating, and removing multiple entities
• Raw SQL Support: Execute raw SQL queries and commands with full parameterization support
• Change Tracking: Access tracked entities through the change tracker
• Lazy Loading: Optional lazy loading of related entities through proxies
• Connection Pooling: Built-in support for connection pool configuration
• Retry Logic: Automatic retry policies for transient failures
• Soft Deletes: Support for auditable entities with soft delete capabilities

Advanced Features

• Custom Criteria Objects: ICriteriaAsync<TEntity>, ICriteriaSingleAsync<TEntity> for complex queries
• Foreign Entity Matching: Query related entities through ICriteriaForeignEntity<TEntity, TFEntity>
• Database Initialization: IDbInitializer interface for migrations and seeding
• Provider-Specific Tuning: Database-specific optimizations through IDbProviderTuner
• Enum String Conversion: Automatic enum-to-string conversion for database storage
• Auditable Models: IAuditableBaseModel for automatic tracking of creation, modification, and deletion

Supported Databases

Forge.Repository supports the following database engines through dedicated NuGet packages:

Installation

Core Package

dotnet add package Forge.Repository

Database-Specific Packages

Choose the package matching your target database:

# For SQL Server
dotnet add package Forge.SqlServer

# For PostgreSQL
dotnet add package Forge.PostgreSql

# For MySQL
dotnet add package Forge.MySql

# For Oracle
dotnet add package Forge.Oracle

Quick Start

1. Define Your Entity Models

using Forge.Models;

public class Product : AuditableBaseModel
{
    public required string Name { get; set; }
    public required decimal Price { get; set; }
    public string Description { get; set; }
    public int StockQuantity { get; set; }
}

2. Create Your DbContext

using Forge.Repository;
using Microsoft.EntityFrameworkCore;

public class ApplicationDbContext : DbContext
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) 
        : base(options)
    {
    }

    public DbSet<Product> Products { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        // Configure your entities
    }
}

3. Register Services (Startup)

// Program.cs - for SQL Server
using Forge.SqlServer;
using Forge.DbTuner;

var builder = WebApplicationBuilder.CreateBuilder(args);

var poolingOptions = new ForgeDbContextPoolingOptions
{
    EnablePooling = true,
    MinPoolSize = 5,
    MaxPoolSize = 20
};

builder.Services.AddForgeRepositorySqlServer<ApplicationDbContext>(
    builder.Configuration.GetConnectionString("DefaultConnection"),
    poolingOptions,
    tuner => tuner
        .SetRetry(3)
        .SetCommandTimeout(30)
        .EnableLazyLoading()
);

var app = builder.Build();
// ... rest of configuration

4. Use the Repository

public class ProductService : IProductService
{
    private readonly IUnitOfWork _unitOfWork;

    public ProductService(IUnitOfWork unitOfWork)
    {
        _unitOfWork = unitOfWork;
    }

    public async Task<IList<Product>> GetAllProductsAsync(CancellationToken cancellationToken)
    {
        var repository = _unitOfWork.GetRepositoryAsync<Product>();
        return await repository.GetAllAsync(cancellationToken);
    }

    public async Task<Product> GetProductByIdAsync(string id, CancellationToken cancellationToken)
    {
        var repository = _unitOfWork.GetRepositoryAsync<Product>();
        return await repository.GetByIdAsync(id, cancellationToken);
    }

    public async Task AddProductAsync(Product product, CancellationToken cancellationToken)
    {
        var repository = _unitOfWork.GetRepositoryAsync<Product>();
        await repository.AddAsync(product, cancellationToken);
        await _unitOfWork.SaveChangesAsync(cancellationToken);
    }
}

Core Concepts

Base Models

Forge provides two base model classes for your entities:

BaseModel

The simplest entity base class with only an Id property.

public abstract class BaseModel : IBaseModel
{
    public virtual required string Id { get; set; }
}

AuditableBaseModel

An extended base class with built-in audit tracking capabilities.

public abstract class AuditableBaseModel : BaseModel, IAuditableBaseModel
{
    public virtual DateTime CreatedOn { get; set; }
    public virtual DateTime ModifiedOn { get; set; }
    public virtual string CreatedBy { get; set; }
    public virtual string ModifiedBy { get; set; }
    public virtual bool IsDeleted { get; set; }

    public virtual void OnCreate()
    {
        CreatedOn = DateTime.UtcNow;
    }

    public virtual void OnUpdate()
    {
        ModifiedOn = DateTime.UtcNow;
    }

    public virtual void OnDelete()
    {
        IsDeleted = true;
        ModifiedOn = DateTime.UtcNow;
    }
}

Repository Pattern

The IRepositoryAsync<TEntity> interface provides the foundation for all data access operations:

public interface IRepositoryAsync<TEntity> where TEntity : class, IBaseModel
{
    // CRUD Operations
    Task AddAsync(TEntity entity, CancellationToken cancellationToken = default);
    Task AddRangeAsync(ICollection<TEntity> entities, CancellationToken cancellationToken = default);
    Task UpdateAsync(TEntity entity, CancellationToken cancellationToken = default);
    Task UpdateRangeAsync(ICollection<TEntity> entities, CancellationToken cancellationToken = default);
    Task RemoveAsync(TEntity entity, CancellationToken cancellationToken = default);
    Task RemoveRangeAsync(ICollection<TEntity> entities, CancellationToken cancellationToken = default);

    // Query Operations
    Task<TEntity> GetByIdAsync(string id, CancellationToken cancellationToken = default);
    Task<IList<TEntity>> GetAllAsync(CancellationToken cancellationToken = default);
    Task<TEntity> FindAsync(Expression<Func<TEntity, bool>> predicate, CancellationToken cancellationToken = default);
    Task<IList<TEntity>> FindAllAsync(Expression<Func<TEntity, bool>> predicate, CancellationToken cancellationToken = default);

    // Counting Operations
    Task<int> CountAsync(CancellationToken cancellationToken = default);
    Task<int> CountAsync(Expression<Func<TEntity, bool>> predicate, CancellationToken cancellationToken = default);
    Task<int> CountAsync(ICountCriteriaAsync<TEntity> criteria, CancellationToken cancellationToken = default);

    // Criteria-Based Queries
    Task<IList<TEntity>> MatchAsync(ICriteriaAsync<TEntity> criteria, CancellationToken cancellationToken = default);
    Task<TEntity> MatchAsync(ICriteriaSingleAsync<TEntity> criteria, CancellationToken cancellationToken = default);

    // Foreign Entity Operations
    Task<IList<TFEntity>> MatchAsync<TFEntity>(ICriteriaForeignEntity<TEntity, TFEntity> criteria, CancellationToken cancellationToken = default) where TFEntity : class;
    Task<TFEntity> MatchAsync<TFEntity>(ICriteriaSingleForeignEntity<TEntity, TFEntity> criteria, CancellationToken cancellationToken = default) where TFEntity : class;

    // Raw SQL Operations
    Task<IList<TFEntity>> FindAllBySql<TFEntity>(string sql, CancellationToken cancellationToken, params object[] parameters);
    Task<IList<TFEntity>> FindAllBySql<TFEntity>(string sql, CancellationToken cancellationToken);
    Task<int> ExecuteSqlRaw(string sql, CancellationToken cancellationToken);
    Task<int> ExecuteSqlRaw(string sql, CancellationToken cancellationToken, params object[] parameters);

    // Tracking Operations
    void Attach(TEntity entity);
    void Detach(TEntity entity);
    void DetachAll();
}

Unit of Work Pattern

The IUnitOfWork interface coordinates operations across multiple repositories:

public interface IUnitOfWork
{
    IRepositoryAsync<TSet> GetRepositoryAsync<TSet>() where TSet : class, IBaseModel;
    Task<int> SaveChangesAsync(CancellationToken cancellationToken = default);
    int SaveChanges();
    void DetachAllAsync<TSet>() where TSet : class, IBaseModel;
}

Modules

Forge (Core)

The main library containing interfaces, base models, and the default repository implementations.

Key Classes and Interfaces:

• DbContext: Abstract base class for Entity Framework DbContext with change tracking utilities
• IRepositoryAsync<TEntity>: Generic repository interface for all entities
• IUnitOfWork: Coordinates repository operations
• IDbInitializer: Handles database initialization and migrations
• BaseModel: Simple entity base class with Id
• AuditableBaseModel: Entity base class with audit tracking
• ForgeDbTuner: Fluent configuration builder for database options
• ForgeDbContextPoolingOptions: Configuration for connection pooling
• DBRepository<TEntity>: Default repository with automatic change persistence
• DBUnitOfWork: Default unit of work implementation
• IDataContext: Low-level database context abstraction

Forge.SqlServer

SQL Server-specific implementation with optimizations for Microsoft SQL Server.

Key Features:

• Native SQL Server connection pooling support
• SQL Server-specific performance tuning
• Optimized retry policies for SQL Server transient errors

Usage:

builder.Services.AddForgeRepositorySqlServer<YourDbContext>(
    connectionString,
    poolingOptions,
    tuner => tuner.SetRetry(3)
);

Forge.PostgreSql

PostgreSQL-specific implementation with Npgsql driver support.

Key Features:

• Npgsql-native connection pooling
• Custom history repository for migration tracking
• PostgreSQL-specific enum handling and optimizations

Usage:

builder.Services.AddForgeRepositoryPostgreSql<YourDbContext>(
    connectionString,
    poolingOptions,
    tuner => tuner.SetRetry(3)
);

Forge.MySql

MySQL and MariaDB implementation with MySqlConnector support.

Key Features:

• MySqlConnector connection pooling with uint pool sizes
• Automatic server version detection
• MySQL-specific performance tuning

Usage:

builder.Services.AddForgeRepositoryMySql<YourDbContext>(
    connectionString,
    poolingOptions,
    tuner => tuner.SetRetry(3)
);

Forge.Oracle

Oracle database implementation with managed data access support.

Key Features:

• Oracle connection pooling support
• Oracle-specific command timeout handling
• Optimized for Oracle database semantics

Usage:

builder.Services.AddForgeRepositoryOracle<YourDbContext>(
    connectionString,
    poolingOptions,
    tuner => tuner.SetRetry(3)
);

API Reference

Repository Interface Methods

Adding Entities

// Add single entity
Task AddAsync(TEntity entity, CancellationToken cancellationToken = default);

// Add multiple entities
Task AddRangeAsync(ICollection<TEntity> entities, CancellationToken cancellationToken = default);

Updating Entities

// Update single entity
Task UpdateAsync(TEntity entity, CancellationToken cancellationToken = default);

// Update multiple entities
Task UpdateRangeAsync(ICollection<TEntity> entities, CancellationToken cancellationToken = default);

Removing Entities

// Remove single entity
Task RemoveAsync(TEntity entity, CancellationToken cancellationToken = default);

// Remove multiple entities
Task RemoveRangeAsync(ICollection<TEntity> entities, CancellationToken cancellationToken = default);

Querying Entities

// Get all entities
Task<IList<TEntity>> GetAllAsync(CancellationToken cancellationToken = default);

// Get entity by ID
Task<TEntity> GetByIdAsync(string id, CancellationToken cancellationToken = default);

// Find first entity matching predicate
Task<TEntity> FindAsync(Expression<Func<TEntity, bool>> predicate, CancellationToken cancellationToken = default);

// Find all entities matching predicate
Task<IList<TEntity>> FindAllAsync(Expression<Func<TEntity, bool>> predicate, CancellationToken cancellationToken = default);

Counting Entities

// Count all entities
Task<int> CountAsync(CancellationToken cancellationToken = default);

// Count entities matching predicate
Task<int> CountAsync(Expression<Func<TEntity, bool>> predicate, CancellationToken cancellationToken = default);

// Count entities matching criteria
Task<int> CountAsync(ICountCriteriaAsync<TEntity> criteria, CancellationToken cancellationToken = default);

Criteria-Based Queries

// Match multiple entities with criteria
Task<IList<TEntity>> MatchAsync(ICriteriaAsync<TEntity> criteria, CancellationToken cancellationToken = default);

// Match single entity with criteria
Task<TEntity> MatchAsync(ICriteriaSingleAsync<TEntity> criteria, CancellationToken cancellationToken = default);

Raw SQL Operations

// Execute SQL query and return mapped entities
Task<IList<TFEntity>> FindAllBySql<TFEntity>(string sql, CancellationToken cancellationToken, params object[] parameters);

// Execute raw SQL command
Task<int> ExecuteSqlRaw(string sql, CancellationToken cancellationToken, params object[] parameters);

Change Tracking

// Attach entity to context
void Attach(TEntity entity);

// Detach single entity
void Detach(TEntity entity);

// Detach all entities
void DetachAll();

Unit of Work Methods

// Get repository for entity type
IRepositoryAsync<TSet> GetRepositoryAsync<TSet>() where TSet : class, IBaseModel;

// Save all changes asynchronously
Task<int> SaveChangesAsync(CancellationToken cancellationToken = default);

// Save all changes synchronously
int SaveChanges();

// Detach all entities of specific type
void DetachAllAsync<TSet>() where TSet : class, IBaseModel;

ForgeDbTuner Configuration Methods

// Enable lazy loading proxies
ForgeDbTuner EnableLazyLoading();

// Configure warning behaviors
ForgeDbTuner ConfigureWarnings(Action<WarningsConfigurationBuilder> warningsConfigurationBuilderAction);

// Set retry count for transient failures
ForgeDbTuner SetRetry(int count);

// Set command timeout in seconds
ForgeDbTuner SetCommandTimeout(int timeoutInSeconds);

// Pool-specific tuning (database-specific)
ForgeDbTuner SetPoolingOptions(...);

Usage Examples

Basic CRUD Operations

// Adding
var product = new Product
{
    Id = Guid.NewGuid().ToString(),
    Name = "Laptop",
    Price = 999.99m,
    StockQuantity = 10
};

var repo = _unitOfWork.GetRepositoryAsync<Product>();
await repo.AddAsync(product, cancellationToken);
await _unitOfWork.SaveChangesAsync(cancellationToken);

// Retrieving
var retrieved = await repo.GetByIdAsync(product.Id, cancellationToken);

// Updating
retrieved.Price = 899.99m;
await repo.UpdateAsync(retrieved, cancellationToken);
await _unitOfWork.SaveChangesAsync(cancellationToken);

// Deleting
await repo.RemoveAsync(retrieved, cancellationToken);
await _unitOfWork.SaveChangesAsync(cancellationToken);

Querying with Predicates

var repo = _unitOfWork.GetRepositoryAsync<Product>();

// Find all expensive products
var expensiveProducts = await repo.FindAllAsync(
    p => p.Price > 1000, 
    cancellationToken
);

// Count products in stock
var inStockCount = await repo.CountAsync(
    p => p.StockQuantity > 0, 
    cancellationToken
);

// Find first product by name
var product = await repo.FindAsync(
    p => p.Name == "Laptop", 
    cancellationToken
);

Bulk Operations

var repo = _unitOfWork.GetRepositoryAsync<Product>();
var products = new List<Product> 
{
    new() { Id = "1", Name = "Product 1", Price = 10 },
    new() { Id = "2", Name = "Product 2", Price = 20 }
};

// Add multiple
await repo.AddRangeAsync(products, cancellationToken);

// Update multiple
foreach (var p in products) p.Price *= 1.1m;
await repo.UpdateRangeAsync(products, cancellationToken);

// Remove multiple
await repo.RemoveRangeAsync(products, cancellationToken);

await _unitOfWork.SaveChangesAsync(cancellationToken);

Criteria-Based Queries

public class ProductPriceCriteria : ICriteriaAsync<Product>
{
    private readonly decimal _minPrice;
    private readonly decimal _maxPrice;

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

    public async Task<IList<Product>> MatchQueryFromAsync(
        IQueryable<Product> data, 
        CancellationToken cancellationToken)
    {
        return await data
            .Where(p => p.Price >= _minPrice && p.Price <= _maxPrice)
            .OrderByDescending(p => p.Price)
            .ToListAsync(cancellationToken);
    }
}

// Usage
var criteria = new ProductPriceCriteria(100, 500);
var products = await repo.MatchAsync(criteria, cancellationToken);

Raw SQL Queries

var repo = _unitOfWork.GetRepositoryAsync<Product>();

// Query with parameters
var results = await repo.FindAllBySql<Product>(
    "SELECT * FROM Products WHERE Price > @price AND StockQuantity > 0",
    cancellationToken,
    new SqlParameter("@price", 100)
);

// Execute command
var rowsAffected = await repo.ExecuteSqlRaw(
    "UPDATE Products SET Price = Price * 1.1 WHERE Category = @category",
    cancellationToken,
    new SqlParameter("@category", "Electronics")
);

Auditable Entities

public class OrderItem : AuditableBaseModel
{
    public required string OrderId { get; set; }
    public required string ProductId { get; set; }
    public int Quantity { get; set; }
    public decimal UnitPrice { get; set; }
}

// Usage with automatic audit tracking
var item = new OrderItem
{
    Id = Guid.NewGuid().ToString(),
    OrderId = orderId,
    ProductId = productId,
    Quantity = 5,
    UnitPrice = 99.99m
};

// Automatically sets CreatedOn and CreatedBy
item.OnCreate();

var repo = _unitOfWork.GetRepositoryAsync<OrderItem>();
await repo.AddAsync(item, cancellationToken);

// Later, when updating
item.Quantity = 10;
item.OnUpdate();
await repo.UpdateAsync(item, cancellationToken);

// For soft delete
item.OnDelete();
await repo.UpdateAsync(item, cancellationToken);

Connection Pooling Configuration

var poolingOptions = new ForgeDbContextPoolingOptions
{
    EnablePooling = true,
    MinPoolSize = 5,
    MaxPoolSize = 50
};

builder.Services.AddForgeRepositorySqlServer<AppDbContext>(
    connectionString,
    poolingOptions,
    tuner => tuner
        .SetRetry(3)
        .SetCommandTimeout(30)
        .EnableLazyLoading()
);

Transactional Operations

public async Task TransferProductAsync(string fromOrderId, string toOrderId, CancellationToken cancellationToken)
{
    try
    {
        var itemRepo = _unitOfWork.GetRepositoryAsync<OrderItem>();

        var items = await itemRepo.FindAllAsync(
            i => i.OrderId == fromOrderId, 
            cancellationToken
        );

        foreach (var item in items)
        {
            item.OrderId = toOrderId;
        }

        await itemRepo.UpdateRangeAsync(items, cancellationToken);

        // All changes committed as single transaction
        await _unitOfWork.SaveChangesAsync(cancellationToken);
    }
    catch (Exception ex)
    {
        // Changes automatically rolled back on exception
        throw;
    }
}

Contributing

Contributions are welcome! Please submit issues to help improve Forge.Repository.

License

This project is licensed under the LICENSE file in the repository.

Author: Sayem
Website: https://www.sayem.xyz/packages/forge-repository
Version: 10.0.1
Target Framework: .NET 10