C# / .NET Order Queries

Choose a query strategy for the reader's task: export the complete matching identity set in sequence, jump between pages of a retained ID set, or calculate counts and money for the whole filtered set. Work through the C# / .NET first Order tutorial for setup and typed Q/E reads; use the mutation recipes when changing an Order.

The selected condition-key/pagination workspace remains WORKSPACE PASS — RELEASE PENDING. Preserve its NuGet feed, source and installed-assembly identity and all earlier generator qualifications. The condition-key repair also affects continuous cursor identity.

For the selection tradeoffs, read keyset vs offset pagination, exact counts and random access before choosing an optimizer.

Prepare the right data and context

All three recipes use the evaluated Order v1.0 model, the complete native application and the retained built workspace/toolchain recorded in the 17-case core receipt. They are not a clean-machine bootstrap. The file includes startup, imports, store instrumentation, row conversion, numeric helpers and reporting. These are application helpers; keep them when adapting the excerpts, which are complete functions or fragments within that application.

Recipe	Fixture and fresh database	What matches
Consecutive export	Nine Orders / 27 items	Eight `BUYER-EXPORT` Orders; one buyer control. All are USD/DRAFT. The export request filters buyer, not currency.
Retained IDs and summaries	Ten Orders / 30 items	Eight export-buyer USD Orders, one buyer control and one export-buyer EUR control; three statuses are represented.

The introductory three-order edit fixture has different business keys and totals. Do not seed a new fixture into an already seeded database or expect nine-fixture counts from the ten-fixture export query. Keep the absolute fixture/database path and model revision visible while diagnosing a count mismatch.

Reader task	Native strategy	Lifetime
Export consecutive pages	ID ordering plus continuous-page optimization.	Reuse one trusted context and sequence namespace inside the export.
Jump backward/forward	Total/ID ordering plus retained-ID optimization.	Reuse the context and installed store through that jump sequence.
Summary cards / status buckets	Provider COUNT/SUM/GROUP BY plus Facets.	Apply the identical buyer, currency and minimum-total predicate to every request.

A namespace is not tenant authorization. These v1.0 buyer filters are business predicates; protected access needs the separate trusted isolation model/policy. The native examples seed via generated audited writes, then their query handlers leave Orders/items, versions, relations and timestamps unchanged.

1. Export consecutive pages

Use a single-ID sort for the seekable path, page size 3, namespace order-export- plus the mode, and the example's explicit 300-second TTL. The optimization selects a provider plan; it does not serialize CSV or create a file. This console returns bounded JSON page reports. In a larger application, choose the output sink and page budget separately from the query optimization.

static async Task<object> ExportPages(UserContext context) {
    string mode=Environment.GetEnvironmentVariable("ORDER_EXPORT_MODE")!;bool complete=mode is "asc" or "desc";
    Require(new[]{"asc","desc","miss","composite","predicate"}.Contains(mode),"Unknown export mode");
    var pages=new List<object>();int fallbackCount=0,seekCount=0;bool ended=false;Console.WriteLine("ORDER_PAGES_BEGIN");
    for(int index=0;index<(complete?10:2);index++) {
        int offset=(index+(mode=="miss"?1:0))*3;string buyer=mode=="predicate"&&index==1?"BUYER-CONTROL":"BUYER-EXPORT";
        var query=Q.CustomerOrders().WithBuyerReferenceIs(buyer);
        if(mode=="composite")query=query.OrderByTotalMinorAscending().OrderByIdAscending();else query=mode=="desc"?query.OrderByIdDescending():query.OrderByIdAscending();
        Console.WriteLine("ORDER_EXPORT_PAGE "+index);
        var page=await query.Offset(offset).Limit(3).OptimizeForContinuousPageFetchWith("order-export-"+mode,300).Count()
            .Comment("Export Order pages").Purpose("Export complete bounded Order identities through one trusted context").ExecuteForListAsync(context);
        var plan=context.ContinuousPagePlan;Require(plan is not null&&page.TotalCount is not null,"Missing native page observation");
        if(plan=="CURSOR_SEEK")seekCount++;else if(plan!.StartsWith("OFFSET_FALLBACK:"))fallbackCount++;
        pages.Add(new {offset,buyer,rows=PageRows(page,false),total=page.TotalCount,plan});if(complete&&page.Count==0){ended=true;break;}
    }
    Require(!complete||ended,"Export page budget exhausted");return new {mode,pages,fallbackCount,seekCount};
}

For complete asc/desc export, the same context is reused inside the bounded loop. It stops on an empty page; ten pages is this fixture's upper safety budget, and exhausting it without an empty tail fails rather than silently returning a truncated export. The other modes run two diagnostic pages deliberately. The report keeps all pages for the small fixture; it is not an unbounded-memory export design.

