Why should you care?

ShopNest homepage shows product count and average rating without loading every row into the API process.

See it live — copy this example

Paste into a .NET project with EF Core packages, then run with LocalDB/SQL Server (dotnet ef / dotnet run).

var stats = await _context.Products
    .Where(p => p.CategoryId == catId && p.IsPublished)
    .GroupBy(p => 1)
    .Select(g => new CategoryStatsDto
    {
        ProductCount = g.Count(),
        AvgPrice = g.Average(p => p.Price),
        MaxPrice = g.Max(p => p.Price)
    })
    .FirstAsync();

What happened?

• GroupBy constant 1 aggregates entire filtered set into one row.
• Count/Average/Max become SQL aggregates returning scalars.

Practice next

1. Use CountAsync on filtered IQueryable for existence totals.
2. Combine aggregates in one Select to avoid multiple round trips.
3. Handle empty sets — Average on zero rows throws.
4. Add Min(p => p.StockQty) for inventory alert threshold.
5. Use Sum on OrderItems for category revenue without GroupBy when single total needed.

Remember

Aggregates run server-side in SQL. Batch aggregates in one query when possible. Use async scalar methods for API endpoints.

ShopNest category header stats

Category page header shows count and average price from one aggregate query.

Outcome: Header renders instantly without scanning full product list in API.