Query Facade

Imagine building an admin order list. The screen shows only the order number, customer name, order amount, and order status, yet the code fetches the order object, looks up the customer object, iterates over line items to calculate the total, and only then assembles the response to send to the screen. Because the list query even passes through the rules needed to cancel an order, adding a single column to the screen can end up touching the entire order domain.

Query Facade collects queries like these into entry points such as ListOrdersAsync or GetDashboardAsync and returns results shaped for the screen or API. The caller does not need to know which table the customer name comes from, whether the amount was pre-calculated, or whether the implementation uses SQL or an ORM; the caller simply passes in query conditions and receives data in a agreed-upon shape.

Here, "query" means a question or lookup, and "facade" means a simple interface placed in front of complex internals. When read-only queries start spreading across individual screens, this design consolidates those queries' conditions and results into one place. Admin screens, dashboards, reports, search results, and list APIs are the most common use cases.

Personal note: this is the pattern that prevents the moment where you set out to build one admin order list and end up refactoring the entire order domain. Query Facade is the shield against "I just wanted to fix one screen — how did it come to this?"

Screen data versus business objects

When canceling an order, you need to verify whether shipment has already begun, whether there is a payment to refund, and whether the caller has the authority to cancel. If the order object is designed to enforce those rules, there is a clear reason to route cancellation through that object.

For an order list, there is no need to execute cancellation rules just to display the names and amounts of 20 orders, and the customer's full address or every attribute of each line item is equally unnecessary; instead, you define the scope of orders that may be read and the fields to display, then fetch data accordingly. Of course, what is omitted here is the behavior required for cancellation processing, not the query authorization or the meaning of the amounts.

Order Cancellation
API -> Cancel Processing -> Order's Business Rules -> Save Changes

Admin Order List
API -> OrderQueries.ListAsync -> Query and Result Composition -> List DTO

DTO stands for Data Transfer Object; in this example it is the shape of data delivered to the caller. The fact that OrderListRow holds an order number and a customer name does not mean that object also carries responsibility for canceling the order or changing the customer's name.

Selecting only the necessary fields from the database and shaping them into a result is called projection. In EF Core this appears as a Select call; in SQL it appears as the column list of a SELECT statement. Microsoft's own query implementation examples also compose a screen-specific ViewModel separately from the write-side domain model. The result of combining data from multiple entities can itself become the read model for the screen. ¹

Core structure

Authentication and authorization may be handled earlier in middleware or an application service, depending on the project. Regardless of where that happens, the Query Facade must retain the allowed scope and apply it to the actual query; it must not blindly trust the tenantId in the request URL to determine company scope. In this context, a tenant is a company or organization that shares the same service but manages its data separately, and the TenantId in this example is that company's identifier.

Personal note: middleware is the intermediate layer that every request passes through before reaching the actual processing logic; it is typically where common checks are placed. Think of it as a checkpoint.

The name "Facade" does not mean you need to build a massive class. If a screen has only one query, starting with a single function is perfectly fine, and if the order list and order detail always change together, you can group them into OrderQueries. Conversely, if you cram member statistics, payment reconciliation, and inventory reports all into one ApplicationQueryFacade, each query ends up dragging the others along again.

Facade, CQS, and CQRS

The Facade described in the Gang of Four's Design Patterns is a structural pattern that simplifies the interface for using a subsystem, and it is not limited to read operations. Query Facade is best understood as applying that idea specifically to query entry points. Because some codebases use names like OrderQueries, QueryService, or ReadService, looking at the actual responsibility rather than the class name is the more useful approach. ²

Personal note: "Look at the actual responsibility, not the class name" or "look at what the real code actually does" is sound advice, but the boundary of that actual responsibility is ultimately decided by the team lead. So whether the name is OrderQueries or QueryService, if the team lead says "this is a query," then it's a query.

But that raises the question of what a "query entry point" actually is, and we need a criterion for distinguishing what counts as a query from what does not.

That is where CQS, Command Query Separation, comes in. It is the principle of separating state-changing methods from queries that return results, and Fowler traces the term to Meyer's Object-Oriented Software Construction. ³

CQRS, Command Query Responsibility Segregation, is a design that allows different models for reads and writes. Fowler's 2011 description also notes that the two models can share the same database, so talking about CQRS does not necessarily mean two separate databases and an event bus must follow. ⁴

Personal note: Of course the current trend leans toward separating them, but there is no obligation to follow every trend. Honestly, I think the idea that "you absolutely must split into two models and attach an event bus" was largely manufactured by SaaS and BaaS vendors who need something to sell to developers. Part of growing as a programmer is trying out whatever Silicon Valley is hyping, and eventually realizing, "Ah, they're recommending this because they want to charge me for it." That said, I still try out the next trend anyway.

Query Facade is responsible for the query entry point and result composition within that landscape. It can sit on the read side of a CQRS architecture that separates write and read models, or it can be used in an ordinary CRUD application simply to extract one complex list query. Splitting out a single class is not sufficient reason to call the entire system CQRS.

Starting from the same application and the same DB

CancelOrderHandler -> domain objects and updates -> same DB
OrderQueries       -> list DTO query           -> same DB


Later, when read load or result shape demands it

CancelOrderHandler -> source DB
Change propagation -> search index or separate read store
OrderQueries       -> chosen read store

