dotnet-linq-optimization

name	dotnet-linq-optimization
category	performance
subcategory	patterns
description	Optimizes LINQ queries. IQueryable vs IEnumerable, compiled queries, deferred exec, allocations.
license	MIT
targets	["*"]
tags	["csharp","dotnet","skill"]
version	0.0.1
author	dotnet-agent-harness
invocable	true
claudecode	{"allowed-tools":["Read","Grep","Glob","Bash","Write","Edit"]}
codexcli	{"short-description":".NET skill guidance for csharp tasks"}
opencode	{"allowed-tools":["Read","Grep","Glob","Bash","Write","Edit"]}
copilot	{}
geminicli	{}
antigravity	{}

dotnet-linq-optimization

LINQ performance patterns for .NET applications. Covers the critical distinction between IQueryable<T> server-side evaluation and IEnumerable<T> client-side materialization, compiled queries for EF Core hot paths, deferred execution pitfalls, LINQ-to-Objects allocation patterns and when to drop to manual loops, and Span-based alternatives for zero-allocation processing.

Scope

• IQueryable vs IEnumerable materialization pitfalls
• Compiled queries for EF Core hot paths
• Deferred execution and multiple enumeration detection
• LINQ-to-Objects allocation patterns and manual loop alternatives

Out of scope

• EF Core DbContext lifecycle and migrations -- see [skill:dotnet-efcore-patterns]
• Strategic data architecture (N+1 governance, read/write split) -- see [skill:dotnet-efcore-architecture]
• Span and Memory fundamentals -- see [skill:dotnet-performance-patterns]
• Microbenchmarking setup -- see [skill:dotnet-benchmarkdotnet]

Cross-references: [skill:dotnet-efcore-patterns] for compiled queries in EF Core context and DbContext usage, [skill:dotnet-performance-patterns] for Span/Memory foundations and ArrayPool patterns, [skill:dotnet-benchmarkdotnet] for measuring LINQ optimization impact.

---

IQueryable vs IEnumerable Materialization

The most impactful LINQ performance decision is where evaluation happens: on the database server (IQueryable<T>) or in application memory (IEnumerable<T>).

The Problem

// DANGEROUS: Materializes entire table into memory, then filters in C#
IEnumerable<Order> orders = dbContext.Orders;
var recent = orders.Where(o => o.CreatedAt > cutoff).ToList();
// SQL: SELECT * FROM Orders  (no WHERE clause!)

// CORRECT: Filter executes on the database server
IQueryable<Order> orders = dbContext.Orders;
var recent = orders.Where(o => o.CreatedAt > cutoff).ToList();
// SQL: SELECT ... FROM Orders WHERE CreatedAt > @cutoff

```text

### When Materialization Happens

| Operation                                           | Effect                                    |
| --------------------------------------------------- | ----------------------------------------- |
| `ToList()`, `ToArray()`, `ToDictionary()`           | Executes query, loads results into memory |
| `foreach` / ` `                         | Executes query, streams results           |
| `AsEnumerable()`                                    | Switches  server to client evaluation |
| `Count()`, `Any()`, `First()`, `Single()`           | Executes query, returns scalar            |
| `Where()`, `Select()`, `OrderBy()`  `IQueryable`  |  = dbContext.Orders
    .AsEnumerable()           
    .Where(o => o.Total > )  
    .ToList();


 results = dbContext.Orders
    .Where(o => IsHighValue(o))  
    .ToList();


 results = dbContext.Orders
    .Where(o => o.Total > )   
    .AsEnumerable()
    .Where(o => IsHighValue(o))  
    .ToList();


 names = dbContext.Orders.ToList().Select(o => o.CustomerName);



 names = dbContext.Orders.Select(o => o.CustomerName).ToList();


```text



- Any `AsEnumerable()`  cast to `IEnumerable<T>` before `Where`/`Select`  a potential server-bypass
- EF Core logs `Microsoft.EntityFrameworkCore.Query` at Warning level  it falls back to client evaluation
- Enable `ConfigureWarnings(w => w.Throw(RelationalEventId.MultipleCollectionIncludeWarning))` during development

---



Compiled queries eliminate the per-call expression tree compilation overhead. For queries executed thousands of times
per second,  can reduce overhead significantly.



```
{
    
       Func<AppDbContext, Guid, Task<Order?>>
        s_findById = EF.CompileAsyncQuery(
            (AppDbContext ctx, Guid id) =>
                ctx.Orders.FirstOrDefault(o => o.Id == id));

       Func<AppDbContext, DateTime, IAsyncEnumerable<Order>>
        s_findRecent = EF.CompileAsyncQuery(
            (AppDbContext ctx, DateTime cutoff) =>
                ctx.Orders
                    .Where(o => o.CreatedAt > cutoff)
                    .OrderByDescending(o => o.CreatedAt));

     Task<Order?> FindByIdAsync(Guid id) =>
        s_findById(db, id);

     =>
        s_findRecent(db, cutoff);
}

```text



| Scenario                                      | Use compiled query?                         |
| --------------------------------------------- | ------------------------------------------- |
| High- = dbContext.Orders.Where(o => o.Status == Status.Active);

 count = query.Count();         
 items = query.ToList();        


 items = dbContext.Orders
    .Where(o => o.Status == Status.Active)
    .ToList();

 count = items.Count;           