Read the plan and the exact total

Offset / ascending sequence	Row count	Exact matching total	Native plan
0	3	8	`OFFSET_FALLBACK:FIRST_PAGE`
3	3	8	`CURSOR_SEEK`
6	2	8	`CURSOR_SEEK`
9	0	8	`OFFSET_FALLBACK:CACHE_MISS`

The total remains 8, including the empty tail. The cursor boundary narrows the row fetch, not the matching-set count. The retained evidence contains an actual provider COUNT on every exported page; count() is not a free cached total promise. A tail cache miss is the recorded fallback behavior, not a failed export. Read this language's observation immediately after the request: context.ContinuousPagePlan. The SmartList exact-count carrier is page.TotalCount.

`ORDER_EXPORT_MODE`	How to interpret it
`asc` / `desc`	All eight matching identities once, in forward/reverse ID order; 3, 3, 2, 0 rows.
`miss`	Start a new sequence at offset 3: CACHE_MISS, then CURSOR_SEEK at 6.
`composite`	Total then ID ordering: retain the native offset-fallback explanation instead of claiming an ID seek.
`predicate`	Change to the control buyer at offset 3 in the same context: CACHE_MISS, zero rows, exact total 1.

The recorded C# / .NET composite-sort plans are: OFFSET_FALLBACK:UNSUPPORTED_QUERY_SHAPE on both composite-sort pages. Starting another process resets this in-memory sequence. The retained restart control starts at the first page again; the old namespace alone does not restore a process-local cursor. Fixed-data completeness is established here; the recipe does not establish a snapshot-consistent export during concurrent writes or test continuous-cursor expiry just because a TTL parameter is present.

Run the existing export

From the site root with the retained built workspace present:

export ORDER_WORKSPACE="$PWD/artifacts/order/2026-10-27/dotnet/dotnet-app-console"
export ORDER_FIXTURE="$PWD/static/examples/order/p2-pages/fixture.json"
export ORDER_INSIGHTS_FIXTURE="$PWD/static/examples/order/p2-insights/fixture.json"
export ORDER_DATABASE="$(mktemp -d)/export.sqlite"
cd "$ORDER_WORKSPACE"
run_order_phase() { dotnet bin/Debug/net8.0/order-workflow-service-console.dll "$1"; }
run_order_phase bootstrap
run_order_phase seed
export ORDER_EXPORT_MODE=asc
run_order_phase pages-export

Stop on a native command failure. Inspect the ORDER_CASE report for pages-export: page rows, totals and native plans all matter. Change the export mode and invoke that phase again to inspect another sequence; each process gets a new context and starts the selected sequence independently.

2. Retain IDs for random page jumps

This sequence filters BUYER-EXPORT and USD, sorts by total then ID and uses page size 3, explicit TTL 300 seconds and capacity 20 IDs in the normal branch. Capacity limits identities per matching set, not the visible page size or number of cache keys. A build retains the complete ordered matching identity set, not just the requested page. Nonempty hits fetch rows by those retained identities.

First install or pass the existing store:

string mode=Environment.GetEnvironmentVariable("ORDER_RETAINED_MODE")!;var store=new ObservedIds();context.WithIdSetStore(store);

The application ObservedIds delegates to the native in-memory store and installs it with context.WithIdSetStore(store). It awaits InvalidateAsync for the actual recorded key. The retained condition-key repair serializes the typed compound predicate; the recorded NuGet and installed assembly identities matter, not only a package version label.

Then the existing loop executes this request, with its current mode, offset and buyer already selected. The mode selects TTL/capacity 300/20 normally, 1/20 for expiry or 300/4 for overflow:

var page=await Q.CustomerOrders().WithBuyerReferenceIs(buyer).WithCurrencyCodeIs("USD").OrderByTotalMinorAscending().OrderByIdAscending()
    .Offset(offsets[index]).Limit(3).OptimizePaginationWithIdSet("order-retained-"+mode,mode=="expiry"?1:300,mode=="overflow"?4:20).Count()
    .Comment("Read retained Order IDs").Purpose("Verify random jumps and retained-set lifecycle").ExecuteForListAsync(context);

Observe the native plan/count through context.IdSetPlan and context.IdSetCount. That observation and the actual store events explain whether a set was built, hit or bypassed; the namespace string or repeated SQL text alone does not establish a cache hit.