Moving to the lower-tier structure requires managing change propagation and read lag. A structure that reads directly from the same source database, as in the upper-tier approach, has no synchronization problem with a separate read store, and explaining both from the start as if they carry identical risks muddies the decision of whether to adopt CQRS at all.

When to use it

Query Facade becomes necessary when a gap opens up between the UI and domain objects: for example, when you need to attach a customer name to an order list, display both order count and revenue together on a dashboard, or blend results from multiple sources in a search view.

If a single FindById call gives you everything you need with no duplication, leaving the query in the existing code is perfectly fine. The case for separation grows stronger when external callers need a stable query contract, or when the query starts accumulating its own rules.

Implementing an Order List in C#

This example targets an administrator who can view all orders for a given company. OrderReadScope is constructed by server-side code after authentication and authorization checks, while OrderListFilter represents the filtering options the user can choose. The company ID is not included in the filter so that users cannot alter the scope of their own query.

EF Core is used for data store access, but no specific ORM is required for a Query Facade. I used plain C# simply because this is code I built for myself some time ago, and I am a C# programmer. An ORM is a tool that helps bridge objects and database tables; the code below uses C# 12 or later syntax to define both the read model and the Facade together. The small storage model intentionally excludes business methods such as order cancellation.

The runtime environment should be checked against the language version in use.

The generic overload used by Enum.IsDefined(status) in the example also exists in the .NET 7 source, but the fact that the code is written in C# 12 alone does not determine runtime and EF Core compatibility. This document's execution was validated with .NET 10 and EF Core SQLite 10.0.12, and EF Core 10 requires .NET 10. If you choose a different version, verify that the runtime, EF Core, and DB provider form a combination that is mutually supported.

The block below contains the primary types and query implementation, but you should not treat it as a self-contained runnable project that includes an execution entry point, database connection setup, and authentication/authorization handling. In a real application, you will need to wire up that configuration and the request adapter.

using Microsoft.EntityFrameworkCore;

public enum OrderStatus
{
    Pending,
    Paid,
    Cancelled
}

// Server code that has verified the authenticated principal creates this.
// The JSON sent by the user is not deserialized directly into this type.
// This `record` itself does not validate permissions or protect its constructor.
public sealed record OrderReadScope(long TenantId, bool CanReadOrders);

// The list is ordered by creation time descending, and by order number descending when timestamps are equal.
// Timestamps store milliseconds since the UTC Unix epoch and are placed as-is into the cursor.
public sealed record OrderCursor(long CreatedAtUnixMs, long Id);

public sealed record OrderListFilter(
    OrderStatus? Status = null,
    int PageSize = 20,
    OrderCursor? After = null);

public sealed record OrderListRow(
    long Id,
    string CustomerName,
    long TotalWon,
    OrderStatus Status,
    long CreatedAtUnixMs);

public sealed record OrderPage(
    IReadOnlyList<OrderListRow> Items,
    OrderCursor? NextCursor);

public sealed class OrderQueries(ReadDbContext db)
{
    public async Task<OrderPage> ListAsync(
        OrderReadScope scope,
        OrderListFilter filter,
        CancellationToken ct)
    {
        // A lack of permission and an empty list are distinguished from each other.
        if (!scope.CanReadOrders || scope.TenantId <= 0)
            throw new UnauthorizedAccessException("Order read denied.");

        // Requests that exceed the upper limit are reported as invalid input rather than silently truncated.
        if (filter.PageSize is < 1 or > 100)
            throw new ArgumentOutOfRangeException(nameof(filter), filter.PageSize, "Page size must be between 1 and 100.");

        if (filter.Status is { } status && !Enum.IsDefined(status))
            throw new ArgumentException("Unknown order status.", nameof(filter));

        if (filter.After is { } cursor &&
            (cursor.CreatedAtUnixMs < 0 || cursor.Id <= 0))
            throw new ArgumentException("Invalid order cursor.", nameof(filter));

        ct.ThrowIfCancellationRequested();

        // The allowed range and deletion policy are applied within the DB query.
        // The query does not fetch all data first and then filter by tenant in memory.
        var orders = db.Orders.AsNoTracking()
            .Where(o => o.TenantId == scope.TenantId && !o.IsDeleted);

        if (filter.Status is { } selectedStatus)
            orders = orders.Where(o => o.Status == selectedStatus);

        if (filter.After is { } after)
        {
            // In DESC ordering, the next page contains older timestamps or smaller IDs.
            // Orders created within the same millisecond have their order determined by ID.
            orders = orders.Where(o =>
                o.CreatedAtUnixMs < after.CreatedAtUnixMs ||
                (o.CreatedAtUnixMs == after.CreatedAtUnixMs && o.Id < after.Id));
        }

        // Even if customer numbers are duplicated across companies, they are never linked to customers from another company.
        // In this example, orders belonging to deleted customers are also excluded from the admin list.
        var visibleRows =
            from order in orders
            join customer in db.Customers.AsNoTracking()
                on new { order.TenantId, Id = order.CustomerId }
                equals new { customer.TenantId, customer.Id }
            where !customer.IsDeleted
            select new { Order = order, CustomerName = customer.Name };

        var rows = await visibleRows
            .OrderByDescending(row => row.Order.CreatedAtUnixMs)
            .ThenByDescending(row => row.Order.Id)
            .Take(filter.PageSize + 1)
            .Select(row => new OrderListRow(
                row.Order.Id,
                row.CustomerName,
                row.Order.TotalWon,
                row.Order.Status,
                row.Order.CreatedAtUnixMs))
            .ToListAsync(ct);

        // One extra row is read solely to confirm whether a next page exists.
        // No separate `COUNT` query is executed.
        var hasMore = rows.Count > filter.PageSize;
        if (hasMore)
            rows.RemoveAt(rows.Count - 1);

        OrderCursor? next = hasMore
            ? new OrderCursor(rows[^1].CreatedAtUnixMs, rows[^1].Id)
            : null;

        return new OrderPage(rows.AsReadOnly(), next);
    }
}

