Fingent-Database-Repository-PostgreSQL 1.0.3

Fingent PostgreSQL Database Repository

A clean and extensible repository layer for PostgreSQL in .NET applications. Simplifies database access by providing strongly typed repository classes that integrate seamlessly with Entity Framework Core. Helps enforce separation of concerns, reduce boilerplate code, and standardize data access patterns.

🚀 Features

• Repository Pattern: Clean abstraction over PostgreSQL data access
• Entity Framework Core: Built on EF Core for PostgreSQL with Npgsql provider
• Generic Repository: Base repository with common CRUD operations
• Unit of Work: Transaction management and change tracking
• Async Support: Full async/await pattern support for better performance
• Clean Architecture: Promotes separation of concerns and testability
• Query Builders: Advanced where clause and sorting expression builders
• Pagination Support: Built-in pagination with efficient queries

📦 Installation

dotnet add package Fingent-Database-Repository-PostgreSQL
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL

🔧 Dependencies

• .NET 8.0
• Microsoft.EntityFrameworkCore 8.0.8
• Npgsql.EntityFrameworkCore.PostgreSQL (recommended)
• Fingent.Shared.Core

💻 Usage

Basic Setup

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

services.AddPostgreSqlRepository<ApplicationDbContext>();

Entity Configuration

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public DateTime CreatedAt { get; set; }
    public bool IsActive { get; set; }
    public List<Order> Orders { get; set; } = new();
}

public class Order
{
    public int Id { get; set; }
    public int UserId { get; set; }
    public User User { get; set; }
    public decimal Amount { get; set; }
    public DateTime OrderDate { get; set; }
}

public class ApplicationDbContext : DbContext
{
    public DbSet<User> Users { get; set; }
    public DbSet<Order> Orders { get; set; }
    
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) 
        : base(options)
    {
    }
    
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // PostgreSQL specific configurations
        modelBuilder.Entity<User>(entity =>
        {
            entity.HasKey(e => e.Id);
            entity.Property(e => e.Id).ValueGeneratedOnAdd();
            entity.Property(e => e.Name).IsRequired().HasMaxLength(100);
            entity.Property(e => e.Email).IsRequired().HasMaxLength(255);
            entity.HasIndex(e => e.Email).IsUnique();
            entity.Property(e => e.CreatedAt).HasDefaultValueSql("CURRENT_TIMESTAMP");
        });
        
        modelBuilder.Entity<Order>(entity =>
        {
            entity.HasKey(e => e.Id);
            entity.Property(e => e.Id).ValueGeneratedOnAdd();
            entity.Property(e => e.Amount).HasColumnType("decimal(18,2)");
            entity.HasOne(e => e.User)
                  .WithMany(u => u.Orders)
                  .HasForeignKey(e => e.UserId);
        });
        
        base.OnModelCreating(modelBuilder);
    }
}

Repository Usage

public class UserService
{
    private readonly IRepository<User> _userRepository;
    private readonly IUnitOfWork _unitOfWork;
    private readonly ILogger<UserService> _logger;
    
    public UserService(
        IRepository<User> userRepository, 
        IUnitOfWork unitOfWork,
        ILogger<UserService> logger)
    {
        _userRepository = userRepository;
        _unitOfWork = unitOfWork;
        _logger = logger;
    }
    
    public async Task<User> CreateUserAsync(User user)
    {
        user.CreatedAt = DateTime.UtcNow;
        await _userRepository.AddAsync(user);
        await _unitOfWork.SaveChangesAsync();
        
        _logger.LogInformation("User created with ID: {UserId}", user.Id);
        return user;
    }
    
    public async Task<User> GetUserAsync(int id)
    {
        return await _userRepository.GetByIdAsync(id);
    }
    
    public async Task<User> GetUserWithOrdersAsync(int id)
    {
        return await _userRepository.GetByIdAsync(id, includes: u => u.Orders);
    }
    
    public async Task<IEnumerable<User>> GetActiveUsersAsync()
    {
        return await _userRepository.FindAsync(u => u.IsActive);
    }
    
    public async Task<PagingResult<User>> GetUsersPaginatedAsync(int page, int pageSize)
    {
        return await _userRepository.GetPagedAsync(
            page: page,
            pageSize: pageSize,
            orderBy: q => q.OrderBy(u => u.Name)
        );
    }
    