```text



```csharp


 queries =  List<IQueryable<Order>>();
 ( i = ; i < statuses.Length; i++)
{
    queries.Add(dbContext.Orders.Where(o => o.Status == statuses[i]));
    
}


 ( i = ; i < statuses.Length; i++)
{
     localStatus = statuses[i];
    queries.Add(dbContext.Orders.Where(o => o.Status == localStatus));
}

```text

Note: C
 shared across iterations, making  a common pitfall  building deferred LINQ queries  a loop.



```csharp




{
     dbContext.Orders.Where(o => o.Status == Status.Active);
    
}


  Task<List<Order>> GetActiveOrdersAsync(CancellationToken ct)
{
      dbContext.Orders
        .Where(o => o.Status == Status.Active)
        .ToListAsync(ct);
}

```text

---



LINQ operators  -memory collections allocate iterators, delegates,  intermediate collections. For hot paths
processing thousands of items per second, these allocations can cause GC pressure.



| Operation                        | Allocations                        |
| -------------------------------- | ---------------------------------- |
| `Where()`, `Select()`            | Iterator  +          |
| `ToList()`, `ToArray()`          | New collection + possible resizing |
| `OrderBy()`                      | Full copy  sorting              |
| `GroupBy()`                      | Dictionary + grouping objects      |
| `SelectMany()`                   | Iterator + inner iterators         |
| Lambda capture of local variable | Closure  per captured scope  |



LINQ allocations are negligible  most code. Optimize only :

-  = items
    .Where(x => x.IsActive)
    .Select(x => x.Name)
    .ToList();


 result =  List<>(items.Count);
 ( item  items)
{
     (item.IsActive)
    {
        result.Add(item.Name);
    }
}

```text

```csharp


 hasActive = items.Any(x => x.IsActive);


 hasActive = ;
 ( item  items)
{
     (item.IsActive)
    {
        hasActive = ;
        ;
    }
}

```text



Before dropping to manual loops, consider these intermediate steps:

```csharp


 first = Array.Find(items, x => x.IsActive);
 exists = Array.Exists(items, x => x.IsActive);


 result =  List<>(items.Length);
result.AddRange(items.Where(x => x.IsActive).Select(x => x.Name));


 result = items.Where( x => x.IsActive).ToList();




```text

---



For the highest-performance scenarios, `Span<T>`  `ReadOnlySpan<T>` enable stack-based, zero-allocation processing.
These APIs are  LINQ-compatible but cover common patterns.



```csharp


ReadOnlySpan<> values =  [] { , , , ,  };
 found = values.Contains();


 index = values.IndexOf();

```text



```csharp


ReadOnlySpan<> csv = ;
 ( segment  csv.Split())
{
    ReadOnlySpan<>  = csv[segment];
    
}


ReadOnlySpan<> input = ;
 match = input.Trim().SequenceEqual();

```text



| Scenario                              | Approach                                   |
| ------------------------------------- | ------------------------------------------ |
| Parsing CSV/log lines  a tight loop | `ReadOnlySpan<>` + `Split`             |
| Searching sorted arrays               | `Span<T>.BinarySearch`                     |
| Processing  buffers  I/O      | `ReadOnlySpan<>` slicing               |
| General business logic  collections | LINQ (readability over micro-optimization) |

See [skill:dotnet-performance-patterns]  comprehensive Span<T>/Memory<T> patterns  ArrayPool<T> usage.

---





Always  only the columns you need:

```csharp


 orders =  dbContext.Orders
    .Include(o => o.Lines)
    .Include(o => o.Customer)
    .ToListAsync(ct);

 summaries = orders.Select(o => 
{
    o.Id,
    o.Customer.Name,
    Total = o.Lines.Sum(l => l.Price * l.Quantity)
});


 summaries =  dbContext.Orders
    .Select(o => 
    {
        o.Id,
        CustomerName = o.Customer.Name,
        Total = o.Lines.Sum(l => l.Price * l.Quantity)
    })
    .ToListAsync(ct);

```text



```csharp


 page =  dbContext.Orders
    .OrderBy(o => o.Id)
    .Skip(pageSize * pageNumber)
    .Take(pageSize)
    .ToListAsync(ct);


 page =  dbContext.Orders
    .Where(o => o.Id > lastSeenId)
    .OrderBy(o => o.Id)
    .Take(pageSize)
    .ToListAsync(ct);

```text



```csharp


 ( order  orders)
{
    order.Status = OrderStatus.Archived;
}
 dbContext.SaveChangesAsync(ct);



 dbContext.Orders
    .Where(o => o.CreatedAt < cutoff)
    .ExecuteUpdateAsync(
        s => s.SetProperty(o => o.Status, OrderStatus.Archived),
        ct);

```text

---



 **Do  cast IQueryable<T> to IEnumerable<T> before filtering** --  silently switches  server-side SQL
   evaluation to client-side -memory evaluation, potentially loading entire tables. Check  `AsEnumerable()`,
    casts,  method signatures that accept `IEnumerable<T>`.
 **Do   IQueryable<T>  repository methods** -- callers can compose additional operators, but the
   DbContext may be disposed before enumeration.