public sealed class Order
{
    public long TenantId { get; set; }
    public long Id { get; set; }
    public long CustomerId { get; set; }
    public long TotalWon { get; set; }
    public OrderStatus Status { get; set; }
    public long CreatedAtUnixMs { get; set; }
    public bool IsDeleted { get; set; }
}

public sealed class Customer
{
    public long TenantId { get; set; }
    public long Id { get; set; }
    public string Name { get; set; } = "";
    public bool IsDeleted { get; set; }
}

public sealed class ReadDbContext(DbContextOptions<ReadDbContext> options)
    : DbContext(options)
{
    public DbSet<Order> Orders => Set<Order>();
    public DbSet<Customer> Customers => Set<Customer>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Customer>()
            .HasKey(c => new { c.TenantId, c.Id });

        modelBuilder.Entity<Order>().HasKey(o => new { o.TenantId, o.Id });

        modelBuilder.Entity<Order>()
            .HasOne<Customer>()
            .WithMany()
            .HasForeignKey(o => new { o.TenantId, Id = o.CustomerId });

        modelBuilder.Entity<Order>()
            .HasIndex(o => new { o.TenantId, o.CreatedAtUnixMs, o.Id });

        modelBuilder.Entity<Order>().Property(o => o.Status)
            .HasConversion<string>();
    }
}

Apply sorting and row limits to the original order columns, then construct the DTO in the final Select. If you construct the DTO first and then attach conditions to its properties, you become subject to whether the provider can translate that expression to SQL, so expose sorting criteria as database columns and verify the actual SQL that is generated.

After means after the last row seen in the list. It is easy to write the condition direction backwards if you read it as meaning a later timestamp, but because this list descends from the newest orders, the next page looks for a smaller timestamp.

TotalWon in the example is the amount in Korean won that was confirmed and stored when the order was created. It is not a value recalculated by multiplying today's product price by the order quantity, nor is it net revenue after refunds or the actually settled payment amount. If the meaning of the amount needed on screen changes, start by changing the field name and the basis for its calculation.

Excluding orders from deleted customers is this example's policy; for settlement or audit queries, you might instead retain orders from deleted customers while anonymizing their names, or use the customer display name stored at the time of the order. Copying the same INNER JOIN as-is will cause historical orders to disappear from the screen, so confirm the retention policy for each view.

Note: the long in OrderListRow is an internal C# contract. If order numbers in a JSON API consumed by JavaScript can exceed the safe integer range, establish a separate wire contract such as serializing IDs as strings, and encode cursors in a way that preserves that precision.

The code that creates the query scope is also an authorization boundary.

OrderReadScope is a value that passes already-verified permissions into a query; it is not a mechanism that proves those permissions. Because its constructor is public, other code in the same process can create new OrderReadScope(999, true), and ListAsync does not re-determine whether that company actually belongs to the caller. This means you must not create this object by taking TenantId and CanReadOrders directly from the request's JSON body or query string.

The adapter that receives HTTP requests should obtain the query scope by mapping the authenticated principal to the server's membership and authorization policy, and configure all other entry points to go through the same policy.

Note: In ASP.NET Core, you can consolidate this judgment through policies and IAuthorizationService. However, a policy that only checks whether a user is logged in is not the same as the permission to read a specific company's orders, so you must also verify the allowed scope for that company. Narrowing the creation path to internal access or a restricted factory reduces mistakes, but you cannot consider a single type to be a complete barrier against arbitrary code running in the same process.⁵

The role of AsNoTracking

EF Core tracks entities so it can detect whether they have changed. Read-only queries sometimes do not need that tracking, which is why AsNoTracking is used, but attaching it alone does not prevent writes to the database. If other code calls SaveChanges on the same DbContext, or issues updates via SQL, writes can still occur. EF Core tracking and no-tracking queries

In other words, it signals that this is a read-only query where there is no need to modify and save the retrieved entities.

When you project only values into a DTO without including entities in the final result, as in this example, EF Core has no entities in the result to track in the first place. In that sense, AsNoTracking here is closer to a marker that communicates the intent of being read-only. After all, the foundation of good code is that it reads clearly.

If you need a hard boundary on writes, you can grant only the SELECT permission required to the read-only database account. Expressing intent through class names or ORM options is one thing; verifying what the database actually enforces is a separate concern.

Reading with SQL

When SQL becomes complex, you can write it directly inside the Facade or extract it into a dedicated query adapter. The caller still only knows ListAsync and OrderPage, while internally the implementation performs whatever queries are needed to produce the DTO.

The following is a PostgreSQL query expressing the same conditions as the list shown earlier. This SQL assumes a schema using lowercase table and column names.

