The Query EF Core Writes When You Ask for a Count

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

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.

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

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:

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:

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:

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