Why should you care?

ShopNest category pages show cheapest-first or newest-first — sort must happen in SQL before pagination slices rows.

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 sorted = await _context.Products
    .Where(p => p.CategoryId == catId)
    .OrderBy(p => p.StockQty == 0) // in-stock first
    .ThenByDescending(p => p.CreatedAt)
    .ThenBy(p => p.Name)
    .Skip(page * pageSize)
    .Take(pageSize)
    .ToListAsync();

What happened?

• OrderBy on boolean puts in-stock first.
• ThenBy adds tie-breakers.
• Skip/Take become OFFSET/FETCH in SQL Server — sort must precede paging.

Practice next

1. Always OrderBy before Skip/Take for stable pages.
2. Use ThenBy for secondary sort keys.
3. Index columns in ORDER BY for large tables.
4. Sort by Price descending then Name ascending for tie-break.
5. Measure query plan with vs without index on CreatedAt.

Remember

OrderBy/ThenBy become SQL ORDER BY. Sort before Skip/Take pagination. Stable sort keys prevent duplicate/missing pages.

ShopNest sort options

Category API accepts sort=newest|price_asc mapped to ThenBy chains.

Outcome: Consistent paging when customers flip sort mid-browse.