Validating both values of the cursor

C#'s OrderCursor holds both a timestamp and an ID together, but when it is decomposed into HTTP inputs or SQL parameters, requests may arrive with only one of the two values. Treat the absence of both values as a request for the first page, the presence of both as a request for the next page, and the presence of only one as an input error to be rejected before the database query is executed. An adapter that receives external input as separate nullable values can construct the cursor as follows.

public static class OrderCursorInput
{
    public static OrderCursor? Parse(long? createdAtUnixMs, long? id)
    {
        if (createdAtUnixMs is null && id is null)
            return null;

        // If either one is missing, don't reset to the first page or return an empty list.
        if (createdAtUnixMs is not { } time || id is not { } orderId)
            throw new ArgumentException("Both cursor fields are required.");

        if (time < 0 || orderId <= 0)
            throw new ArgumentException("Invalid order cursor.");

        return new OrderCursor(time, orderId);
    }
}

Strings that cannot be parsed as numbers should be rejected at the request-parsing stage before reaching this function, and any ArgumentException raised here should also map to an input-error response from the API. If you catch the failure and replace the cursor with null, an invalid request silently becomes a first-page query.

-- $1: tenant_id verified by the server
-- $2: status string or NULL
-- $3: cursor's `created_at_unix_ms` or NULL
-- $4: cursor's `id` or NULL
-- $5: validated `page_size` + 1
-- At the execution boundary, $3 and $4 are validated together. A partial NULL is an input error.
-- The defensive conditions below alone will not raise an error for invalid input.
SELECT
    o.id,
    c.name AS customer_name,
    o.total_won,
    o.status,
    o.created_at_unix_ms
FROM orders AS o
JOIN customers AS c
  ON c.tenant_id = o.tenant_id
 AND c.id = o.customer_id
WHERE o.tenant_id = $1
  AND o.is_deleted = FALSE
  AND c.is_deleted = FALSE
  AND ($2::text IS NULL OR o.status = $2::text)
  AND (
      ($3::bigint IS NULL AND $4::bigint IS NULL)
      OR (
          $3::bigint IS NOT NULL
          AND $4::bigint IS NOT NULL
          AND (o.created_at_unix_ms, o.id)
              < ($3::bigint, $4::bigint)
      )
  )
ORDER BY o.created_at_unix_ms DESC, o.id DESC
LIMIT $5;

(timestamp, ID) < (cursor timestamp, cursor ID) compares the timestamp first, then the ID when timestamps are equal. This is the condition for finding the next page in descending order when both columns are NOT NULL, and it must not be applied as-is to queries that sort the two columns in different directions.⁶

Why bother checking that both values are present? Because if you rely solely on $3 IS NULL to detect a missing cursor as before, a pair like (NULL, 34) ignores the ID and reads the first page, while (1000, NULL) reads only rows older than 1000ms and can miss order 33 at the same timestamp. The existing comparison works under the assumption that both values always arrive together, but passing external input through without verifying that assumption produces incorrect results. (Admittedly, this level of validation may be overkill in practice.)

The SQL above guards against returning rows when a partial NULL is received, but that alone is not enough to distinguish an input error from a legitimately empty result set. Therefore, connect the earlier input validation to the execution boundary, and do not treat the SQL condition as a substitute for that validation.

Pass values as provider parameters rather than concatenating them into the SQL string. When status or cursor is absent, it is also possible to omit the corresponding condition as a fixed SQL fragment, but that is entirely different from interpolating user-supplied strings into SQL.

Review indexes in terms of the conditions and sort order of the query. The following are candidates for this list; the customers table also needs a uniqueness constraint on (tenant_id, id).

-- This is a candidate index for the latest per-company list of non-deleted orders.
CREATE INDEX ix_orders_tenant_created_id_visible
    ON orders (tenant_id, created_at_unix_ms DESC, id DESC)
    WHERE is_deleted = FALSE;

The earlier EF Core HasIndex is a regular composite index that includes deleted orders as well, while this SQL defines a PostgreSQL Partial Index covering only non-deleted orders. These are not two representations of the same index; they illustrate a default configuration versus a per-database optimization candidate. EF Core also supports HasFilter and IsDescending on providers that expose them, but the filter SQL and column names must match the actual mappings. EF Core Index Configuration

PostgreSQL's B-tree can also be scanned in reverse. When a query fixes tenant_id to a single value and reads the remaining two columns both in descending order, as in this case, a regular ascending index can still support the sort, so the absence of DESC alone is not grounds for concluding there is a performance defect. The coverage of a Partial Index and its sort direction are distinct characteristics; check the execution plan to confirm which index the query actually uses. PostgreSQL Indexes and ORDER BY

Creating an index does not automatically speed up every call. The query plan can vary depending on the selectivity of a particular status filter, the number of orders per tenant, and the number of customers to join, and queries that combine selection conditions with OR ... IS NULL must also be verified against the actual plan. Check the execution plan for row counts, sort operations, and buffer usage, and when necessary, use separate SQL for queries with and without the status filter. PostgreSQL EXPLAIN

-- Since this actually executes, account for the load when running it in production.
-- The values below are example values for verifying the execution plan.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_won, created_at_unix_ms
FROM orders
WHERE tenant_id = 7 AND is_deleted = FALSE
ORDER BY created_at_unix_ms DESC, id DESC
LIMIT 21;

