# The Query EF Core Writes When You Ask for a Count

> On EF Core 10.0.12, AsEnumerable() before Where().Count() sent a full-table SELECT instead of COUNT(*) and allocated 1056.36 KB against 49.53 KB at 1,000 rows.

- Published: 2026-10-18
- Tags: runtime, memory, diagnostics
- Source: https://csharpmind.com/blog/the-query-ef-core-writes-when-you-ask-for-a-count/
- Language: en-GB
- Author: Callum Reeve

---

## TL;DR

- On EF Core 10.0.12 with SQLite, Where(o => o.Amount >= 500).Count() sent one SELECT COUNT(*) with a WHERE clause and allocated 49.53 KB at 1,000 rows, and the same count with AsEnumerable() inserted before Where allocated 1056.36 KB.
- The AsEnumerable shape sent a SELECT of all six columns with no WHERE, read every row of the table, and tracked every entity, and a translatable predicate after AsEnumerable changed none of that.
- An untranslatable predicate on the IQueryable threw InvalidOperationException before any command was sent, and the message names AsEnumerable as one way out.
- Allocation for the AsEnumerable shapes grew from 1056.36 KB to 9988.68 KB when the table grew from 1,000 to 10,000 rows, while the COUNT(*) shapes stayed within 0.01 KB of their 1,000-row figures.
- The difference is visible in the command log and not in ToQueryString(). Only one engine, one EF Core version and two table sizes were measured.

---

Asking EF Core 10.0.12 for the number of large orders sent one `SELECT COUNT(*)` in two spellings and a `SELECT` of all six columns of every row in a third. All three returned 500 from a table of 1,000 rows. The third spelling put `AsEnumerable()` before `Where()` and `Count()`, read 1,000 rows, tracked 1,000 entities and allocated 1056.36 KB, against 49.53 KB for the same count written as `Where(...).Count()`.

Nothing in the C# marks the difference. Allocation is the measure here, as it was when [a six-component middleware pipeline was measured one component at a time](/blog/the-middleware-pipeline-measured-one-component-at-a-time/), because the integer returned is identical across the shapes and only what was sent and read differs.

## Shapes that return the same integer, and the SQL each sends

The table holds `Order` rows with `Id`, `CustomerId`, `Amount`, `Status`, `CreatedUtc` and `Note`. `Amount` is seeded as `i % 1000`, so half of the rows satisfy `Amount >= 500`. Each operation opens its own `ShopContext` against an in-memory SQLite database. `Rules.Threshold` is the constant 500, and `IsLarge` is an ordinary method EF Core has no translation for:

```csharp
public static bool IsLarge(Order order) => order.Amount >= Threshold;

ctx.Orders.Count(o => o.Amount >= Rules.Threshold);                       // A2
ctx.Orders.Where(o => o.Amount >= Rules.Threshold).Count();               // B
ctx.Orders.AsEnumerable().Where(o => Rules.IsLarge(o)).Count();           // C
```

The command log of shape B, and the lines of shape C that matter, read as follows, written through `LogTo` as shown later.

```text
info: 09/30/2026 19:24:11.107 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT COUNT(*)
      FROM "Orders" AS "o"
      WHERE "o"."Amount" >= 500
```

```text
dbug: 09/30/2026 19:24:11.108 CoreEventId.QueryCompilationStarting[10111] (Microsoft.EntityFrameworkCore.Query) 
      Compiling query expression: 
      'DbSet<Order>()'
info: 09/30/2026 19:24:11.120 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "o"."Id", "o"."Amount", "o"."CreatedUtc", "o"."CustomerId", "o"."Note", "o"."Status"
      FROM "Orders" AS "o"
```

Shapes A2 and B sent byte-identical SQL. The query EF Core compiled for shape C was `DbSet<Order>()` alone, and the filter and the count appear in neither line. The data reader and the entity counts from an inspection pass over the same shapes give the same picture:

| Shape | Result | `Read()` calls | Entities materialised | Entities tracked |
|---|---:|---:|---:|---:|
| `Count()` | 1000 | 2 | 0 | 0 |
| `Count(pred)` (A2) | 500 | 2 | 0 | 0 |
| `Where(pred).Count()` (B) | 500 | 2 | 0 | 0 |
| `AsEnumerable()` first (C) | 500 | 1001 | 1000 | 1000 |

At 10,000 rows shape C read 10,001 times; the others still read twice.

## A full-table read that does not depend on the predicate

The obvious suspect is the predicate, since `IsLarge` cannot be translated. Another shape removed it: `AsEnumerable()` followed by the ordinary lambda `o => o.Amount >= 500`. It sent the same full-table `SELECT` with no `WHERE`, read the same 1,001 times, and allocated 1056.32 KB, within 0.04 KB of shape C. The cause is `AsEnumerable()`. It returns an `IEnumerable<Order>`, so `Where` and `Count` bind to the in-memory LINQ operators, and the provider is handed `DbSet<Order>()` and nothing more.

BenchmarkDotNet's `[MemoryDiagnoser]` printed these `Allocated` figures per operation:

| Shape | 1,000 rows | 10,000 rows |
|---|---:|---:|
| `Count()` | 47.02 KB | 47.03 KB |
| `Count(pred)` | 48.38 KB | 48.38 KB |
| `Where(pred).Count()` | 49.53 KB | 49.53 KB |
| `AsEnumerable()`, `IsLarge` | 1056.36 KB | 9988.68 KB |
| `AsEnumerable()`, translatable lambda | 1056.32 KB | 9988.68 KB |
| `AsNoTracking().AsEnumerable()`, `IsLarge` | 482.26 KB | 4399.43 KB |

