IQueryable translation and narrow projection

An expression tree is not a materialized collection

IQueryable describes work for its provider, while an Enumerable pipeline over an array describes work over already supplied values. Similar method names do not make the execution location interchangeable. The external query combines a predicate, ordering and a scalar projection before ToListAsync executes it. This lets SQLite perform the selected relational work and returns only the columns the result needs. The portable array lab captures the intended predicate and output ordering but supplies no translation evidence. Read both programs with their actual source type and materialization boundary in mind before extracting shared helpers.

Inspect SQL and then materialize the expected rows

The seeded items have prices of 150, 50 and 200 integer cents. The query keeps values at least 100, orders by Name and selects Name plus PriceCents. ToQueryString is checked for a WHERE clause, then ToListAsync actually runs the query and verifies A,C. An empty ChangeTracker supports the claim that this scalar-only projection did not track entity objects. Inspecting generated SQL alone would not prove the returned values, and observing A,C alone would not prove the predicate was translated rather than evaluated after an eager load. The fixture deliberately checks both facts on its pinned provider.

using System;
using System.Linq;
using System.Collections.Generic;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;

await using var connection = new SqliteConnection("Data Source=:memory:;Foreign Keys=True");
await connection.OpenAsync();
var options = new DbContextOptionsBuilder<LabDb>().UseSqlite(connection).Options;
await using var db = new LabDb(options); await db.Database.EnsureCreatedAsync();
db.Items.AddRange(new Item { Name = "A", PriceCents = 150 }, new Item { Name = "B", PriceCents = 50 }, new Item { Name = "C", PriceCents = 200 }); await db.SaveChangesAsync(); db.ChangeTracker.Clear();
var query = db.Items.Where(x => x.PriceCents >= 100).OrderBy(x => x.Name).Select(x => new { x.Name, x.PriceCents });
Check(query.ToQueryString().Contains("WHERE", StringComparison.OrdinalIgnoreCase), "provider WHERE");
var rows = await query.ToListAsync(); Check(string.Join(",", rows.Select(x => x.Name)) == "A,C", "projected rows"); Check(!db.ChangeTracker.Entries().Any(), "scalar projection not tracked");
Console.WriteLine("rows:" + string.Join(",", rows.Select(x => x.Name))); Console.WriteLine("tracked:" + db.ChangeTracker.Entries().Count());

static void Check(bool condition, string name) { if (!condition) throw new InvalidOperationException("CHECK: " + name); }

Trace the same predicate on supplied data without overstating it

The .NET9 sample starts from three Row records already in memory. Its Enumerable Where removes B because fifty cents is below the threshold; ordinal ordering retains A before C, and Select returns their names. That deterministic A,C output is a useful specification of the intended selection rule. It does not prove any SQL command, database column projection, index choice or network transfer volume. If an application loads everything before applying that rule, it may still produce the same names while doing materially different work. Keep the pure specification and provider execution checks complementary.

A custom C# helper can change what is translatable

EF attempts to translate supported expressions and permits some client evaluation in the top-level projection. An unsupported expression elsewhere, such as a predicate depending on an arbitrary helper, can fail rather than silently filtering the entire table on the client. Moving to AsEnumerable deliberately changes the remaining pipeline to client-side execution and should be a reviewed boundary, not a mysterious fix. Provider support also differs. This example uses simple supported comparisons and scalar members; it does not advertise every .NET operation as SQL-compatible. ToQueryString is a diagnostic view, not a measured execution plan or a universal guarantee of index usage.

Practice: challenge translation at the correct stage

Add a custom formatting helper only in the final projection and inspect which values must be fetched before it can run. Then place a deliberately untranslatable helper in the predicate in a separate controlled query and observe the provider’s rejection without catching it as a successful empty result. Restore the baseline and compare the generated SQL and materialized values after adding Take. Specify where ordering must occur for that limit to be meaningful. These experiments require the real EF provider; an Enumerable-only test would execute the helper normally and conceal the translation boundary.

• Hint: ToListAsync is an execution boundary; building the query variable is not materialization.
• Hint: inspect translated SQL and returned values as separate evidence.

Practice: preserve selection rules without creating a SQL emulator

Test the exact hundred-cent threshold, an empty input and equal names with different identifiers in the portable lab. Decide whether Name ordering alone is sufficient or whether a second key is required for a deterministic public report. Keep prices numeric in the supplied domain units until the display boundary. Do not extend the lab by generating pretend SQL strings and call that EF validation. Its role is to express independent expected results. The corresponding provider check should then execute those cases on real storage with the actual query shape and explicitly ordered results.

• Hint: include the boundary value in the expected input table before running it.
• Hint: a tie-breaker belongs to the result contract when ordering must be unique.

Review the source type, columns and execution point

A query review should identify whether the source is provider-backed, which operations remain translatable, which columns are projected and when materialization happens. The external assertion set establishes those selected facts for EF Core10 with SQLite provider package10.0.14, while the separate practice remains a .NET9 BCL rule check. It does not benchmark production data or verify another provider’s query plan. Preserve that limit when making performance decisions: reduce unnecessary data where useful, inspect realistic execution plans and measure a representative workload rather than treating a short LINQ expression or a single successful query as a performance certificate.