This EXPLAIN is a simplified example that checks only the filter and sort on the orders table. When verifying the performance of the full Facade, measure queries that include the customer join along with the actual status and cursor conditions.

Why fetching the list first causes slowdowns

Even after building a Query Facade, if the implementation reads full objects internally, the read cost remains unchanged. When all orders are fetched from the database with ToListAsync and the page is then sliced or converted to DTOs in memory, the data transfer and allocation have already occurred before that point.

// Here is an example of the form to avoid.
// It first fetches all entity properties for every row that matches the condition.
var allOrders = await db.Orders
    .Where(o => o.TenantId == scope.TenantId)
    .ToListAsync(ct);

// Because the data is trimmed after it has already been read, the amount of data the database reads and sends does not decrease.
var page = allOrders
    .OrderByDescending(o => o.CreatedAtUnixMs)
    .ThenByDescending(o => o.Id)
    .Take(20)
    .ToList();

Include Select to pick the necessary columns, Where to filter rows, OrderBy to define order, and Take to set an upper limit in the query the repository executes. The EF Core efficient querying guide also covers projection and limiting result counts together. EF Core Efficient Querying

That said, creating a DTO is not a reason to strip out necessary business meaning. Decisions such as whether to include canceled orders in revenue totals, how much personally identifiable information to expose in each field, and where to convert time zones remain part of the query's contract.

N+1 queries and row multiplication from joins

If you fetch a list of 20 orders and then load each order's customer separately, one list query is followed by 20 customer queries. This pattern is called N+1.

This is probably the most common problem in the AI era, and it is also a growing pain for programmers. Creating and then fixing N+1 queries with every new program is simply part of the daily routine.

The meaning of N+1 is that the number of subsequent queries grows in proportion to the number of results returned by the first query.

// This is an illustrative anti-pattern. The sequential calls cause repeated round trips.
foreach (var order in orders)
{
    var customer = await customerRepository.FindAsync(order.CustomerId, ct);
    // It attaches the customer name to each order.
}

As in the order list example above, if each order belongs to exactly one customer, you can use a join to fetch the required name at the same time. When combining multiple external query APIs, also check whether they support batch retrieval. The fact that a Facade method is a single call does not mean the underlying database or network calls are reduced to one.

On the other hand, joining every table creates a different problem. If one order has two line items and three payment records, expanding both relationships simultaneously produces six rows. Summing the line item amounts in that state repeats the same item once for each payment record. The PostgreSQL documentation on join expressions confirms that each input row is combined with every matching row from the other side to form the result. PostgreSQL Table Expressions and Joins

Order A
  Items: Keyboard, Mouse
  Payment records: Approved, Partial Refund, Additional Refund

Items and payment records joined as-is
  Keyboard x Approved
  Keyboard x Partial Refund
  Keyboard x Additional Refund
  Mouse x Approved
  Mouse x Partial Refund
  Mouse x Additional Refund

The result is 6 rows, and each item's amount appears 3 times.

You can address this by computing per-order subtotals first and then joining, or by determining the page of orders first and then batch-fetching line items and payments separately for only those order IDs. Using SUM(DISTINCT amount) as a workaround will collapse distinct line items that happen to share the same amount.

-- After generating one row per order from the item aggregation, link it to the order.
-- Items with the same amount are each summed separately if they are different items.
WITH item_totals AS (
    SELECT tenant_id, order_id, SUM(amount_won) AS item_total_won
    FROM order_items
    WHERE tenant_id = $1
    GROUP BY tenant_id, order_id
)
SELECT
    o.id,
    COALESCE(it.item_total_won, 0) AS item_total_won
FROM orders AS o
LEFT JOIN item_totals AS it
  ON it.tenant_id = o.tenant_id AND it.order_id = o.id
WHERE o.tenant_id = $1 AND o.is_deleted = FALSE;

This query is an example of an aggregation structure that shows orders with no line items as 0; the page limit and soft-delete conditions for customers are not included. When attaching this to a production list, you can narrow the aggregation target to the orders on the current page, and you should also confirm upfront whether the displayed order amount carries the same meaning as this line item total.

Applying LIMIT 20 to an expanded result set gives you 20 expanded rows, not 20 orders. If each item in the list represents an order, define page boundaries at the order level and then attach the subordinate data afterward.

Pagination and cursors

Sorting requires a tiebreaker value

Sorting by creation timestamp alone leaves the relative order of orders created within the same millisecond undefined. Paginating in that state makes it hard to explain why orders are duplicated or missing, so the earlier example appends the order ID to make the ordering unique within the permitted result set. EF Core's pagination documentation also requires a fully unique sort.⁷

Sort: created_at_unix_ms DESC, id DESC

1000ms, Order 35
1000ms, Order 34 <- last order on the first page
1000ms, Order 33 <- should remain on the next page
 900ms, Order 40

Next page condition
time < 1000ms
OR (time = 1000ms AND id < 34)

Storing only the timestamp in the cursor makes it easy to skip order 33. Include both the original sort value and the ID as a tiebreaker, and preserve precision and sort direction exactly.

Choose between offset and keyset based on the expected UI behavior