The `COUNT(*)` shapes do not move with the table. The `AsEnumerable()` shapes grow with it: 1056.36 KB became 9988.68 KB, which is 22.46 and then 212.41 times the allocation of `Count()`.

`AsNoTracking()` cut the figure to less than half, since no entity was tracked. It is not a fix. The SQL was the same full-table `SELECT`, the reader was still read 1,001 times, and the allocation still grew with the table.

## The exception that names AsEnumerable as the way out

Leave `AsEnumerable()` out and an untranslatable predicate fails loudly. Both `Where(o => Rules.IsLarge(o)).Count()` and `Count(o => Rules.IsLarge(o))` threw `InvalidOperationException`, and no command reached the database. The message tells the reader to "switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'". Following it turns the failing count into shape C.

Microsoft's page on client evaluation explains the stance: "Because the filter can't be applied in the database, all the data needs to be pulled into memory to apply the filter on the client." EF Core blocks that, and it lists explicit client evaluation as a choice for when "the amount of data is small so that evaluating on the client doesn't incur a huge performance penalty". The exception is the warning, and the suggested call is the means of ignoring it.

Not every untranslatable call is an error or a cost. `Select(o => Rules.IsLarge(o)).Count()` threw nothing, returned 1000, and sent the SQL of the plain `Count()`, so the method was evaluated nowhere. The documented exception to the rule is the last projection: `Where(pred).Select(o => new { o.Id, Large = Rules.IsLarge(o) }).ToList()` kept the `WHERE` in SQL, selected six columns, and materialised and tracked the 500 matching rows. Of the shapes measured, only two evaluated on the client without an error, an explicit `AsEnumerable()` and a final projection. The same page says EF Core did this anywhere in a query before version 3.0; no such version was run.

## What the command log shows and ToQueryString() does not

`Count()` returns an `int`, so `ToQueryString()` exists only on the query before it. On `ctx.Orders.Where(o => o.Amount >= 500)` it printed an entity `SELECT` with the `WHERE`, and the `COUNT(*)` that `Count()` sends appeared nowhere in it. After `AsEnumerable()` it cannot be called at all. `CountAsync` sent the same `COUNT(*)` with the `WHERE` and materialised no entity.

The command log does. It was switched on with one line:

```csharp
.LogTo(logSink.WriteLine, [RelationalEventId.CommandExecuted, CoreEventId.QueryCompilationStarting])
```

Two signs separate the shapes. A method that reads as a count should produce an `Executed DbCommand` whose SQL contains `COUNT(`. And a `Compiling query expression` that ends at `DbSet<Order>()` means everything written after it ran in memory.

Four limits apply. SQLite in memory was the only engine, so the network transfer of every row to a remote server is in no figure. Two table sizes were run, with half the rows matching. Only EF Core 10.0.12 was run. And timings are not used to compare shapes.

Run a `Count()` that follows an `AsEnumerable()` with the command log on before trusting it.

---

**Measured on** .NET 10.0.12, SDK 10.0.401, Linux arm64 (Ubuntu 24.04.5) in a Docker container on Docker Desktop for Apple silicon, started with `--network none --cap-drop ALL --security-opt no-new-privileges --user 501:20 --pids-limit 512`, with no CPU or memory limit and 12 CPUs visible. EF Core 10.0.12 with Microsoft.Data.Sqlite 10.0.12 against an in-memory database held open by one connection; BenchmarkDotNet 0.15.8 with its default job and `[MemoryDiagnoser]`, at 1,000 and 10,000 rows. The SQL, `Read()` counts and entity counts come from an inspection pass over the same shapes, pasted as printed, except that the `dbug` line of shape B is left out of the first log excerpt. A repeat run put every `Allocated` figure within 0.04 KB of the first; the widest gap was shape C2 at 1,000 rows, 1056.32 KB then 1056.36 KB. Mean times are not reported: one shape at 10,000 rows printed 260.10 μs in the first run and 193.13 μs in the repeat. The Microsoft Learn page "Client vs. Server Evaluation" was read on 30 September 2026. Not examined: a networked database, other providers, other EF Core versions, other match ratios and other table sizes.

---

## Frequently asked

### Does Where(...).Count() in EF Core run the count in the database?

In the run measured here, yes. On EF Core 10.0.12 with SQLite, Where(o => o.Amount >= 500).Count() sent a single SELECT COUNT(*) with a WHERE clause, and no entity was materialised.

### Why did a translatable predicate after AsEnumerable() still read the whole table?

AsEnumerable() ends the EF Core query. The command that was sent selected every column of every row with no WHERE clause, and the filter and the count ran in memory, so the predicate made no difference to the SQL.

### Does AsNoTracking() fix an AsEnumerable() count?

It reduced allocation to 482.26 KB from 1056.36 KB at 1,000 rows, because no entity was tracked. The SQL was the same full-table SELECT and every row was still read, so the table read remains.

### Why does ToQueryString() not show the SQL of a Count()?

Count() returns an int, so ToQueryString() can only be called on the query before it. On that query it printed an entity SELECT with the WHERE clause, not the COUNT(*) that Count() sends.