Fingent-Database-Repository-SQL 1.0.1

Fingent SQL Database Repository

A lightweight and extensible repository layer for Microsoft SQL Server in .NET applications. Helps simplify database access by providing reusable repository classes that implement common CRUD operations, while supporting clean architecture principles such as separation of concerns and testability.

🚀 Features

• Repository Pattern: Clean abstraction over SQL Server data access
• Entity Framework Core: Built on EF Core for SQL Server
• Generic Repository: Base repository with common CRUD operations
• Unit of Work: Transaction management and change tracking
• Async Support: Full async/await pattern support for scalability
• Clean Architecture: Promotes separation of concerns and testability
• Query Builders: Advanced where clause and sorting expression builders
• Pagination Support: Efficient pagination with SQL Server optimizations

📦 Installation

dotnet add package Fingent-Database-Repository-SQL

🔧 Dependencies

• .NET 8.0
• Microsoft.EntityFrameworkCore 8.0.8
• Microsoft.EntityFrameworkCore.SqlServer 8.0.8
• Fingent.Shared.Core

💻 Usage

Basic Setup

// Program.cs or Startup.cs
services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlServer(connectionString));

services.AddSqlServerRepository<ApplicationDbContext>();

Entity Configuration

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public decimal Price { get; set; }
    public int CategoryId { get; set; }
    public Category Category { get; set; }
    public DateTime CreatedAt { get; set; }
    public DateTime? UpdatedAt { get; set; }
    public bool IsActive { get; set; }
    public int StockQuantity { get; set; }
}

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public List<Product> Products { get; set; } = new();
}

public class ApplicationDbContext : DbContext
{
    public DbSet<Product> Products { get; set; }
    public DbSet<Category> Categories { get; set; }
    
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) 
        : base(options)
    {
    }
    
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // SQL Server specific configurations
        modelBuilder.Entity<Product>(entity =>
        {
            entity.HasKey(e => e.Id);
            entity.Property(e => e.Id).ValueGeneratedOnAdd();
            entity.Property(e => e.Name).IsRequired().HasMaxLength(100);
            entity.Property(e => e.Description).HasMaxLength(500);
            entity.Property(e => e.Price).HasColumnType("decimal(18,2)");
            entity.Property(e => e.CreatedAt).HasDefaultValueSql("GETUTCDATE()");
            entity.HasIndex(e => e.Name);
            entity.HasOne(e => e.Category)
                  .WithMany(c => c.Products)
                  .HasForeignKey(e => e.CategoryId)
                  .OnDelete(DeleteBehavior.Restrict);
        });
        
        modelBuilder.Entity<Category>(entity =>
        {
            entity.HasKey(e => e.Id);
            entity.Property(e => e.Id).ValueGeneratedOnAdd();
            entity.Property(e => e.Name).IsRequired().HasMaxLength(50);
            entity.Property(e => e.Description).HasMaxLength(200);
            entity.HasIndex(e => e.Name).IsUnique();
        });
        
        base.OnModelCreating(modelBuilder);
    }
}

Repository Usage

public class ProductService
{
    private readonly IRepository<Product> _productRepository;
    private readonly IRepository<Category> _categoryRepository;
    private readonly IUnitOfWork _unitOfWork;
    private readonly ILogger<ProductService> _logger;
    
    public ProductService(
        IRepository<Product> productRepository,
        IRepository<Category> categoryRepository,
        IUnitOfWork unitOfWork,
        ILogger<ProductService> logger)
    {
        _productRepository = productRepository;
        _categoryRepository = categoryRepository;
        _unitOfWork = unitOfWork;
        _logger = logger;
    }
    
    public async Task<Product> CreateProductAsync(Product product)
    {
        product.CreatedAt = DateTime.UtcNow;
        product.IsActive = true;
        
        await _productRepository.AddAsync(product);
        await _unitOfWork.SaveChangesAsync();
        
        _logger.LogInformation("Product created: {ProductId} - {ProductName}", product.Id, product.Name);
        return product;
    }
    
    public async Task<Product> GetProductAsync(int id)
    {
        return await _productRepository.GetByIdAsync(id);
    }
    
    public async Task<Product> GetProductWithCategoryAsync(int id)
    {
        return await _productRepository.GetByIdAsync(id, includes: p => p.Category);
    }
    
