Why should you care?

ShopNest category browse LINQ looks fine — DBA analysis shows scan on 2M rows because EF translated Contains unexpectedly.

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 sql = _context.Products
    .Where(p => categoryIds.Contains(p.CategoryId))
    .OrderBy(p => p.Name)
    .ToQueryString();

// Paste sql into SSMS → Include Actual Execution Plan → F5
// Look for: Index Seek vs Scan, Key Lookup, Sort cost, Warnings

What happened?

• ToQueryString outputs parameterized SQL EF will run.
• SSMS plan reveals whether Contains became IN seek or table scan — guides index or LINQ rewrite.

Practice next

1. Copy ToQueryString output to SSMS against staging data volume.
2. Enable actual execution plan before running.
3. Check missing index suggestions — validate with DBA not blindly.
4. Compare plan before/after composite index on CategoryId+Name.
5. Use SET STATISTICS IO ON in SSMS to compare logical reads.

Remember

ToQueryString bridges LINQ to SSMS. Execution plan shows seek vs scan. Analyze on realistic row counts.

ShopNest DBA pairing

Dev shares ToQueryString and plan screenshot for slow search — DBA adds filtered index.

Outcome: Logical reads drop 90%; no LINQ change required.