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:
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
1---2name: linq-optimization-patterns3description: "LINQ and EF Core query optimization for ABP Framework. N+1 prevention, eager loading, projections, pagination."4layer: 25tech_stack: [dotnet, csharp, efcore, postgresql]6topics: [query-optimization, n-plus-one, eager-loading, projections, performance]7depends_on: []8complements: [efcore-patterns, abp-framework-patterns]9keywords: [Include, ThenInclude, Select, AsNoTracking, AsSplitQuery, WhereIf, N+1, CountAsync]10metadata:11 source: https://skillmd.com/skills/thapaliyabikendra/linq-optimization-patterns12---1314# LINQ Optimization Patterns1516Optimize LINQ queries and prevent performance anti-patterns in EF Core/ABP applications.1718---1920## Summary2122This skill covers efficient data access patterns for Entity Framework Core in ABP Framework applications. Focus areas: N+1 prevention, pagination, projections, and batch loading.2324**When to use**: Reviewing queries, fixing slow endpoints, implementing list APIs.2526---2728## Concepts2930| Concept | Description | Details |31|---------|-------------|---------|32| Eager Loading | Load related entities in single query via JOIN | [patterns/eager-loading.md] |33| Projection | Transform to DTO at database level | [patterns/projection.md] |34| Batch Loading | Load related data for multiple parents in one query | [patterns/batch-loading.md] |35| Pagination | Efficient paging with count optimization | [patterns/pagination.md] |3637---3839## Anti-Patterns4041| Anti-Pattern | Severity | Detect | Impact | Details |42|--------------|----------|--------|--------|---------|43| N+1 Query | 🔴 HIGH | `foreach.*await.*Repo` | N+1 DB calls | [anti-patterns/n-plus-one.md] |44| Count After Pagination | 🔴 HIGH | `Count.*after.*ToList` | Double query | [anti-patterns/count-after-pagination.md] |45| Full Table Load | 🔴 HIGH | `GetListAsync()` then filter | Memory explosion | [anti-patterns/full-table-load.md] |46| In-Memory Join | 🔴 HIGH | Multiple `ToListAsync` | Cartesian in memory | [anti-patterns/in-memory-join.md] |4748---4950## Decision Tree5152```53Need related data for display?54├─ Single entity ──────────► Include() → [patterns/eager-loading.md]55├─ List of entities ───────► Batch load → [patterns/batch-loading.md]56└─ Only specific fields ───► Projection → [patterns/projection.md]5758Need paginated list with count?59└─ Always count FIRST ─────► CountAsync() → [patterns/pagination.md]6061Loading data then filtering?62├─ Filter in query ────────► WhereIf() → ✅ Correct63└─ Filter after ToList ────► ❌ Anti-pattern → [anti-patterns/full-table-load.md]64```6566---6768## Quick Detection6970Run these to find issues in your codebase:7172```bash73# N+1: Query inside loop74grep -rn "foreach.*await.*Repository\|for.*await.*GetAsync" src/7576# Count after pagination77grep -rn "\.Count().*ToList\|ToList.*\.Count()" src/7879# Full table load80grep -rn "GetListAsync()" src/ | grep -v "Where\|Any\|First"8182# In-memory filtering83grep -rn "ToListAsync().*\.Where\|GetListAsync.*\.Where" src/84```8586---8788## Rules8990| ID | Rule | Related |91|----|------|---------|92| R001 | Execute `CountAsync()` before `Skip/Take` | [anti-patterns/count-after-pagination.md] |93| R002 | Apply `Where` clauses before `ToListAsync()` | [anti-patterns/full-table-load.md] |94| R003 | Load related entities via Include or batch, not in loops | [anti-patterns/n-plus-one.md] |95| R004 | Use `Select()` projection for DTO returns | [patterns/projection.md] |96| R005 | Use `AsNoTracking()` for read-only queries | [patterns/projection.md] |97| R006 | Use `AsSplitQuery()` for multiple collection includes | [patterns/eager-loading.md] |9899---100101## Checklist102103Review checklist for LINQ queries:104105- [ ] No `foreach`/`for` with `await` repository calls inside106- [ ] `CountAsync()` executed before `Skip/Take`107- [ ] No `GetListAsync()` followed by `.Where()` in memory108- [ ] No `FirstOrDefault` inside `Select` projections109- [ ] Related data loaded via `Include` or batch query110- [ ] `Select()` projection used for DTO returns111- [ ] `AsNoTracking()` used for read-only queries112- [ ] Multiple collection includes use `AsSplitQuery()`113114---115116## Integration Points117118This skill is used by:119- **abp-code-reviewer**: Query performance validation120- **abp-developer**: Efficient data access implementation121- **debugger**: Performance issue diagnosis122123---124125## Related Skills126127- [efcore-patterns](../efcore-patterns/SKILL.md) - Entity configuration, migrations128- [abp-framework-patterns](../abp-framework-patterns/SKILL.md) - Repository patterns129- [dotnet-async-patterns](../dotnet-async-patterns/SKILL.md) - Async/await correctness130131---