    public async Task<IEnumerable<Product>> GetActiveProductsAsync()
    {
        return await _productRepository.FindAsync(
            predicate: p => p.IsActive,
            orderBy: q => q.OrderBy(p => p.Name),
            includes: p => p.Category
        );
    }
    
    public async Task<PagingResult<Product>> GetProductsPaginatedAsync(int page, int pageSize, int? categoryId = null)
    {
        Expression<Func<Product, bool>> predicate = p => p.IsActive;
        
        if (categoryId.HasValue)
        {
            predicate = p => p.IsActive && p.CategoryId == categoryId.Value;
        }
        
        return await _productRepository.GetPagedAsync(
            page: page,
            pageSize: pageSize,
            predicate: predicate,
            orderBy: q => q.OrderBy(p => p.Name),
            includes: p => p.Category
        );
    }
    
    public async Task<IEnumerable<Product>> GetExpensiveProductsAsync(decimal minPrice)
    {
        return await _productRepository.FindAsync(
            predicate: p => p.Price >= minPrice && p.IsActive,
            orderBy: q => q.OrderByDescending(p => p.Price)
        );
    }
    
    public async Task UpdateProductAsync(Product product)
    {
        product.UpdatedAt = DateTime.UtcNow;
        _productRepository.Update(product);
        await _unitOfWork.SaveChangesAsync();
        
        _logger.LogInformation("Product updated: {ProductId}", product.Id);
    }
    
    public async Task DeleteProductAsync(int id)
    {
        var product = await _productRepository.GetByIdAsync(id);
        if (product != null)
        {
            _productRepository.Delete(product);
            await _unitOfWork.SaveChangesAsync();
            
            _logger.LogInformation("Product deleted: {ProductId}", id);
        }
    }
}

Advanced Query Building

public class AdvancedProductService
{
    private readonly IRepository<Product> _productRepository;
    
    public AdvancedProductService(IRepository<Product> productRepository)
    {
        _productRepository = productRepository;
    }
    
    // Using WhereBuilder for dynamic filtering
    public async Task<IEnumerable<Product>> SearchProductsAsync(ProductSearchCriteria criteria)
    {
        var predicate = PredicateBuilder.True<Product>();
        
        // Add base filter for active products
        var activePred = PredicateBuilder.BuildPredicate<Product, bool>(
            x => x.IsActive, "=", true);
        predicate = PredicateBuilder.Combine(predicate, activePred, "and");
        
        // Add name filter if provided
        if (!string.IsNullOrEmpty(criteria.Name))
        {
            var namePred = PredicateBuilder.BuildPredicate<Product, string>(
                x => x.Name, "contains", criteria.Name);
            predicate = PredicateBuilder.Combine(predicate, namePred, "and");
        }
        
        // Add price range filters
        if (criteria.MinPrice.HasValue)
        {
            var minPricePred = PredicateBuilder.BuildPredicate<Product, decimal>(
                x => x.Price, ">=", criteria.MinPrice.Value);
            predicate = PredicateBuilder.Combine(predicate, minPricePred, "and");
        }
        
        if (criteria.MaxPrice.HasValue)
        {
            var maxPricePred = PredicateBuilder.BuildPredicate<Product, decimal>(
                x => x.Price, "<=", criteria.MaxPrice.Value);
            predicate = PredicateBuilder.Combine(predicate, maxPricePred, "and");
        }
        
        // Add category filter
        if (criteria.CategoryId.HasValue)
        {
            var categoryPred = PredicateBuilder.BuildPredicate<Product, int>(
                x => x.CategoryId, "=", criteria.CategoryId.Value);
            predicate = PredicateBuilder.Combine(predicate, categoryPred, "and");
        }
        
        return await _productRepository.FindAsync(predicate, includes: p => p.Category);
    }
    
    // Using SortExpressionBuilder for dynamic sorting
    public async Task<IEnumerable<Product>> GetSortedProductsAsync(string sortBy, bool descending = false)
    {
        var sortExpression = sortBy.ToLower() switch
        {
            "name" => SortExpressionBuilder.BuildSortExpression<Product, string>(x => x.Name),
            "price" => SortExpressionBuilder.BuildSortExpression<Product, decimal>(x => x.Price),
            "created" => SortExpressionBuilder.BuildSortExpression<Product, DateTime>(x => x.CreatedAt),
            "stock" => SortExpressionBuilder.BuildSortExpression<Product, int>(x => x.StockQuantity),
            _ => SortExpressionBuilder.BuildSortExpression<Product, int>(x => x.Id)
        };
        
        var query = _productRepository.GetQueryable().Where(p => p.IsActive);
        
        var orderedQuery = descending 
            ? query.OrderByDescending(sortExpression)
            : query.OrderBy(sortExpression);
            
        return await orderedQuery.Include(p => p.Category).ToListAsync();
    }
}

