Why should you care?

ShopNest search at Flipkart scale fails if every product triggers separate category query — optimized LINQ keeps p95 latency low.

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).

// Bad: N+1 if looping Include
// Good: project in one query
var items = await _context.Products
    .Where(p => p.IsPublished)
    .Select(p => new
    {
        p.Id,
        p.Name,
        p.Price,
        Category = p.Category!.Name
    })
    .TagWith("PublishedProductList")
    .ToListAsync();

What happened?

• Select with Category.Name becomes JOIN — one round trip.
• TagWith labels SQL in logs for profiling.
• No tracked entities for read-only list.

Practice next

1. Enable SQL logging and count queries per request.
2. Replace Include loops with Select projection on hot paths.
3. Add indexes matching Where and OrderBy columns.
4. Add AsNoTracking to read-only hot query and compare memory.
5. Run same query with and without composite index on CategoryId+IsPublished.

Remember

Optimize by reducing queries and columns. Project to DTOs on read-heavy APIs. Measure with logs and execution plans.

ShopNest search latency fix

Team replaces per-item category lookup with projected JOIN — queries drop from 101 to 1.

Outcome: P95 search latency falls from 800ms to 90ms under load test.