    public async Task UpdateUserAsync(User user)
    {
        _userRepository.Update(user);
        await _unitOfWork.SaveChangesAsync();
        
        _logger.LogInformation("User updated: {UserId}", user.Id);
    }
    
    public async Task DeleteUserAsync(int id)
    {
        var user = await _userRepository.GetByIdAsync(id);
        if (user != null)
        {
            _userRepository.Delete(user);
            await _unitOfWork.SaveChangesAsync();
            
            _logger.LogInformation("User deleted: {UserId}", id);
        }
    }
}

Advanced Query Building

public class AdvancedUserService
{
    private readonly IRepository<User> _userRepository;
    
    public AdvancedUserService(IRepository<User> userRepository)
    {
        _userRepository = userRepository;
    }
    
    // Using WhereBuilder for dynamic queries
    public async Task<IEnumerable<User>> SearchUsersAsync(string nameFilter, string emailFilter, bool? isActive)
    {
        var predicate = PredicateBuilder.True<User>();
        
        if (!string.IsNullOrEmpty(nameFilter))
        {
            var namePred = PredicateBuilder.BuildPredicate<User, string>(
                x => x.Name, "contains", nameFilter);
            predicate = PredicateBuilder.Combine(predicate, namePred, "and");
        }
        
        if (!string.IsNullOrEmpty(emailFilter))
        {
            var emailPred = PredicateBuilder.BuildPredicate<User, string>(
                x => x.Email, "contains", emailFilter);
            predicate = PredicateBuilder.Combine(predicate, emailPred, "and");
        }
        
        if (isActive.HasValue)
        {
            var activePred = PredicateBuilder.BuildPredicate<User, bool>(
                x => x.IsActive, "=", isActive.Value);
            predicate = PredicateBuilder.Combine(predicate, activePred, "and");
        }
        
        return await _userRepository.FindAsync(predicate);
    }
    
    // Using SortExpressionBuilder for dynamic sorting
    public async Task<IEnumerable<User>> GetSortedUsersAsync(string sortBy, bool descending = false)
    {
        var sortExpression = sortBy.ToLower() switch
        {
            "name" => SortExpressionBuilder.BuildSortExpression<User, string>(x => x.Name),
            "email" => SortExpressionBuilder.BuildSortExpression<User, string>(x => x.Email),
            "created" => SortExpressionBuilder.BuildSortExpression<User, DateTime>(x => x.CreatedAt),
            _ => SortExpressionBuilder.BuildSortExpression<User, int>(x => x.Id)
        };
        
        var query = _userRepository.GetQueryable();
        
        var orderedQuery = descending 
            ? query.OrderByDescending(sortExpression)
            : query.OrderBy(sortExpression);
            
        return await orderedQuery.ToListAsync();
    }
}

Custom Repository Implementation

public interface IUserRepository : IRepository<User>
{
    Task<User> GetByEmailAsync(string email);
    Task<IEnumerable<User>> GetUsersWithRecentOrdersAsync(int days);
    Task<decimal> GetTotalOrderValueByUserAsync(int userId);
    Task<bool> EmailExistsAsync(string email);
}

public class UserRepository : Repository<User>, IUserRepository
{
    public UserRepository(ApplicationDbContext context) : base(context)
    {
    }
    
    public async Task<User> GetByEmailAsync(string email)
    {
        return await Context.Users
            .FirstOrDefaultAsync(u => u.Email.ToLower() == email.ToLower());
    }
    
    public async Task<IEnumerable<User>> GetUsersWithRecentOrdersAsync(int days)
    {
        var cutoffDate = DateTime.UtcNow.AddDays(-days);
        
        return await Context.Users
            .Where(u => u.Orders.Any(o => o.OrderDate >= cutoffDate))
            .Include(u => u.Orders.Where(o => o.OrderDate >= cutoffDate))
            .ToListAsync();
    }
    
    public async Task<decimal> GetTotalOrderValueByUserAsync(int userId)
    {
        return await Context.Orders
            .Where(o => o.UserId == userId)
            .SumAsync(o => o.Amount);
    }
    
    public async Task<bool> EmailExistsAsync(string email)
    {
        return await Context.Users
            .AnyAsync(u => u.Email.ToLower() == email.ToLower());
    }
}

// Register custom repository
services.AddScoped<IUserRepository, UserRepository>();

Transaction Management

