CountBy, AggregateBy, Index() and EF Core translation pitfalls.

LINQ in .NET 9 — Deferred Execution & IQueryable

LINQ wraps enumerable data in composable query operators with deferred execution — the query only runs when iterated. IEnumerable<T> executes client-side (LINQ-to-Objects). IQueryable<T> translates the expression tree to SQL in EF Core. .NET 9 adds CountBy (group-count in one pass), AggregateBy (group-aggregate), and Index() (index-value pairs). Query syntax vs method syntax are equivalent; the compiler translates both.

Interactive Pipeline Flow

FoundationCore LogicProduction

📦 Source — IEnumerable / IQueryable

Any IEnumerable<T> is a LINQ source. IQueryable<T> adds an expression tree that providers (EF Core, custom LINQ providers) translate to native queries.

Architecture Diagrams

CountBy and AggregateBy: group operations in a single pass.

Loading diagram...

Key Design Decisions

🚫Never call AsEnumerable() before Filter

ctx.Orders.AsEnumerable().Where(...) loads ALL rows into memory first. Always filter while still IQueryable.

📊Use CountBy over GroupBy+Count

.NET 9 CountBy is 30%+ faster for large collections — it counts in a single pass without allocating groups.

🔍Check EF translation with ToQueryString()

Call query.ToQueryString() to inspect the generated SQL in development. Untranslatable expressions silently fall back to client evaluation.

Discussion