public class ProductSearchCriteria
{
    public string Name { get; set; }
    public decimal? MinPrice { get; set; }
    public decimal? MaxPrice { get; set; }
    public int? CategoryId { get; set; }
}

Custom Repository Implementation

public interface IProductRepository : IRepository<Product>
{
    Task<IEnumerable<Product>> GetLowStockProductsAsync(int threshold);
    Task<decimal> GetAveragePriceAsync();
    Task<IEnumerable<Product>> GetTopSellingProductsAsync(int count);
    Task<bool> ProductExistsAsync(string name);
    Task<IEnumerable<Product>> GetProductsByCategoryAsync(int categoryId);
    Task UpdateStockQuantityAsync(int productId, int newQuantity);
}

public class ProductRepository : Repository<Product>, IProductRepository
{
    public ProductRepository(ApplicationDbContext context) : base(context)
    {
    }
    
    public async Task<IEnumerable<Product>> GetLowStockProductsAsync(int threshold)
    {
        return await Context.Products
            .Where(p => p.IsActive && p.StockQuantity <= threshold)
            .Include(p => p.Category)
            .OrderBy(p => p.StockQuantity)
            .ToListAsync();
    }
    
    public async Task<decimal> GetAveragePriceAsync()
    {
        return await Context.Products
            .Where(p => p.IsActive)
            .AverageAsync(p => p.Price);
    }
    
    public async Task<IEnumerable<Product>> GetTopSellingProductsAsync(int count)
    {
        // Assuming you have a Sales table or similar
        return await Context.Products
            .Where(p => p.IsActive)
            .OrderByDescending(p => p.StockQuantity) // Placeholder for actual sales data
            .Take(count)
            .Include(p => p.Category)
            .ToListAsync();
    }
    
    public async Task<bool> ProductExistsAsync(string name)
    {
        return await Context.Products
            .AnyAsync(p => p.Name.ToLower() == name.ToLower());
    }
    
    public async Task<IEnumerable<Product>> GetProductsByCategoryAsync(int categoryId)
    {
        return await Context.Products
            .Where(p => p.CategoryId == categoryId && p.IsActive)
            .OrderBy(p => p.Name)
            .ToListAsync();
    }
    
    public async Task UpdateStockQuantityAsync(int productId, int newQuantity)
    {
        var product = await Context.Products.FindAsync(productId);
        if (product != null)
        {
            product.StockQuantity = newQuantity;
            product.UpdatedAt = DateTime.UtcNow;
            await Context.SaveChangesAsync();
        }
    }
}

// Register custom repository
services.AddScoped<IProductRepository, ProductRepository>();

Transaction Management

public class OrderProcessingService
{
    private readonly IProductRepository _productRepository;
    private readonly IRepository<Order> _orderRepository;
    private readonly IUnitOfWork _unitOfWork;
    
    public OrderProcessingService(
        IProductRepository productRepository,
        IRepository<Order> orderRepository,
        IUnitOfWork unitOfWork)
    {
        _productRepository = productRepository;
        _orderRepository = orderRepository;
        _unitOfWork = unitOfWork;
    }
    
    public async Task<Order> ProcessOrderAsync(Order order, List<OrderItem> items)
    {
        using var transaction = await _unitOfWork.BeginTransactionAsync();
        
        try
        {
            // Validate and update product stock
            foreach (var item in items)
            {
                var product = await _productRepository.GetByIdAsync(item.ProductId);
                if (product == null)
                    throw new ArgumentException($"Product {item.ProductId} not found");
                
                if (product.StockQuantity < item.Quantity)
                    throw new InvalidOperationException($"Insufficient stock for product {product.Name}");
                
                // Update stock
                product.StockQuantity -= item.Quantity;
                product.UpdatedAt = DateTime.UtcNow;
                _productRepository.Update(product);
            }
            
            // Create order
            order.CreatedAt = DateTime.UtcNow;
            await _orderRepository.AddAsync(order);
            
            // Save all changes within transaction
            await _unitOfWork.SaveChangesAsync();
            
            await transaction.CommitAsync();
            return order;
        }
        catch
        {
            await transaction.RollbackAsync();
            throw;
        }
    }
}