public class OrderService
{
    private readonly IRepository<Order> _orderRepository;
    private readonly IRepository<User> _userRepository;
    private readonly IUnitOfWork _unitOfWork;
    
    public OrderService(
        IRepository<Order> orderRepository,
        IRepository<User> userRepository,
        IUnitOfWork unitOfWork)
    {
        _orderRepository = orderRepository;
        _userRepository = userRepository;
        _unitOfWork = unitOfWork;
    }
    
    public async Task<Order> CreateOrderWithUserUpdateAsync(Order order, User userUpdate)
    {
        using var transaction = await _unitOfWork.BeginTransactionAsync();
        
        try
        {
            // Create order
            await _orderRepository.AddAsync(order);
            
            // Update user
            _userRepository.Update(userUpdate);
            
            // Save all changes within transaction
            await _unitOfWork.SaveChangesAsync();
            
            await transaction.CommitAsync();
            return order;
        }
        catch
        {
            await transaction.RollbackAsync();
            throw;
        }
    }
}

PostgreSQL Specific Features

public class PostgreSqlSpecificService
{
    private readonly ApplicationDbContext _context;
    
    public PostgreSqlSpecificService(ApplicationDbContext context)
    {
        _context = context;
    }
    
    // Using PostgreSQL arrays
    public async Task<IEnumerable<User>> GetUsersByIdsAsync(int[] userIds)
    {
        return await _context.Users
            .Where(u => userIds.Contains(u.Id))
            .ToListAsync();
    }
    
    // Using PostgreSQL JSON operations (if using JSONB columns)
    public async Task<IEnumerable<User>> SearchByJsonPropertyAsync(string jsonProperty, string value)
    {
        return await _context.Users
            .Where(u => EF.Functions.JsonContains(u.Metadata, $@"{{""{{jsonProperty}}"": ""{value}""}}""))
            .ToListAsync();
    }
    
    // Using PostgreSQL full-text search
    public async Task<IEnumerable<User>> FullTextSearchAsync(string searchTerm)
    {
        return await _context.Users
            .Where(u => EF.Functions.ToTsVector(u.Name + " " + u.Email)
                .Matches(EF.Functions.ToTsQuery(searchTerm)))
            .ToListAsync();
    }
    
    // Bulk operations with PostgreSQL
    public async Task BulkInsertUsersAsync(IEnumerable<User> users)
    {
        _context.Users.AddRange(users);
        await _context.SaveChangesAsync();
    }
}

🔧 Configuration

Connection String

{
  "ConnectionStrings": {
    "DefaultConnection": "Host=localhost;Database=myapp;Username=postgres;Password=password;Port=5432"
  }
}

Advanced Configuration

services.AddDbContext<ApplicationDbContext>(options =>
{
    options.UseNpgsql(connectionString, npgsqlOptions =>
    {
        npgsqlOptions.EnableRetryOnFailure(
            maxRetryCount: 3,
            maxRetryDelay: TimeSpan.FromSeconds(5),
            errorCodesToAdd: null);
        npgsqlOptions.CommandTimeout(30);
    });
    
    options.EnableSensitiveDataLogging(isDevelopment);
    options.EnableDetailedErrors(isDevelopment);
});

📊 Query Builder Examples

Where Expression Builder

// Example from Readme.txt
public class Person
{
    public int Age { get; set; }
    public string Name { get; set; }
}

// Build individual predicates
var agePred = PredicateBuilder.BuildPredicate<Person, int>(x => x.Age, ">", 18);
var namePred = PredicateBuilder.BuildPredicate<Person, string>(x => x.Name, "contains", "John");

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

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

Sort Expression Builder

// Build sorting expression for Age
var sortAge = SortExpressionBuilder.BuildSortExpression<Person, int>(x => x.Age);

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

// Use with EF Core
var sorted1 = dbContext.People.OrderBy(sortAge).ToList();
var sorted2 = dbContext.People.OrderByDescending(sortName).ToList();

🏗️ Architecture

The PostgreSQL repository provides:

• Repository Pattern: Abstraction over data access logic
• Unit of Work: Consistent transaction boundary management
• Query Building: Dynamic query construction capabilities
• Performance: Optimized for PostgreSQL-specific features
• Scalability: Support for complex queries and bulk operations

👨‍💻 Author

Frebin Francis
Fingent Technology Solutions Pvt Ltd

📄 License

Copyright © 2025 Fingent Technology Solutions Pvt Ltd