Read Generated SQL with ToQueryString and Verify Translation Before Performance Tuning

Learning outcomes

A ServiceHub query returns the correct queue but slows as the lab dataset grows. The developer pastes ToQueryString() into a ticket and labels it “the execution plan.” That skips the most important diagnostic step: generated SQL tells you what EF intends to send; command logs show what executed; the database plan shows how the engine chose to execute it. This lesson builds that evidence chain.

1. Build one diagnostic query

// Existing course model — deliberately not redesigned for Chapter 08.public sealed partial class WorkOrder{    public int Id { get; private set; }    public WorkOrderPublicId PublicId { get; private set; }    public string WorkOrderNumber { get; private set; } = string.Empty;    public string CustomerName { get; private set; } = string.Empty;    public string Summary => _summary;    public WorkOrderPriority Priority { get; private set; }    public DateTimeOffset OpenedUtc { get; private set; }    // Chapter 04 also maps shadow DateTime "CreatedUtc" -> created_utc    // so the SQLite lab can compare/order a UTC timestamp server-side.    public int? AssignedTechnicianId { get; private set; }    public Technician? AssignedTechnician { get; private set; }    public ServiceAddress ServiceAddress { get; private set; } = null!;    public Guid Revision { get; private set; }}public enum WorkOrderPriority{    Low = 1,    Normal = 2,    High = 3}

var minPriority = WorkOrderPriority.Normal;var cutoff = DateTime.UtcNow.AddDays(-30);var queue = db.WorkOrders    .TagWith("ServiceHub.WorkOrders.QueuePage")    .AsNoTracking()    .Where(w => w.Priority >= minPriority &&                EF.Property<DateTime>(w, "CreatedUtc") >= cutoff)    .OrderBy(w => EF.Property<DateTime>(w, "CreatedUtc"))    .ThenBy(w => w.Id)    .Select(w => new    {        w.Id,        w.WorkOrderNumber,        w.CustomerName,        w.Priority,        CreatedUtc = EF.Property<DateTime>(w, "CreatedUtc")    })    .Take(50);Console.WriteLine(queue.ToQueryString());

TagWith places a comment in generated SQL so code and captured commands can be correlated. Tags are diagnostic literals, not user-input parameters; do not put secrets or untrusted large text into tags.

2. ToQueryString answers translation questions

.param set @__minPriority_0 2.param set @__cutoff_1 '2026-07-28T00:00:00.0000000+00:00'.param set @__p_2 50-- ServiceHub.WorkOrders.QueuePageSELECT w.work_order_id, w.work_order_number, w.customer_name,       w.priority, w.created_utcFROM work_orders AS wWHERE w.priority >= @__minPriority_0  AND w.created_utc >= @__cutoff_1ORDER BY w.created_utc, w.work_order_idLIMIT @__p_2;

This can answer “did my filter/project/order translate?” It cannot tell you runtime parameter sniffing, locks, cache state, actual rows, I/O, network latency, or optimizer choices. Microsoft’s API documentation explicitly states the string is intended for debugging and may not be suitable for direct execution.

3. Command logs show execution-time evidence

info: Microsoft.EntityFrameworkCore.Database.Command[20101]      Executed DbCommand (4ms) [Parameters=[@__minPriority_0='2',      @__cutoff_1='2026-07-28T00:00:00+00:00', @__p_2='50'],      CommandType='Text', CommandTimeout='30']      -- ServiceHub.WorkOrders.QueuePage      SELECT ...

The duration includes provider command execution timing as observed by EF, not application end-to-end latency. With sensitive-data logging disabled, logs may redact parameter values. Keep production logging safe; temporarily enabling sensitive-data logging can expose customer data and should be tightly controlled.

4. Database plan evidence is a different artifact

EXPLAIN QUERY PLANSELECT work_order_id, work_order_number, customer_name, priority, created_utcFROM work_ordersWHERE priority >= 2  AND created_utc >= '2026-07-28 00:00:00'ORDER BY created_utc, work_order_idLIMIT 50;

SCAN work_ordersUSE TEMP B-TREE FOR ORDER BY

