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/for with await repository calls inside
• CountAsync() executed before Skip/Take
• No GetListAsync() followed by .Where() in memory
• No FirstOrDefault inside Select projections
• Related data loaded via Include or batch query
• Select() projection used for DTO returns
• AsNoTracking() 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

---