🚀 Feature: Implement Database-Agnostic Repository Layer with Type-Safe Fluent Queries

[pournasserian]

🎯 Feature Request

Implement a comprehensive database-agnostic repository abstraction layer that supports multiple database types (RDBMS via EF Core, MongoDB, LiteDB) with type-safe fluent query interfaces and compile-time ordering validation.

📋 Proposed Implementation

Core Abstractions

// Generic repository interface
public interface IRepository<TEntity> : IRepository where TEntity : class, IEntity
{
    // CRUD operations
    Task<TEntity> Add(TEntity entity, CancellationToken cancellationToken = default);
    Task<IEnumerable<TEntity>> AddRange(IEnumerable<TEntity> entities, CancellationToken cancellationToken = default);
    Task<TEntity> Update(TEntity entity, CancellationToken cancellationToken = default);
    Task<TEntity?> Remove(Guid id, CancellationToken cancellationToken = default);
    
    // Bulk operations
    Task<int> UpdateRange(ISpecification<TEntity> specification, Expression<Func<TEntity, TEntity>> updateExpression, CancellationToken cancellationToken = default);
    Task<int> RemoveRange(ISpecification<TEntity> specification, CancellationToken cancellationToken = default);
    
    // Query operations
    Task<IEnumerable<TEntity>> Query(ISpecification<TEntity> specification, CancellationToken cancellationToken = default);
    Task<IPagedResult<TEntity>> FindPaged(IFluentOrderedQuery<TEntity> orderedQuery, int page, int pageSize, CancellationToken cancellationToken = default);
}

// Fluent query interfaces
public interface IFluentQuery<T> where T : class
{
    IFluentQuery<T> Where(Expression<Func<T, bool>> predicate);
    IFluentOrderedQuery<T> OrderBy<TKey>(Expression<Func<T, TKey>> keySelector);
    IFluentOrderedQuery<T> OrderByDescending<TKey>(Expression<Func<T, TKey>> keySelector);
    // No Skip/Take here - forces ordering first!
}

public interface IFluentOrderedQuery<T> : IFluentQuery<T> where T : class
{
    IFluentOrderedQuery<T> ThenBy<TKey>(Expression<Func<T, TKey>> keySelector);
    IFluentOrderedQuery<T> Skip(int count);  // Only available after ordering!
    IFluentOrderedQuery<T> Take(int count);  // Only available after ordering!
}

Database Management

// Database-agnostic connection management
public interface IDatabaseManager<T> : IDatabaseManager where T : IDatabaseManagerMarker
{
    string ConnectionString { get; }
    Task<bool> EntitySetsExist(IEnumerable<string> entitySetNames, CancellationToken cancellationToken = default);
}

// Database initialization
public interface IDataSeeder
{
    int Priority { get; }
    Task<bool> HasData(CancellationToken cancellationToken = default);
    Task SeedData(CancellationToken cancellationToken = default);
}

public interface ISchemaValidator
{
    int Priority { get; }
    Task<bool> ValidateSchema(CancellationToken cancellationToken = default);
    Task CreateSchema(CancellationToken cancellationToken = default);
}

💡 Key Benefits

1. Compile-Time Ordering Safety

// ✅ This compiles - proper ordering
var pagedResults = await repo.Query()
    .Where(x => x.IsActive)
    .OrderBy(x => x.CreatedDate)
    .Skip(20)
    .Take(10)
    .Query();

// ❌ This won't compile - no ordering
var invalid = repo.Query()
    .Where(x => x.IsActive)
    .Skip(20); // Compile error: Skip not available on IFluentQuery<T>!

2. Database-Agnostic Design

• Consistent API across RDBMS, MongoDB, and LiteDB
• Easy database switching without code changes
• Database-specific optimizations under the hood

3. Enhanced Developer Experience

• IntelliSense-guided query building
• Fluent API similar to LINQ
• Immediate execution capabilities
• No runtime ordering errors

4. Performance Features

• Bulk operations for mass data changes
• Efficient pagination with guaranteed ordering
• Expression-based query building
• Immutable query construction

🔧 Technical Specifications

