// This complex query combines multiple LINQ operations:
var recentDate = DateTime.UtcNow.AddMonths(-6);

var results = await _context.DemoProducts
    // 1. Filtering - Recent, active, rated, in-stock products
    .Where(p => p.IsActive && 
               p.CreatedDate >= recentDate &&
               p.Rating >= 4.0 &&
               p.StockQuantity > 0)
    
    // 2. Grouping - By category AND dynamically calculated price range
    .GroupBy(p => new { 
        p.Category, 
        PriceRange = p.Price < 250 ? "Budget" 
                   : p.Price < 500 ? "Mid-Range" 
                   : "Premium" 
    })
    
    // 3. Aggregation - Calculate statistics per group
    .Select(g => new ComplexQueryResultDto
    {
        Category = g.Key.Category,
        PriceRange = g.Key.PriceRange,
        ProductCount = g.Count(),
        AverageRating = g.Average(p => p.Rating),
        AveragePrice = g.Average(p => p.Price),
        TotalValue = g.Sum(p => p.Price * p.StockQuantity)
    })
    
    // 4. Sorting - Multi-level ordering
    .OrderBy(r => r.Category)
    .ThenBy(r => r.PriceRange)
    .ToListAsync();