The offset approach skips a given number of earlier results and then fetches the next batch; OFFSET 200000 LIMIT 20 skips 200,000 rows and reads 20. It makes jumping to an arbitrary page number convenient, but the cost of skipping those leading rows grows as you go deeper. For a feed-style screen where users scroll down through the latest entries, keyset pagination, which seeks the position just after the last sort value seen, is often the better fit. The earlier C# example uses exactly that approach, and the cursor is the value that marks where the next query should begin. Keyset pagination also requires a supporting index on those conditions to read efficiently.

The earlier example reads PageSize + 1 rows to determine whether a next page exists. This suits screens that don't need to compute the total order count on every request; if an administrator requires an exact total page count, a separate count query may be necessary.

A stable ordering is not the same as a fixed snapshot

Using a cursor does not mean all pages reflect data from the same point in time. If an order is deleted between pages, the next result set may be shorter; if a status filter is active and an order's status changes, that order may enter or leave the result set. If the creation timestamp used for sorting is itself updated, an already-read order may reappear or an unread order may be skipped.

Typical recent-activity feeds accept this kind of drift. Audit reports and bulk exports, by contrast, should be designed separately with a fixed reference point or version, a dedicated read store, or an appropriate snapshot query. Simply capturing the timestamp of the last item on the first page does not produce a snapshot that accounts for all subsequent modifications and deletions.

A cursor does not substitute for authorization

Even if a cursor obtained from another company's context is submitted, the tenant condition of the current request must be applied again. A cursor can serve as the boundary for slicing results, but it is not evidence that the holder of that cursor is authorized to access the underlying orders.

Exposing a cursor as an opaque token in an API and Base64-encoding it alone is not sufficient to prevent tampering. If the contract requires tamper resistance, use a signed or server-stored token, and you can also enforce rules that reject cursors whose sort order, filters, or tenant scope have changed. Regardless of the approach, the server must re-verify the authorization of the current request.

Dashboards aggregate multiple values

Dashboards are more about aggregation than lists. Today's order count, total completed payment amount, and recent orders can all be bundled into a single response, but the fact that they appear together in one response does not mean the numbers reflect the state at the same point in time.

GetDashboardAsync
  -> fetch today's order count
  -> fetch completed payment total
  -> fetch recent orders
  -> build DashboardDto

Under PostgreSQL's default Read Committed isolation, two consecutive SELECT statements within the same transaction can each see a different snapshot, so if a new order is committed after the order count is read, that new order may appear in the recent-orders list. If values from the same database must be read from the same snapshot, either combine them into a single SQL aggregation or choose a read transaction isolation level that meets that requirement. PostgreSQL's Repeatable Read isolation captures a snapshot at the time of the first data-reading or data-manipulation statement after BEGIN (excluding control statements such as BEGIN itself) and uses that same snapshot for all subsequent queries in the transaction. ⁸

A dashboard that reads from different databases or external APIs has no single common snapshot achievable within one DB transaction. Where necessary, include the aggregation reference time or source version of each value in the response, and define which values must be consistent with each other.

Do not use the same DbContext in parallel

You should not attach three queries to the same EF Core DbContext and call Task.WhenAll in an attempt to finish aggregation faster. EF Core does not support multiple parallel operations on a single context. DbContext threading limitations

Running queries on independent contexts does allow parallel calls, but it increases the number of database connections and instantaneous load, and any contract that required a common snapshot must be revisited. If you are aggregating from the same database, it is worth checking first whether conditional aggregation can be combined into a single SQL statement.

The difference between a Repository and a Service Layer

Because the names often overlap, it helps to look at what result each one actually promises.

Fowler's Repository definition focuses on a collection-like interface for accessing domain objects, while the Service Layer focuses on a boundary that coordinates application operations and interactions. In practice, a Query Facade may serve as the query portion of the Service Layer, or it may be offered under the name "read repository" to provide DTO-based lookups. Repository, Service Layer

Separating concerns does not mean you must always create one layer per concern. If you have Controller -> QueryService -> QueryFacade -> ReadRepository -> DbContext and every intermediate class simply forwards the same arguments and returns results unchanged, check whether each layer has a distinct decision it actually owns.

When returning IQueryable to the outside

If the Facade returns IQueryable<Order>, the caller ends up appending conditions and executing the query later. That caller then needs to understand the ORM's translation capabilities, the database connection lifetime, the execution timing, and the shape of the results, which means the list contract described here becomes loosely defined.

// The caller decides when and in what shape this query will be executed.
public IQueryable<Order> GetOrders() => db.Orders;

If the goal is a shared query composer, you can deliberately design such a contract, but for a UI-facing Facade, returning a fully executed DTO along with pagination information makes responsibilities clearer. Exports that require streaming can use an IAsyncEnumerable<Row> contract; in that case, define separately how long the connection must remain open during enumeration and how mid-stream cancellation or failure is handled.

Security concerns to uphold even in read-only contexts

A list endpoint can expose far more information at once than an API that fetches a single ID. Being read-only is not itself a condition that prevents data leakage.

Apply the company scope that the server has verified to every query, and include the company identifier in joins as well. As in the earlier example, customer number 5 can exist across multiple companies, so joining on customer_id alone may mix in names from other companies. Counts, totals, and CSV exports added to the order list must use the same scope, and you should also test for the mistake of properly restricting the list while counting the total against all companies.

For services with per-user access restrictions, TenantId alone is not sufficient. You must also apply the actual allowed scope, such as the assigned representative, team, or document ACL. The CanReadOrders check in the example is a simplification targeting only administrators who can view all orders for that company.

