LINQ Optimization Patterns
Optimize LINQ queries and prevent performance anti-patterns in EF Core/ABP applications.
Summary
This skill covers efficient data access patterns for Entity Framework Core in ABP Framework applications. Focus areas: N+1 prevention, pagination, projections, and batch loading.
When to use: Reviewing queries, fixing slow endpoints, implementing list APIs.
Concepts
| Concept | Description | Details |
|---|---|---|
| Eager Loading | Load related entities in single query via JOIN | [patterns/eager-loading.md] |
| Projection | Transform to DTO at database level | [patterns/projection.md] |
| Batch Loading | Load related data for multiple parents in one query | [patterns/batch-loading.md] |
| Pagination | Efficient paging with count optimization | [patterns/pagination.md] |
Anti-Patterns
| Anti-Pattern | Severity | Detect | Impact | Details |
|---|---|---|---|---|
| N+1 Query | 🔴 HIGH | foreach.*await.*Repo | N+1 DB calls | [anti-patterns/n-plus-one.md] |
| Count After Pagination | 🔴 HIGH | Count.*after.*ToList | Double query | [anti-patterns/count-after-pagination.md] |
| Full Table Load | 🔴 HIGH | GetListAsync() then filter | Memory explosion | [anti-patterns/full-table-load.md] |
| In-Memory Join | 🔴 HIGH | Multiple ToListAsync | Cartesian in memory | [anti-patterns/in-memory-join.md] |
Decision Tree
Need related data for display?
├─ Single entity ──────────► Include() → [patterns/eager-loading.md]
├─ List of entities ───────► Batch load → [patterns/batch-loading.md]
└─ Only specific fields ───► Projection → [patterns/projection.md]
Need paginated list with count?
└─ Always count FIRST ─────► CountAsync() → [patterns/pagination.md]
Loading data then filtering?
├─ Filter in query ────────► WhereIf() → ✅ Correct
└─ Filter after ToList ────► ❌ Anti-pattern → [anti-patterns/full-table-load.md]Quick Detection
Run these to find issues in your codebase:
# N+1: Query inside loop
grep -rn "foreach.*await.*Repository\|for.*await.*GetAsync" src/
# Count after pagination
grep -rn "\.Count().*ToList\|ToList.*\.Count()" src/
# Full table load
grep -rn "GetListAsync()" src/ | grep -v "Where\|Any\|First"
# In-memory filtering
grep -rn "ToListAsync().*\.Where\|GetListAsync.*\.Where" src/Rules
| ID | Rule | Related |
|---|---|---|
| R001 | Execute CountAsync() before Skip/Take | [anti-patterns/count-after-pagination.md] |
| R002 | Apply Where clauses before ToListAsync() | [anti-patterns/full-table-load.md] |
| R003 | Load related entities via Include or batch, not in loops | [anti-patterns/n-plus-one.md] |
| R004 | Use Select() projection for DTO returns | [patterns/projection.md] |
| R005 | Use AsNoTracking() for read-only queries | [patterns/projection.md] |
| R006 | Use AsSplitQuery() for multiple collection includes | [patterns/eager-loading.md] |
Checklist
Review checklist for LINQ queries:
- No
foreach/forwithawaitrepository calls inside CountAsync()executed beforeSkip/Take- No
GetListAsync()followed by.Where()in memory - No
FirstOrDefaultinsideSelectprojections - Related data loaded via
Includeor batch query Select()projection used for DTO returnsAsNoTracking()used for read-only queries- Multiple collection includes use
AsSplitQuery()
Integration Points
This skill is used by:
- abp-code-reviewer: Query performance validation
- abp-developer: Efficient data access implementation
- debugger: Performance issue diagnosis
Related Skills
- efcore-patterns - Entity configuration, migrations
- abp-framework-patterns - Repository patterns
- dotnet-async-patterns - Async/await correctness