SQLite’s plan text is intentionally high-level and can change across SQLite versions. Treat it as evidence about search/scan/sort shape, not a stable string for exact assertion tests. On SQL Server/PostgreSQL/MySQL/Oracle, use that engine’s actual execution-plan tooling instead.

5. Deliberately wrong conclusion: “the SQL is short, so it is fast”

The query can be semantically correct yet require a full scan plus sort as data grows. SQL length does not measure work. First verify data distribution and indexes; then change one thing at a time.

modelBuilder.Entity<WorkOrder>()    .HasIndex(nameof(WorkOrder.Priority), "CreatedUtc", nameof(WorkOrder.Id))    .HasDatabaseName("ix_work_orders_priority_created_id");

This is a candidate, not a universal recommendation. The inequality on Priority, ordering columns, selectivity, and provider optimizer determine whether/how it helps. Generate/review the migration, apply it only to the disposable lab, rerun the same plan/query, and compare measured evidence.

6. Verify the migration rather than assuming the index exists

dotnet ef migrations add AddQueueEvidenceIndex   --project src/ServiceHub.EfLab   --startup-project src/ServiceHub.EfLab   --output-dir Data/Migrationsdotnet ef database update   --project src/ServiceHub.EfLab   --startup-project src/ServiceHub.EfLab

SELECT name, sqlFROM sqlite_masterWHERE type = 'index'  AND tbl_name = 'work_orders'ORDER BY name;

EF metadata, migration source, and the live database are three different artifacts. A production investigation should know which migration actually ran and whether the expected index exists on the target database.

7. Query tags improve correlation, not performance

var query = db.WorkOrders    .TagWithCallSite()    .Where(w => w.Priority == WorkOrderPriority.High)    .Take(25);

EF Core can include source file/line information with TagWithCallSite, which can help match a production SQL capture back to source. Tags are cumulative. They are not parameterizable and should not contain request secrets, personal data, or arbitrary user text.

8. A useful evidence record separates four questions

9. Do not benchmark the wrong thing

If you time only the first query, you mix model initialization, JIT, query compilation, file/database cache warmup, and database execution. If you leave verbose console logging enabled, you measure logging too. Record Release/Debug, .NET/EF/provider/database versions, machine/container, dataset size/distribution, tracking mode, projection, warmup, connection pooling, concurrency, indexes/statistics, and whether logging was enabled. Report measured samples rather than copying numbers from another machine.

10. Hands-on capstone: create the Chapter 08 query evidence packet

1. Seed a disposable dataset large enough to make plan differences visible; record row count and priority/date distribution.
2. Run the tagged queue query and save LINQ source plus ToQueryString().
3. Execute it with command logging and save the command metadata/duration.
4. Run equivalent EXPLAIN QUERY PLAN against SQLite and save raw output.
5. Inspect live indexes from sqlite_master.
6. Add the candidate composite index through a reviewed migration; rerun the same evidence packet.
7. If the plan does not improve, keep the result—do not force the narrative. Explain selectivity/order/provider reasons and try a different query/index only with a hypothesis.
8. Repeat one important query on the production-like provider before making a production tuning decision.

11. Chapter 08 production judgment and bridge

Query performance work should move from source intent → EF translation → actual command → database plan/metrics. Keep ToQueryString in its proper diagnostic role. Use tags/logs safely, avoid sensitive data, and make index decisions from workload evidence. Chapter 09 builds on this query-pipeline mental model with advanced joins, grouping, subqueries, set operations, and raw-SQL composition—where cardinality and composability become even more important.

Authoritative references

• ToQueryString API - EF Core 10 — debugging representation and limitations
• Query Tags - EF Core — TagWith/TagWithCallSite behavior and limitations
• Simple Logging - EF Core — command/logging categories and sensitive-data considerations
• Efficient Querying - EF Core — indexes, projections, result limiting, plan-first tuning
• SQLite EXPLAIN QUERY PLAN — free/local plan evidence for the mandatory lab
• Indexes - EF Core — index metadata/migrations and provider considerations