---
name: linq-optimization-patterns
description: "LINQ and EF Core query optimization for ABP Framework. N+1 prevention, eager loading, projections, pagination."
layer: 2
tech_stack: [dotnet, csharp, efcore, postgresql]
topics: [query-optimization, n-plus-one, eager-loading, projections, performance]
depends_on: []
complements: [efcore-patterns, abp-framework-patterns]
keywords: [Include, ThenInclude, Select, AsNoTracking, AsSplitQuery, WhereIf, N+1, CountAsync]
---

# 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:

```bash
# 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](../efcore-patterns/SKILL.md) - Entity configuration, migrations
- [abp-framework-patterns](../abp-framework-patterns/SKILL.md) - Repository patterns
- [dotnet-async-patterns](../dotnet-async-patterns/SKILL.md) - Async/await correctness

---