linq-optimization-patterns

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.

---

| 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-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] |

---

```

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]

```

---

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/

```

---

| 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] |

---

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()

---

This skill is used by:

• abp-code-reviewer: Query performance validation
• abp-developer: Efficient data access implementation
• debugger: Performance issue diagnosis

---

• [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

---