Mode / offsets	Expected plan sequence	Interpretation
`jumps`: 6, 0, 3, 9, 0	BUILD, HIT, HIT, HIT, HIT	Total 8, including the empty offset-9 tail; the first request may start in the middle.
`empty`: 0, 3	BUILD, HIT	Retain an empty matching set, total 0. An empty hit may need no row SQL; an absent IN query is not itself a missing hit.
`overflow`: 3, 0	FALLBACK_LIMIT_EXCEEDED twice	Capacity 4 observes lower bound 5, retains no partial set, and gets exact total 8 from provider COUNT.
`isolation`: 0, 0, 3	BUILD, BUILD, HIT	Buyer changes create a separate one-ID set; returning to the original predicate reuses its set. This tests predicate identity, not tenant authorization.
`expiry`: 0, 3	BUILD, BUILD	The helper uses a one-second TTL and actually waits about 1.3 seconds before reusing the same query key.
`invalidate`: 0, 3, 6	BUILD, HIT, BUILD	Invalidate the actual observed key between the second and third requests.

The full native names are ID_SET_BUILD, ID_SET_HIT and ID_SET_FALLBACK_LIMIT_EXCEEDED. observedCount=5 in the overflow branch is a lower bound from the bounded identity scan, not the page's exact matching total or a complete retained set. Inspect the language-specific exact-count carrier above. Expiry/invalidation rebuild the same matching query; do not demonstrate a rebuild by silently changing the namespace instead.

Retained membership is not a transactional snapshot of every row value. These reads run over an unchanged fixture; the evidence does not define behavior for simultaneous inserts, deletes, reordering, distributed stores or cross-process retention. Do not advertise those guarantees based on the in-memory example.

Run the retained-ID sequence on its own data

Continue in the prepared application directory. Switch to a new database and the absolute insights-fixture path saved by the export setup:

export ORDER_DATABASE="$(mktemp -d)/insights.sqlite"
export ORDER_FIXTURE="$ORDER_INSIGHTS_FIXTURE"
run_order_phase bootstrap
run_order_phase seed
export ORDER_RETAINED_MODE=jumps
run_order_phase insights-retained

The report is insights-retained, with pages and events. Set another ORDER_RETAINED_MODE and invoke the same phase to inspect the corresponding read-only sequence. Each invocation owns a new context/store; the expiry and invalidation actions occur within that invocation, not across separate shell commands. Keep the store helper when adapting this source; copying only its Q request does not reproduce these lifecycle observations.

3. Summarize the same filtered Order set

Use the ten-order insights database. The application repeats buyer, currency and minimum total on each grouped/overall aggregate and on each Facet request that also supplies the visible parent page.

Grouped provider results:

var groups=await Q.CustomerOrders().WithBuyerReferenceIs(buyer).WithCurrencyCodeIs(currency).WithTotalMinorGreaterThanOrEqualTo(minimum)
    .GroupByOrderStatus().CountAs("count").SumTotalMinorAs("totalMinor").Limit(4)
    .Comment("Summarize filtered Order amounts by status").Purpose("Compute same-currency Order summaries in the provider").ExecuteForRowsAsync(context);

Separate overall aggregate:

var summary=await Q.CustomerOrders().WithBuyerReferenceIs(buyer).WithCurrencyCodeIs(currency).WithTotalMinorGreaterThanOrEqualTo(minimum)
    .CountAs("count").SumTotalMinorAs("totalMinor").Limit(1)
    .Comment("Summarize the complete filtered Order set").Purpose("Compare exact counts and money independent of page size").ExecuteForRowsAsync(context);

The provider performs the SUM and GROUP BY. The application only converts fields for the shared report; it does not sum the visible page to invent a grand total. Money in these fixtures remains integer minor units, with one selected currency.

ExecuteForRowsAsync(context) supplies analytic records in .Rows; their fields are TeaQL Value wrappers. The native status key is order_status, normalized to statusId by the report. The application helper N(Value) calls TryF64() for nonempty group values. The overall sum instead uses nullable TryF64() directly; Convert casts or the non-null helper would mishandle an empty sum.

The existing loop runs this Facet request with all-status buckets and then with matched-only buckets:

var page=await Q.CustomerOrders().WithBuyerReferenceIs(buyer).WithCurrencyCodeIs(currency).WithTotalMinorGreaterThanOrEqualTo(minimum)
    .FacetByOrderStatusAs("statuses",Q.OrderStatusesWithMinimalFields().SelectCode().CountCustomerOrdersAs("count").Limit(4),all)
    .SelectOrderItemListWith(Q.OrderItems().OrderBySkuCodeAscending().OrderByIdAscending().Limit(1)).OrderByIdAscending().Limit(3).Count()
    .Comment("Read Order status buckets and bounded item previews").Purpose("Keep rows facets and counts on the same filtered set").ExecuteForListAsync(context);

This request returns at most three Orders, with one item preview per parent. The overall count/sum and status buckets describe the whole filtered set. In the all-USD case the visible page has three Orders (stored parent totals sum to 13100), while the complete matching set has eight Orders totaling 35350 cents. Neither a page sum nor a sum of Notebook previews is the matching-set amount.