Include only the personal information needed for the screen in the DTO, and do not serialize DB entities directly. Even if an email address or payment identifier is needed on an admin screen, do not bundle it into a shared DTO with other lists so that it spreads automatically. The OWASP authorization guidelines emphasize deny-by-default, per-request authorization checks, and least privilege. OWASP Authorization Cheat Sheet

Pass SQL input values as parameters. Column names and sort directions sometimes cannot be bound as ordinary value parameters, so the server should select them from an allowlist. OWASP SQL Injection Prevention

// This is an example of sort selection when constructing SQL directly.
// Do not append an external sort string directly after ORDER BY.
string orderBy = sort switch
{
    "newest" => "o.created_at_unix_ms DESC, o.id DESC",
    "oldest" => "o.created_at_unix_ms ASC, o.id ASC",
    _ => throw new ArgumentException("Unsupported sort.")
};

If you add a new sort option, you must also update the cursor condition to match that sort. Simply adding oldest to the switch above while keeping the earlier descending cursor comparison will break pagination.

Minimize the privileges of the read-only DB account where possible, and apply limits on the number of rows returned and query execution time. The execution time limit is called a command timeout on the DB client side. Even when you pass the request's CancellationToken, the point at which the provider processes the cancellation may vary, so do not claim that passing the token alone guarantees immediate termination.

Do not silently convert failures into empty screens

Having no orders that match a filter and being unable to read from the DB are two different outcomes. If you catch a DB failure and return an empty array, an administrator may conclude that there are no orders today. If a stock query fails and you substitute stock = 0, users may think all items are sold out, and an automated system might even trigger out-of-stock processing or a reorder.
A stock level of zero and an inability to read the stock level are entirely different states.

The former means sales are going well; the latter means something is going wrong.

A personal note: I have not personally encountered this mistake in a real incident. That said, the principle of never overwriting an error with a normal value appears repeatedly across many system design references.

The examples use .NET's exception and cancellation contracts. If your project uses Result types or typed diagnostics, convert to your existing error contract at that boundary; there is no need to introduce a separate error scheme just because you are adding a Query Facade.

For screens that allow partial results, return the status of failed items alongside the response. Avoid contracts that silently substitute stock = 0 and report success when an inventory lookup has failed.

When something goes wrong:

{
  "orders": { "state": "ready", "count": 128 },
  "stock": {
    "state": "unavailable",
    "code": "STOCK_TIMEOUT"
  }
}

When everything succeeds:

{
  "orders": { "state": "ready", "count": 128 },
  "stock": {
    "state": "ready",
    "quantity": 0
  }
}

This is where you do not include internal SQL or connection strings; instead, define the status of each item in the API contract so the client can render it, and avoid wrapping the response in a success envelope for screens where a complete failure should be surfaced as such.

Using Query Facade with Cache-Aside

The Query Facade owns the query conditions and the response shape, while Cache-Aside owns the strategy for reusing those results. When the same order list is read multiple times, you can apply caching in a decorator outside the Facade or at a fixed point inside the query, but you should not let the cache redefine the read boundary.

API
  -> check current request's permission and read scope
  -> look up cached OrderPage
       hit  -> return result within allowed scope
       miss -> OrderQueries.ListAsync -> store result -> return

The cache key for a list should reflect the tenant scope, additional permission scope, filters, sort order and cursor, page size, and response version that affect the result. You can also encode scope and conditions as a normalized identifier; the example below is a conceptual illustration that assumes each component is normalized and safely encoded.

order-list:v1:tenant=7:scope=admin-all:status=Paid:
sort=newest:cursor=1000-34:size=20

Whether a user with different permissions may read the same key is determined by whether the result scope is identical. Simply appending scope=admin-all does not confer fine-grained permissions, and authorization checks at call time must be performed even on a cache hit.

List caches tend to have more invalidation targets than detail caches. Changing the status of a single order can affect the Paid list, the Pending list, the full list, and dashboard totals. Manage this by tracking which lists are affected, rotating a generation number, or setting a short TTL that the contract permits. Do not use this cache directly for settlement decisions that always require the latest value. Detailed cache population and invalidation flows are covered in Cache-Aside.

Read replicas and separate read models

A database that receives copies of changes from the primary DB and is used for reads is called a read replica. Even when read load drives you to shift queries to a replica or a search index, you can keep the caller's method signature unchanged; however, because an administrator who just changed an order status may see the old state when they refresh the list, read freshness must also be part of the contract.

Cancel request -> source DB commit
                  |
                  | propagation not yet complete
                  v
List query -> replica or read index -> stale state

Options for reads immediately after a write include directing those reads to the primary, confirming that the read model has caught up to the required version, or surfacing the lag to the user. The right choice depends on the screen and the business requirement, and the Query Facade name does not guarantee any of these automatically.

Updating a separate read model is managed outside the query request path. Having a query method create an order or modify business state to recover when the read model is missing blurs the boundary. Read models that need to be reconstructed should have a dedicated build, replay, and verification procedure.

Do not mix business mutations into queries

Mixing mutations into queries makes behavior hard to audit. Queries are invoked by crawlers, retries, and page refreshes alike, and there is no way to determine later which of those triggered a business change. Many anti-patterns begin precisely by packing different responsibilities into a single boundary. One of the most important skills for a programmer is the ability to identify the smallest unit of responsibility whose rationale can be explained independently within a mental model.