• Target Framework: .NET 9
• Pattern: Repository + Specification + Fluent Query
• Database Support:
  • EF Core (SQL Server, PostgreSQL, MySQL, SQLite)
  • MongoDB (Document database)
  • LiteDB (Embedded database)
• Features: Async/await throughout, comprehensive cancellation token support
• Architecture: Immutable query building, expression-based specifications

📝 Implementation Phases

Phase 1: Core Abstractions ✅

• IRepository<TEntity> interface with CRUD operations
• IFluentQuery<T> and IFluentOrderedQuery<T> interfaces
• ISpecification<T> pattern implementation
• Database manager interfaces
• Bulk operations support
• Extension methods for fluent API

Phase 2: Database Implementations

• EF Core Repository

  • DbContext integration
  • Transaction support
  • Change tracking optimization
  • Include/ThenInclude support

• MongoDB Repository

  • MongoDB.Driver integration
  • Aggregation pipeline support
  • Document-specific optimizations
  • GridFS support for file storage

• LiteDB Repository

  • Embedded database scenarios
  • File-based storage
  • Lightweight transaction support
  • Cross-platform compatibility

Phase 3: Advanced Features

• Multi-database transaction coordination
• Query caching layer
• Performance monitoring and metrics
• Database migration tools
• Connection pooling optimization

📊 Usage Examples

Basic Usage

// Simple query
var activeUsers = await userRepository.Query()
    .Where(x => x.IsActive)
    .Query();

// Complex ordered query with pagination
var pagedUsers = await userRepository.Query()
    .Where(x => x.Role == "Manager")
    .Where(x => x.IsActive)
    .OrderBy(x => x.Name)
    .ThenBy(x => x.CreatedDate)
    .Skip(20)
    .Take(10)
    .Query();

// Type-safe pagination
var orderedQuery = userRepository.Query()
    .Where(x => x.IsActive)
    .OrderBy(x => x.CreatedDate);
    
var pagedResult = await userRepository.FindPaged(orderedQuery, page: 1, pageSize: 10);

Bulk Operations

// Bulk update
var specification = new ExpressionSpecification<User>(x => x.IsActive == false);
await userRepository.UpdateRange(specification, x => x with { LastLoginDate = DateTime.UtcNow });

// Bulk delete
await userRepository.RemoveRange(specification);

Database Switching

// Configuration-driven database selection
services.AddDatabaseManager(options =>
{
    options.MapLibrary<IUserLibraryMarker>().UseSqlServer(connectionString);
    options.MapLibrary<IProductLibraryMarker>().UseMongoDB(mongoConnectionString);
    options.MapLibrary<ICacheLibraryMarker>().UseLiteDB(liteDbPath);
});

✅ Acceptance Criteria

• Core abstractions implemented with comprehensive interfaces
• EF Core repository with transaction support
• MongoDB repository with aggregation pipeline support
• LiteDB repository for embedded scenarios
• Compile-time ordering validation working correctly
• Bulk operations implemented for all database types
• Extension methods providing fluent API experience
• Unit tests achieving 90%+ code coverage
• Integration tests for all database implementations
• Performance benchmarks comparing database types
• Comprehensive documentation with examples
• Database migration and seeding tools

🧪 Testing Strategy

• Unit Tests: Core abstractions, query building, specifications
• Integration Tests: Database implementations, transaction scenarios
• Performance Tests: Bulk operations, pagination, complex queries
• Compatibility Tests: Cross-platform, different database versions

📚 Documentation Needs

• Architecture overview and design decisions
• Getting started guide for each database type
• Advanced usage patterns and best practices
• Migration guide from existing data access patterns
• Performance tuning recommendations
• Troubleshooting guide

🔗 Related Issues

• Database connection management
• Schema migration tools
• Performance monitoring integration
• Multi-tenant database support

---

Priority: High
Complexity: Medium-High
Estimated Effort: 3-4 weeks
Dependencies: None

This implementation will provide FluentCMS with a robust, type-safe, and database-agnostic data access layer that supports modern development practices while maintaining excellent performance across different database technologies.