Buyer / currency / minimum	Grouped status totals (count, cents)	Overall aggregate
Export / USD / 0	DRAFT (3, 11850), CONFIRMED (2, 10400), CANCELLED (3, 13100)	Count 8, sum 35350.
Export / USD / 5000	CONFIRMED (2, 10400), CANCELLED (1, 5200); no DRAFT group row.	Count 3, sum 15600.
Control / USD / 0	CONFIRMED (1, 3950).	Count 1, sum 3950.
Export / EUR / 0	DRAFT (1, 3950).	Count 1, sum 3950 euro cents, not USD.
Missing buyer / USD / 0	Empty grouped result.	One overall row: count 0, sum null.

For minimum 5000, all-status Facets retain DRAFT:0, while matched-only Facets omit DRAFT. For a missing buyer, all three all-status buckets are zero, matched-only buckets and the parent page are empty. Preserve provider NULL for an empty SUM; replacing it with zero is an explicit presentation decision, not this recorded aggregate result. An empty grouped list and a one-row zero-count summary are different successful result shapes.

Read the summary report

The retained-ID recipe already prepared the insights database; these calls only read it:

export ORDER_SUMMARY_BUYER=BUYER-EXPORT
export ORDER_SUMMARY_CURRENCY=USD
export ORDER_SUMMARY_MINIMUM=0
run_order_phase insights-summary
export ORDER_SUMMARY_MINIMUM=5000
run_order_phase insights-summary

For the other table rows, change all three inputs deliberately: use BUYER-CONTROL, BUYER-MISSING, or currency EUR as appropriate, resetting the minimum to 0. The helper accepts console inputs; a service must validate its currency/amount/scope inputs before building the request. Do not merge USD and EUR amounts or expect a three-row UI limit to change the full-set summary.

Diagnose plans and result shapes

Symptom	Inspect first	Recovery / correct interpretation
Nine export-buyer rows rather than eight	Which fixture was seeded, and whether the export request filters currency.	Use the nine-order export fixture for this recipe; the ten-order fixture adds an export-buyer EUR control.
Every page looks like a fresh first page	Actual offset, same-context lifetime and the native observation immediately after that request.	Keep one sequence context; a new process starts a new in-memory sequence. Preserve the recorded workspace runtime when diagnosing observation differences.
Offset 9 falls back or returns no rows	Page rows versus exact total.	The export tail's CACHE_MISS and total 8 are expected. A retained-ID empty tail can still be a HIT.
Repeated BUILD after changing mode	Namespace, context/store recreation and query predicate.	Demonstrate reuse within the same sequence; separate processes/modes deliberately create fresh state.
`observedCount=5`, `total=8`	Capacity-4 overflow, native fallback and provider COUNT.	Keep the lower bound and exact total distinct; do not cache a partial set or return 5 as the total.
Hit returns another buyer's IDs	Typed predicate key and exact runtime/source/package identity.	Use the qualified scope and the recorded condition-key fix where needed; do not hide a key-collision defect by omitting isolation predicates.
Empty sum is missing or throws a cast error	The overall analytic carrier/alias and native nullable numeric conversion.	Preserve the one-row count 0/sum null result; use this language's analytic values, not typed Order accessors or required non-null casts.
Facets or sum disagree with the visible page	Filters and difference between full matching set, parent limit and child preview.	Repeat buyer/currency/minimum on every request; compare totals for the full set rather than summing a truncated preview.

Existing evidence and continuation

The pagination receipt retains the original bounded/export campaign. The core insights receipt reruns export plus retained-ID and summary cases with the exact source quoted here. Inspect runtimes.dotnet.verification.cases.continuous-pages, retained-id-pages and order-analytics: native reports/plans, store keys/events, provider SQL and unchanged full snapshots supply different parts of the explanation.

These are local SQLite, fixed-data reads with the recorded runtime and application adapters. Existing published failures, pending-release marks, generator repairs pending deployment, School and every earlier Order receipt remain scoped and unchanged. See the C# / .NET troubleshooting guide, query API guide and coverage matrix.

Continue with buyer and tenant isolation

Use C# / .NET Order Isolation to upgrade the model to v1.1 and build root/buyer scope from an application-owned trusted session.

Prepare the right data and context​

1. Export consecutive pages​

Read the plan and the exact total​

Run the existing export​

2. Retain IDs for random page jumps​

Run the retained-ID sequence on its own data​

3. Summarize the same filtered Order set​

Facets and bounded row previews​

Read the summary report​

Diagnose plans and result shapes​

Existing evidence and continuation​

Continue with buyer and tenant isolation​