If GetOrderDetailAsync expires an unpaid order, reserves inventory, or marks a coupon as used, a page refresh becomes a business action. Crawlers, retries, and repeated lookups from an admin tab can all end up executing that action as well.

Read request -> load data needed by the screen

Expiry job -> a defined command or batch job -> check business rules -> update state

Infrastructure work such as logging, metrics, and caching query results belongs in a different category from changing order state. Rather than broadly permitting any side effects, establish a clear boundary that queries do not alter business state, and separately justify each infrastructure effect that is allowed. A read use case can be well-designed even if it is not a pure function.

There is also a standard for sharing display-related calculations. Concatenating product names or reformatting currency values are things that can be handled in the query layer, but rewriting business rules such as refundable amounts in list SQL can diverge from the rules on the write side. Assign a single owner for each rule and reuse it, or read results that have already been finalized according to that rule.

How to verify performance

Moving logic to a Facade does not automatically make queries dramatically faster. In most cases there is an improvement, but it ultimately depends on whether what was previously an N+1 query has been reduced to a join or a batch fetch, whether only the necessary columns are read instead of full objects, and whether deep pages repeat the same amount of work.

The values to check when verifying performance are as follows.

p50 and p95 mean that 50% and 95% of requests completed within those times, respectively. They are useful for surfacing slow requests that averages alone would hide; values such as rows read must be obtained from the database query plan or actual traces. Do not record the number of rows the Facade returned as if it were the number of rows the database read.

Give traces a name that distinguishes the operation type, such as Orders.List, and record relevant attributes like filter type or page size. Do not log raw search terms, customer names, or connection strings, and do not put full user IDs or cursors into metric labels, as that would cause the label cardinality to grow without bound.

Personal note: I started writing this document intending to jot down just a few quick thoughts, and before I knew it a week had passed. I came in to write one ListOrdersAsync and ended up covering authorization, tracing, performance, and logging. This is the trap of the Query Facade. A single query never stays a single query. The hardest assumption in programming is this: a smart programmer puts in only what is needed, while a foolish programmer puts in everything. That is why I am a foolish programmer.

Boundaries to test

Tests that only verify a mock repository returns the desired DTO cannot validate join behavior or sort order. Insert a small amount of data against a real provider that executes SQL, then verify the result contract and boundary conditions. It is important not to treat the EF Core InMemory provider as a valid substitute for reproducing SQL translation or relational constraints from an actual relational database.

For pagination tests, do not include only a single success example; also include cases that read through multiple pages to the end and compare the set of IDs and their order. Test combinations of status filters and cursors, and verify that the number of queries does not grow proportionally to the number of returned rows.

Using SQLite lets you quickly validate relational joins and some query translations, but its query plans, types, and isolation behavior differ from PostgreSQL or SQL Server. The SQL translation and actual load behavior of your production provider must be verified in separate integration tests.

When this pattern is not necessary

If your code reads a single item and returns it as-is, with no dedicated query policy and no stable external contract, adding a Facade and an interface may only increase the number of files you have to read. If the application service already handles the scope and result of queries well, it is fine to keep the logic there.

On the other hand, if you are designing a system that directly joins another service's DB without permission to read the source tables or event records, you need to resolve the data ownership boundary before renaming methods to Query Facade. Retrieve the necessary data through an approved query API or explicit read projections.

Separating small queries within the same DB, using a read replica, and operating a dedicated read store are decisions at different scales. Start with the scope that is sufficient to solve your current query problem.

Summary

A Query Facade collects the queries required by a screen or API and returns the results needed within the permitted scope. For an admin list, that contract includes the company and permission conditions, the fields to display, and the sort and page boundaries, while the internal implementation is free to choose an appropriate means such as EF Core or raw SQL.

The model that cancels an order and the result that displays a list do not have to be identical, and separating a single query does not require two databases or an event bus. Instead, verify that the query does not mutate business state, that it is not reading far more data than necessary, that data from other tenants is not mixed in, and that failures do not surface as normal empty results.

References

• Gamma, Helm, Johnson, Vlissides. Design Patterns: Elements of Reusable Object-Oriented Software. Background on structural patterns, including the Facade.

• Martin Fowler. Command Query Separation, CQRS. Distinguishing queries from mutations, and the read model from the write model.

• Martin Fowler. Repository, Service Layer. Accessing domain objects and defining application operation boundaries.

• Microsoft. Implement reads and queries in a CQRS microservice. Structuring queries and screen-facing DTOs independently of the domain model.

• Microsoft. Efficient querying, Pagination, Tracking and no-tracking queries, DbContext configuration, Indexes. Projection, result size limits, sorting, tracking and context lifetime, and index configuration.

• Microsoft. Policy-based authorization, What's New in EF Core 10. Server-side authorization decisions and the EF Core execution environment.

• PostgreSQL. Table expressions, Row Constructor Comparison, Indexes and ORDER BY, Transaction isolation, Using EXPLAIN. Joins, NULL and cursor comparisons, index ordering, snapshots, and execution plans.

• OWASP. Authorization Cheat Sheet, SQL Injection Prevention Cheat Sheet. Per-request authorization checks, and allowlists for parameters and sort fields.