SQL Server Specific Features

public class SqlServerSpecificService
{
    private readonly ApplicationDbContext _context;
    
    public SqlServerSpecificService(ApplicationDbContext context)
    {
        _context = context;
    }
    
    // Using SQL Server full-text search
    public async Task<IEnumerable<Product>> FullTextSearchAsync(string searchTerm)
    {
        return await _context.Products
            .Where(p => EF.Functions.Contains(p.Name, searchTerm) || 
                       EF.Functions.Contains(p.Description, searchTerm))
            .ToListAsync();
    }
    
    // Using SQL Server temporal tables (if configured)
    public async Task<IEnumerable<Product>> GetProductHistoryAsync(int productId)
    {
        return await _context.Products
            .TemporalAll()
            .Where(p => p.Id == productId)
            .OrderBy(p => EF.Property<DateTime>(p, "PeriodStart"))
            .ToListAsync();
    }
    
    // Bulk operations with SQL Server
    public async Task BulkInsertProductsAsync(IEnumerable<Product> products)
    {
        _context.Products.AddRange(products);
        await _context.SaveChangesAsync();
    }
    
    // Using raw SQL for complex operations
    public async Task<decimal> GetTotalSalesByCategoryAsync(int categoryId, DateTime startDate, DateTime endDate)
    {
        var sql = @"
            SELECT ISNULL(SUM(oi.Quantity * oi.Price), 0) 
            FROM OrderItems oi 
            INNER JOIN Products p ON oi.ProductId = p.Id 
            INNER JOIN Orders o ON oi.OrderId = o.Id 
            WHERE p.CategoryId = @categoryId 
            AND o.CreatedAt BETWEEN @startDate AND @endDate";
        
        var result = await _context.Database.SqlQueryRaw<decimal>(sql, 
            new SqlParameter("@categoryId", categoryId),
            new SqlParameter("@startDate", startDate),
            new SqlParameter("@endDate", endDate)).FirstOrDefaultAsync();
            
        return result;
    }
}

🔧 Configuration

Connection String

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=MyAppDb;Trusted_Connection=true;MultipleActiveResultSets=true;TrustServerCertificate=true"
  }
}

Advanced Configuration

services.AddDbContext<ApplicationDbContext>(options =>
{
    options.UseSqlServer(connectionString, sqlOptions =>
    {
        sqlOptions.EnableRetryOnFailure(
            maxRetryCount: 3,
            maxRetryDelay: TimeSpan.FromSeconds(5),
            errorNumbersToAdd: null);
        sqlOptions.CommandTimeout(30);
    });
    
    options.EnableSensitiveDataLogging(isDevelopment);
    options.EnableDetailedErrors(isDevelopment);
    options.LogTo(Console.WriteLine, LogLevel.Information);
});

📊 Query Builder Examples

Where Expression Builder

// Build individual predicates
var pricePred = PredicateBuilder.BuildPredicate<Product, decimal>(x => x.Price, ">", 100);
var namePred = PredicateBuilder.BuildPredicate<Product, string>(x => x.Name, "contains", "Phone");

// Combine with AND
var combined = PredicateBuilder.Combine(pricePred, namePred, "and");

// Use in EF Core query
var results = dbContext.Products.Where(combined).ToList();

Sort Expression Builder

// Build sorting expression for Price
var sortPrice = SortExpressionBuilder.BuildSortExpression<Product, decimal>(x => x.Price);

// Build sorting expression for Name
var sortName = SortExpressionBuilder.BuildSortExpression<Product, string>(x => x.Name);

// Use with EF Core
var sortedByPrice = dbContext.Products.OrderBy(sortPrice).ToList();
var sortedByName = dbContext.Products.OrderByDescending(sortName).ToList();

🏗️ Architecture

The SQL Server repository provides:

• Repository Pattern: Clean separation of data access logic
• Unit of Work: Consistent transaction boundary management
• Query Building: Dynamic query construction with type safety
• Performance: Optimized for SQL Server-specific features
• Scalability: Support for complex queries and efficient pagination

👨‍💻 Author

Frebin Francis
Fingent Technology Solutions Pvt Ltd

📄 License

Copyright © 2025 Fingent Technology Solutions Pvt Ltd