# Fast Cross-Module Queries in .NET Modular Monoliths

* * *

A modular monolith gives each module clear ownership of its business rules and data. The Orders module controls orders, the Customers module controls customer information, and the Payments module controls payments. Even when every module stores its data in the same SQL database, separate schemas help make those boundaries visible. This works well until the application needs to display data spanning several modules.

An order details page in an online store might need the order header from the `ordering` schema, customer information from `customers`, product information from `catalog`, payment status from `payments`, and tracking information from `shipping`.

A traditional monolith could retrieve all of this with one SQL query. A strictly interpreted modular monolith may instead call five module contracts, with each module issuing its own query. The result is a clean-looking application layer built on a slower data-access pattern.

A modular monolith shouldn’t give up the relational database’s ability to execute efficient joins. The better approach is to keep ownership strict for commands and writes while introducing a controlled query module for read use cases that span module boundaries. This article develops that approach using an online store built with vertical slices, CQRS-style command and query separation, ASP.NET Core, modern C#, Dapper and SQL Server.

## The example modular monolith

Our online store contains five business modules:

*   Catalog owns products, categories and prices.
    
*   Customers owns customer profiles and addresses.
    
*   Ordering owns orders and order lines.
    
*   Payments owns payment attempts and payment status.
    
*   Shipping owns shipments, delivery methods and tracking details.
    

Each module uses a separate SQL schema while sharing one physical database.

![](https://cdn.hashnode.com/uploads/covers/67c36038c69a4b7143c5fc49/0eab6131-cc4a-4e0b-8de6-c0c782a7fd00.png align="center")

The source layout follows vertical slices inside each module:

```text
src/
├── Store.Api/
├── Store.Modules.Catalog/
│   ├── Products/
│   │   ├── CreateProduct/
│   │   ├── ChangePrice/
│   │   └── GetProduct/
│   └── Infrastructure/
├── Store.Modules.Customers/
│   ├── Customers/
│   ├── Addresses/
│   └── Infrastructure/
├── Store.Modules.Ordering/
│   ├── Orders/
│   │   ├── PlaceOrder/
│   │   ├── CancelOrder/
│   │   └── GetOrder/
│   └── Infrastructure/
├── Store.Modules.Payments/
├── Store.Modules.Shipping/
└── Store.Modules.Queries/
```

Every business module has its own domain model, DbContext, migrations and public contract. The Ordering module cannot update the Customers module’s tables, and the Payments module cannot reach into the Ordering module’s DbContext. Those boundaries remain valuable. The difficulty appears when we apply the same restrictions to every read.

## The cost of composing reads through module contracts

Think about an order details endpoint. A first implementation might ask the Ordering module for the order, then call Customers, Catalog, Payments and Shipping for the remaining information.

![](https://cdn.hashnode.com/uploads/covers/67c36038c69a4b7143c5fc49/7d32ae06-031a-40e7-bfde-c0573f8547ef.png align="center")

The module calls themselves are in-process and usually cheap. The five database operations beneath them are more significant. This design introduces repeated connection use, repeated command execution, repeated result mapping and application-side composition. It may also produce inconsistent results if data changes between the individual queries. Parallelising the module calls can reduce elapsed time, but it doesn’t remove the additional database work. It can also increase pressure on the connection pool during busy periods.

Batching also improves it:

```csharp
var customerIds = orders
    .Select(order => order.CustomerId)
    .Distinct()
    .ToArray();

var customers = await customerQueries.GetManyAsync(
    customerIds,
    stopToken);
```

Batching is useful when module composition is unavoidable, but a database join will usually remain the more natural option when all the required data already sits in the same relational database.

## Separate write ownership from read composition

CQRS gives us room to treat commands and queries differently. Commands change state. They must honour module ownership, aggregate boundaries, invariants, authorisation and transaction rules. Queries return information. They don’t change domain state, so they don’t need to load aggregates or travel through repositories designed for transactional updates. In the store, placing an order must still go through the owning modules. The Ordering module should not query `catalog.Stock` directly and decrement a column. It should use the Catalog module’s public operation for reserving stock. That operation can validate availability, enforce product rules and record the reservation correctly.

Displaying an order details page has different requirements. It can read the order, customer, payment and shipping data in one database operation without asking each aggregate to participate.

This gives us a useful boundary:

> Business modules exclusively own changes to their data. Cross-module query slices may read approved data from several schemas to produce purpose-built responses.

The query side is deliberately coupled to read structures. The command side remains protected from that coupling.

## Introducing a dedicated Query module

The store can add a module dedicated to application-level reads:

```text
Store.Modules.Queries
```

This module owns cross-module query use cases such as:

```text
Store.Modules.Queries/
├── Orders/
│   ├── GetOrderDetails/
│   ├── SearchOrders/
│   └── GetCustomerOrderHistory/
├── Checkout/
│   └── GetCheckoutLookups/
├── Customers/
│   └── GetCustomerOverview/
├── Operations/
│   └── GetFulfilmentDashboard/
└── Infrastructure/
    ├── QueryConnectionFactory.cs
    └── QueryDatabaseOptions.cs
```

The Query module does not contain aggregates, repositories or commands. It doesn’t become the owner of customers, orders or payments.

Its responsibilities are narrower:

1.  Execute read-only queries spanning one or more module schemas.
    
2.  Return DTOs shaped for a specific endpoint or consumer.
    
3.  Own database views or projection tables in a `queries` schema.
    
4.  Apply query-level authorisation and tenant filtering.
    
5.  Add read-side caching where appropriate.
    
6.  Measure and optimise important query paths.
    

The architecture now looks like this:

![](https://cdn.hashnode.com/uploads/covers/67c36038c69a4b7143c5fc49/2dfa059c-7db2-4906-83fa-030608fb68d6.png align="center")

The Query module is an application module rather than a domain module. It exists because some read use cases belong to the application as a whole rather than to one bounded context.

## Avoid turning it into a dumping ground

A project named `Queries` can easily become a folder containing hundreds of unrelated handlers. Vertical slices remain useful here. Organise the module by user-facing capability, not by database table.

For example:

```text
Orders/GetOrderDetails
Orders/SearchOrders
Checkout/GetCheckoutLookups
Operations/GetFulfilmentDashboard
```

Avoid generic abstractions such as:

```text
CustomerQueryService
OrderQueryService
ProductQueryService
```

Those services tend to reproduce repository-style APIs on the read side. Callers then compose them, bringing back the same chatty pattern the Query module was meant to remove. Each cross-module query should represent a complete use case and return the data required by that use case.

As the system grows, one Query module can be split by consuming surface:

```text
Store.Modules.StorefrontQueries
Store.Modules.BackOfficeQueries
Store.Modules.Reporting
```

The storefront, operational back office and reporting workloads usually have different security, latency, caching and consistency requirements. Keeping them separate can prevent a single central query project from becoming another monolith inside the monolith.

## Three ways to implement cross-module queries

A shared database gives us three practical implementation choices.

### Direct cross-schema SQL

The query handler can reference module-owned tables directly:

```sql
SELECT
    o.Id,
    o.Status,
    c.DisplayName,
    p.Status AS PaymentStatus
FROM ordering.Orders AS o
INNER JOIN customers.Customers AS c
    ON c.Id = o.CustomerId
LEFT JOIN payments.Payments AS p
    ON p.OrderId = o.Id
WHERE o.Id = @OrderId;
```

This provides the least ceremony and gives the query author complete control over the SQL. It also gives the Query module direct knowledge of table names and columns. A schema migration in any participating module may therefore require a matching query change. This can be an acceptable trade-off in a modular monolith, provided the dependency is explicit and covered by integration tests.

### Published read views

Each business module can expose a stable database view containing the fields it permits cross-module queries to consume. For example, Catalog might publish:

```sql
CREATE OR ALTER VIEW catalog.ProductReadContract
AS
SELECT
    Id AS ProductId,
    Sku,
    Name,
    Slug,
    ThumbnailUrl,
    IsActive
FROM catalog.Products;
```

Customers might publish:

```sql
CREATE OR ALTER VIEW customers.CustomerReadContract
AS
SELECT
    Id AS CustomerId,
    DisplayName,
    Email,
    CountryCode
FROM customers.Customers;
```

The Query module then joins the published views rather than private source tables.

![](https://cdn.hashnode.com/uploads/covers/67c36038c69a4b7143c5fc49/f512fe84-b9d0-465a-939e-f129c0db53a8.png align="center")

This creates a database-level read contract. The owning module can reorganise its underlying tables while preserving the published view. When the read contract must change, the change becomes visible and can be managed deliberately. A normal SQL Server view stores a query definition rather than a precomputed result. It shouldn’t automatically be described as a materialised view. Its performance still depends on the generated execution plan and the indexes on the underlying tables. Views are useful for controlling shape and access, but they don’t remove the cost of a complex join.

### Projection tables

For expensive or extremely frequent reads, the Query module can own a physical table containing precomputed data.

An operational order search table:

```sql
CREATE TABLE queries.OrderSearch
(
    OrderId uniqueidentifier NOT NULL,
    CustomerId uniqueidentifier NOT NULL,
    CustomerName nvarchar(200) NOT NULL,
    PlacedAt datetimeoffset NOT NULL,
    OrderStatus nvarchar(50) NOT NULL,
    PaymentStatus nvarchar(50) NULL,
    ShipmentStatus nvarchar(50) NULL,
    TotalAmount decimal(18, 2) NOT NULL,
    Currency char(3) NOT NULL,
    LastUpdatedAt datetimeoffset NOT NULL,

    CONSTRAINT PK_OrderSearch
        PRIMARY KEY (OrderId)
);
```

The source modules publish events such as `OrderPlaced`, `PaymentCaptured`, `ShipmentDispatched` and `CustomerRenamed`. Projection handlers update the Query module’s table.

![](https://cdn.hashnode.com/uploads/covers/67c36038c69a4b7143c5fc49/949e9774-9cad-4004-8175-3594be6c185c.png align="center")

The resulting query is simple and predictable:

```sql
SELECT
    OrderId,
    CustomerName,
    PlacedAt,
    OrderStatus,
    PaymentStatus,
    ShipmentStatus,
    TotalAmount,
    Currency
FROM queries.OrderSearch
WHERE CustomerName LIKE @SearchTerm
ORDER BY PlacedAt DESC
OFFSET @Offset ROWS
FETCH NEXT @PageSize ROWS ONLY;
```

The trade-off is eventual consistency. A newly captured payment might take a short period to appear in the search result. Projection tables are well suited to dashboards, search pages, reports and frequently requested summaries. They add unnecessary complexity when a straightforward cross-schema join already performs well.

## Choosing between the approaches

The implementation can be selected per query rather than imposed across the entire application.

| Approach | Consistency | Runtime cost | Coupling | Best use |
| --- | --- | --- | --- | --- |
| Direct cross-schema SQL | Current source data | Join executed per request | Query module knows tables | Normal application queries |
| Published SQL views | Current source data | Join executed per request | Stable read contracts | Controlled cross-schema access |
| Projection table | Usually eventual | Cheap indexed read | Event and projection maintenance | Search, dashboards and heavy reads |
| Module contract composition | Depends on calls | Multiple calls and queries | Public application contracts | Data outside the database or behaviour-driven operations |

Start with direct SQL or published views. Introduce projections where measurements show that the direct query is too expensive, too difficult to index or requested too frequently.

## Creating a query schema

A separate `queries` schema gives the Query module somewhere to own database views, stored procedures and projection tables.

```sql
CREATE SCHEMA queries;
```

A cross-module order header view could look like this:

```sql
CREATE OR ALTER VIEW queries.OrderHeader
AS
SELECT
    o.Id AS OrderId,
    o.CustomerId,
    o.PlacedAt,
    o.Status AS OrderStatus,
    o.TotalAmount,
    o.Currency,
    c.DisplayName AS CustomerName,
    c.Email,
    latestPayment.Status AS PaymentStatus,
    latestShipment.Status AS ShipmentStatus,
    latestShipment.TrackingNumber
FROM ordering.Orders AS o
INNER JOIN customers.CustomerReadContract AS c
    ON c.CustomerId = o.CustomerId
OUTER APPLY
(
    SELECT TOP (1)
        p.Status
    FROM payments.Payments AS p
    WHERE p.OrderId = o.Id
    ORDER BY p.CreatedAt DESC
) AS latestPayment
OUTER APPLY
(
    SELECT TOP (1)
        s.Status,
        s.TrackingNumber
    FROM shipping.Shipments AS s
    WHERE s.OrderId = o.Id
    ORDER BY s.CreatedAt DESC
) AS latestShipment;
```

A second view returns the order lines:

```sql
CREATE OR ALTER VIEW queries.OrderLine
AS
SELECT
    line.OrderId,
    line.LineNumber,
    line.ProductId,
    line.ProductName,
    product.Sku,
    product.Slug AS CurrentProductSlug,
    product.ThumbnailUrl,
    line.Quantity,
    line.UnitPrice,
    line.LineTotal
FROM ordering.OrderLines AS line
LEFT JOIN catalog.ProductReadContract AS product
    ON product.ProductId = line.ProductId;
```

The order line stores the product name and price recorded at the point of purchase. Catalog contributes current presentation information such as the product slug and thumbnail. This prevents a product rename or price change from rewriting the historical meaning of an existing order. The views should explicitly list their columns. Using `SELECT *` makes accidental contract changes much more likely.

## Enforcing read-only access

Code conventions provide some protection, but database permissions can make the boundary stronger. The Query module can use a separate connection string whose database principal only has permission to read approved query objects.

```sql
CREATE ROLE store_query_reader;

GRANT SELECT
ON OBJECT::queries.OrderHeader
TO store_query_reader;

GRANT SELECT
ON OBJECT::queries.OrderLine
TO store_query_reader;

ALTER ROLE store_query_reader
ADD MEMBER store_query_runtime;
```

The runtime principal receives `SELECT` permission but no permission to insert, update or delete source data. When views reference objects in other schemas, SQL Server permissions and ownership chaining need to be tested against the database’s ownership configuration. The important outcome is straightforward: the Query module’s runtime identity should be able to execute its approved reads and should fail when attempting to modify module-owned tables.

If the entire application uses one highly privileged database login, module data ownership depends almost entirely on code review and developer discipline. Separate command and query credentials provide stronger enforcement without requiring separate databases.

## Query module infrastructure

The Query module can use a small connection factory around `Microsoft.Data.SqlClient`.

```csharp
using System.Data.Common;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Options;

namespace Store.Modules.Queries.Infrastructure;

internal interface IQueryConnectionFactory
{
    ValueTask<DbConnection> OpenAsync(
        CancellationToken stopToken);
}

internal sealed class SqlQueryConnectionFactory(
    IOptions<QueryDatabaseOptions> options)
    : IQueryConnectionFactory
{
    private readonly string _connectionString =
        options.Value.ConnectionString;

    public async ValueTask<DbConnection> OpenAsync(
        CancellationToken stopToken)
    {
        var connection = new SqlConnection(_connectionString);

        try
        {
            await connection.OpenAsync(stopToken);
            return connection;
        }
        catch
        {
            await connection.DisposeAsync();
            throw;
        }
    }
}
```

The options type contains the read-only connection string:

```csharp
namespace Store.Modules.Queries.Infrastructure;

internal sealed class QueryDatabaseOptions
{
    public const string SectionName = "QueryDatabase";

    public required string ConnectionString { get; init; }
}
```

The module registers its infrastructure and handlers through one extension method:

```csharp
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;

namespace Store.Modules.Queries;

public static class QueriesModule
{
    public static IServiceCollection AddQueriesModule(
        this IServiceCollection services,
        IConfiguration configuration)
    {
        services
            .AddOptions<QueryDatabaseOptions>()
            .Bind(configuration.GetSection(
                QueryDatabaseOptions.SectionName))
            .Validate(
                options => !string.IsNullOrWhiteSpace(
                    options.ConnectionString),
                "A query database connection string is required.")
            .ValidateOnStart();

        services.AddScoped<
            IQueryConnectionFactory,
            SqlQueryConnectionFactory>();

        services.AddScoped<
            Orders.GetOrderDetails.Handler>();

        services.AddScoped<
            Checkout.GetCheckoutLookups.Handler>();

        services.AddHybridCache();

        return services;
    }
}
```

The host application remains responsible for composing the modules:

```csharp
var builder = WebApplication.CreateBuilder(args);

builder.Services
    .AddCatalogModule(builder.Configuration)
    .AddCustomersModule(builder.Configuration)
    .AddOrderingModule(builder.Configuration)
    .AddPaymentsModule(builder.Configuration)
    .AddShippingModule(builder.Configuration)
    .AddQueriesModule(builder.Configuration);

var app = builder.Build();

app.MapStoreQueries();

app.Run();
```

## A vertical slice for order details

The order details query is kept together as a feature:

```text
Orders/
└── GetOrderDetails/
    ├── Query.cs
    ├── Response.cs
    ├── Handler.cs
    └── Endpoint.cs
```

The query contains both the requested order and the customer making the request.

```csharp
namespace Store.Modules.Queries.Orders.GetOrderDetails;

internal sealed record Query(
    Guid OrderId,
    Guid CustomerId);
```

The response is shaped around the API rather than any domain aggregate.

```csharp
namespace Store.Modules.Queries.Orders.GetOrderDetails;

internal sealed record OrderDetailsResponse(
    Guid OrderId,
    DateTimeOffset PlacedAt,
    string Status,
    MoneyResponse Total,
    CustomerResponse Customer,
    PaymentResponse? Payment,
    ShipmentResponse? Shipment,
    IReadOnlyList<OrderLineResponse> Lines);

internal sealed record MoneyResponse(
    decimal Amount,
    string Currency);

internal sealed record CustomerResponse(
    Guid CustomerId,
    string DisplayName,
    string Email);

internal sealed record PaymentResponse(
    string Status);

internal sealed record ShipmentResponse(
    string Status,
    string? TrackingNumber);

internal sealed record OrderLineResponse(
    int LineNumber,
    Guid ProductId,
    string ProductName,
    string? Sku,
    string? ProductUrl,
    string? ThumbnailUrl,
    int Quantity,
    decimal UnitPrice,
    decimal LineTotal);
```

The handler executes two result sets as one SQL batch and one database round trip. Using two result sets avoids producing a large Cartesian result where the order header, payment and shipping columns are repeated once for every order line.

```csharp
using Dapper;
using Store.Modules.Queries.Infrastructure;

namespace Store.Modules.Queries.Orders.GetOrderDetails;

internal sealed class Handler(
    IQueryConnectionFactory connectionFactory)
{
    public async Task<OrderDetailsResponse?> Handle(
        Query query,
        CancellationToken stopToken)
    {
        const string sql = """
            SELECT
                OrderId,
                CustomerId,
                PlacedAt,
                OrderStatus,
                TotalAmount,
                Currency,
                CustomerName,
                Email,
                PaymentStatus,
                ShipmentStatus,
                TrackingNumber
            FROM queries.OrderHeader
            WHERE OrderId = @OrderId
              AND CustomerId = @CustomerId;

            SELECT
                LineNumber,
                ProductId,
                ProductName,
                Sku,
                CurrentProductSlug,
                ThumbnailUrl,
                Quantity,
                UnitPrice,
                LineTotal
            FROM queries.OrderLine
            WHERE OrderId = @OrderId
            ORDER BY LineNumber;
            """;

        await using var connection =
            await connectionFactory.OpenAsync(stopToken);

        var command = new CommandDefinition(
            sql,
            new
            {
                query.OrderId,
                query.CustomerId
            },
            cancellationToken: stopToken);

        using var results =
            await connection.QueryMultipleAsync(command);

        var header =
            await results.ReadSingleOrDefaultAsync<HeaderRow>();

        if (header is null)
        {
            return null;
        }

        var lines = (await results.ReadAsync<LineRow>())
            .Select(line => new OrderLineResponse(
                line.LineNumber,
                line.ProductId,
                line.ProductName,
                line.Sku,
                BuildProductUrl(line.CurrentProductSlug),
                line.ThumbnailUrl,
                line.Quantity,
                line.UnitPrice,
                line.LineTotal))
            .ToArray();

        return new OrderDetailsResponse(
            header.OrderId,
            header.PlacedAt,
            header.OrderStatus,
            new MoneyResponse(
                header.TotalAmount,
                header.Currency),
            new CustomerResponse(
                header.CustomerId,
                header.CustomerName,
                header.Email),
            header.PaymentStatus is null
                ? null
                : new PaymentResponse(
                    header.PaymentStatus),
            header.ShipmentStatus is null
                ? null
                : new ShipmentResponse(
                    header.ShipmentStatus,
                    header.TrackingNumber),
            lines);
    }

    private static string? BuildProductUrl(string? slug) =>
        string.IsNullOrWhiteSpace(slug)
            ? null
            : $"/products/{slug}";

    private sealed class HeaderRow
    {
        public Guid OrderId { get; set; }
        public Guid CustomerId { get; set; }
        public DateTimeOffset PlacedAt { get; set; }
        public string OrderStatus { get; set; } = string.Empty;
        public decimal TotalAmount { get; set; }
        public string Currency { get; set; } = string.Empty;
        public string CustomerName { get; set; } = string.Empty;
        public string Email { get; set; } = string.Empty;
        public string? PaymentStatus { get; set; }
        public string? ShipmentStatus { get; set; }
        public string? TrackingNumber { get; set; }
    }

    private sealed class LineRow
    {
        public int LineNumber { get; set; }
        public Guid ProductId { get; set; }
        public string ProductName { get; set; } = string.Empty;
        public string? Sku { get; set; }
        public string? CurrentProductSlug { get; set; }
        public string? ThumbnailUrl { get; set; }
        public int Quantity { get; set; }
        public decimal UnitPrice { get; set; }
        public decimal LineTotal { get; set; }
    }
}
```

The endpoint retrieves the current customer from the authenticated request and includes it in the database query.

```csharp
namespace Store.Modules.Queries.Orders.GetOrderDetails;

internal static class Endpoint
{
    public static IEndpointRouteBuilder Map(
        IEndpointRouteBuilder endpoints)
    {
        endpoints.MapGet(
                "/api/store/orders/{orderId:guid}",
                HandleAsync)
            .RequireAuthorization("store-customer")
            .WithName("GetOrderDetails")
            .WithTags("Orders");

        return endpoints;
    }

    private static async Task<IResult> HandleAsync(
        Guid orderId,
        ICurrentCustomer currentCustomer,
        Handler handler,
        CancellationToken stopToken)
    {
        var response = await handler.Handle(
            new Query(
                orderId,
                currentCustomer.CustomerId),
            stopToken);

        return response is null
            ? Results.NotFound()
            : Results.Ok(response);
    }
}
```

Authorisation is applied in the SQL predicate:

```sql
WHERE OrderId = @OrderId
  AND CustomerId = @CustomerId
```

The handler does not load an order belonging to another customer and then reject it in memory. Filtering at the database boundary reduces exposure through logs, tracing, caches and application defects. An administrative order endpoint should be a separate slice with its own authorisation policy and query rather than adding a collection of flags to the customer-facing handler.

## Cross-module lookup queries

Lookup endpoints can also become chatty.

A checkout page might require:

*   The customer’s saved delivery addresses.
    
*   Shipping methods available for the destination country.
    
*   Payment methods currently enabled for the store.
    
*   A set of order limits or checkout configuration values.
    

Requesting these through three or four module contracts produces several database queries for data that the client needs at the same time.

The Query module can provide one checkout lookup slice:

```text
Checkout/
└── GetCheckoutLookups/
    ├── Query.cs
    ├── Response.cs
    ├── Handler.cs
    └── Endpoint.cs
```

The response is specific to the checkout screen:

```csharp
namespace Store.Modules.Queries.Checkout.GetCheckoutLookups;

internal sealed record Query(
    Guid CustomerId,
    string CountryCode);

internal sealed record CheckoutLookupsResponse(
    IReadOnlyList<AddressOption> Addresses,
    IReadOnlyList<ShippingMethodOption> ShippingMethods,
    IReadOnlyList<PaymentMethodOption> PaymentMethods);

internal sealed record AddressOption(
    Guid Id,
    string Label,
    string Recipient,
    string Summary);

internal sealed record ShippingMethodOption(
    Guid Id,
    string Name,
    decimal Price,
    string Currency,
    int EstimatedDays);

internal sealed record PaymentMethodOption(
    string Code,
    string DisplayName);
```

The handler executes the three lookups in one batch and optionally caches the combined result.

```csharp
using Dapper;
using Microsoft.Extensions.Caching.Hybrid;
using Store.Modules.Queries.Infrastructure;

namespace Store.Modules.Queries.Checkout.GetCheckoutLookups;

internal sealed class Handler(
    IQueryConnectionFactory connectionFactory,
    HybridCache cache)
{
    private static readonly HybridCacheEntryOptions CacheOptions =
        new()
        {
            Expiration = TimeSpan.FromMinutes(2),
            LocalCacheExpiration = TimeSpan.FromSeconds(30)
        };

    public ValueTask<CheckoutLookupsResponse> Handle(
        Query query,
        CancellationToken stopToken)
    {
        var cacheKey =
            $"checkout-lookups:{query.CustomerId:N}:{query.CountryCode}";

        return cache.GetOrCreateAsync(
            cacheKey,
            token => LoadAsync(query, token),
            CacheOptions,
            cancellationToken: stopToken);
    }

    private async ValueTask<CheckoutLookupsResponse> LoadAsync(
        Query query,
        CancellationToken stopToken)
    {
        const string sql = """
            SELECT
                Id,
                Label,
                RecipientName AS Recipient,
                CONCAT(
                    AddressLine1,
                    ', ',
                    City,
                    ', ',
                    PostalCode) AS Summary
            FROM customers.Addresses
            WHERE CustomerId = @CustomerId
              AND IsActive = 1
            ORDER BY IsDefault DESC, Label;

            SELECT
                Id,
                Name,
                Price,
                Currency,
                EstimatedDays
            FROM shipping.Methods
            WHERE CountryCode = @CountryCode
              AND IsEnabled = 1
            ORDER BY SortOrder;

            SELECT
                Code,
                DisplayName
            FROM payments.PaymentMethods
            WHERE IsEnabled = 1
            ORDER BY SortOrder;
            """;

        await using var connection =
            await connectionFactory.OpenAsync(stopToken);

        var command = new CommandDefinition(
            sql,
            new
            {
                query.CustomerId,
                query.CountryCode
            },
            cancellationToken: stopToken);

        using var results =
            await connection.QueryMultipleAsync(command);

        var addresses =
            (await results.ReadAsync<AddressOption>())
            .AsList();

        var shippingMethods =
            (await results.ReadAsync<ShippingMethodOption>())
            .AsList();

        var paymentMethods =
            (await results.ReadAsync<PaymentMethodOption>())
            .AsList();

        return new CheckoutLookupsResponse(
            addresses,
            shippingMethods,
            paymentMethods);
    }
}
```

The cache key includes the customer because saved addresses are customer-specific. Leaving the customer out could serve one customer’s address information to another. Short expiration may be enough for low-risk lookup data. More sensitive or frequently changing values can be invalidated through module events. Caching shouldn’t compensate for an inefficient query. First reduce the number of round trips and inspect the execution plan. Add caching after the underlying query is understood.

## When a lookup still belongs to a business module

The Query module shouldn’t absorb every read endpoint. A product lookup used only by the Catalog administration screen can remain in Catalog. A customer address lookup used only inside Customers can remain there as well. The cross-module layer is useful when the response genuinely belongs to a wider application use case. It should also avoid making business decisions.

Suppose the checkout process needs to know whether a shipping method can handle a hazardous product. Reading a `SupportsHazardousGoods` column and deciding inside the Query module would duplicate Shipping’s business rules. The Query module can display shipping methods. The command that confirms the order should still ask Shipping to validate and select the method through its public application contract. Query results can support a user experience, but they shouldn’t become an alternative command model.

## Using EF Core instead of Dapper

Dapper is convenient for explicit SQL, but the same architecture can use EF Core. A query DbContext should remain separate from the module write DbContexts.

```csharp
using Microsoft.EntityFrameworkCore;

namespace Store.Modules.Queries.Infrastructure;

internal sealed class QueryDbContext(
    DbContextOptions<QueryDbContext> options)
    : DbContext(options)
{
    public DbSet<OrderSearchRow> Orders =>
        Set<OrderSearchRow>();

    protected override void OnModelCreating(
        ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<OrderSearchRow>(entity =>
        {
            entity.HasNoKey();

            entity.ToView(
                "OrderSearchView",
                "queries");

            entity.Property(row => row.OrderId);
            entity.Property(row => row.CustomerName);
            entity.Property(row => row.PlacedAt);
            entity.Property(row => row.OrderStatus);
            entity.Property(row => row.PaymentStatus);
            entity.Property(row => row.ShipmentStatus);
            entity.Property(row => row.TotalAmount);
            entity.Property(row => row.Currency);
        });
    }
}
```

The keyless type represents a read model:

```csharp
namespace Store.Modules.Queries.Infrastructure;

internal sealed class OrderSearchRow
{
    public Guid OrderId { get; set; }
    public string CustomerName { get; set; } = string.Empty;
    public DateTimeOffset PlacedAt { get; set; }
    public string OrderStatus { get; set; } = string.Empty;
    public string? PaymentStatus { get; set; }
    public string? ShipmentStatus { get; set; }
    public decimal TotalAmount { get; set; }
    public string Currency { get; set; } = string.Empty;
}
```

The handler can project directly into its response:

```csharp
using Microsoft.EntityFrameworkCore;

namespace Store.Modules.Queries.Orders.SearchOrders;

internal sealed class Handler(
    QueryDbContext dbContext)
{
    public async Task<IReadOnlyList<OrderSummaryResponse>> Handle(
        Query query,
        CancellationToken stopToken)
    {
        return await dbContext.Orders
            .Where(order =>
                query.SearchTerm == null ||
                order.CustomerName.Contains(query.SearchTerm))
            .OrderByDescending(order => order.PlacedAt)
            .Take(query.PageSize)
            .Select(order => new OrderSummaryResponse(
                order.OrderId,
                order.CustomerName,
                order.PlacedAt,
                order.OrderStatus,
                order.PaymentStatus,
                order.ShipmentStatus,
                order.TotalAmount,
                order.Currency))
            .ToListAsync(stopToken);
    }
}
```

EF Core keyless entity types are read-only from the model’s perspective and aren’t tracked for changes. They fit query views and projection tables well. Dapper remains useful where precise SQL, multiple result sets or provider-specific features make the query easier to control. A Query module can use both without introducing a repository abstraction over them.

## Query consistency

A direct query against the shared database reads current source data, but consistency still depends on the transaction isolation level and the shape of the SQL. The order details example executes two `SELECT` statements in one batch. This gives one network round trip, but the statements remain separate. Under concurrent updates, the header and lines could theoretically observe different committed states. For most order display pages, that small window may be acceptable.

Where the response must represent one coherent database snapshot, use an appropriate transaction isolation level or redesign the SQL as one statement. SQL Server snapshot isolation can provide a transactionally consistent read without holding long-running shared locks, but it must be enabled and assessed for the workload. Avoid adding transactions automatically to every query. They can increase version-store usage, lock duration or contention depending on the selected isolation level.

Consistency should be chosen according to the use case:

*   Checkout confirmation and command validation require authoritative module operations.
    
*   An order details screen usually needs current, coherent-enough data.
    
*   An operational dashboard can often tolerate a projection lag.
    
*   Financial reconciliation may require a carefully defined reporting snapshot.
    

The Query module gives each slice somewhere to state and implement that choice.

## Indexing cross-module queries

Moving SQL into a Query module doesn’t make it fast by itself. The base schemas still need indexes supporting the join and filter paths. For the order details query, likely candidates include:

```sql
CREATE INDEX IX_Payments_OrderId_CreatedAt
ON payments.Payments
(
    OrderId,
    CreatedAt DESC
)
INCLUDE
(
    Status
);

CREATE INDEX IX_Shipments_OrderId_CreatedAt
ON shipping.Shipments
(
    OrderId,
    CreatedAt DESC
)
INCLUDE
(
    Status,
    TrackingNumber
);

CREATE INDEX IX_OrderLines_OrderId_LineNumber
ON ordering.OrderLines
(
    OrderId,
    LineNumber
)
INCLUDE
(
    ProductId,
    ProductName,
    Quantity,
    UnitPrice,
    LineTotal
);
```

Indexes belong with the module that owns the underlying table. The Query module can identify the need, but the Payments module should own the migration adding an index to `payments.Payments`. This creates a legitimate architecture dependency:

```text
GetOrderDetails requires efficient lookup of payments by OrderId and CreatedAt.
```

That dependency should be documented and tested rather than hidden behind a service call. Inspect actual execution plans using realistic data volumes. A query that performs well with a few thousand development rows may behave very differently with millions of orders and payment attempts.

## Schema migrations and deployment order

Cross-schema queries create migration dependencies. Suppose Catalog renames `ThumbnailUrl` to `PrimaryImageUrl`. If the Query module reads the source table directly, its deployment must change at the same time. Published views reduce this pressure. Catalog can change its private table and preserve the existing contract:

```sql
CREATE OR ALTER VIEW catalog.ProductReadContract
AS
SELECT
    Id AS ProductId,
    Sku,
    Name,
    Slug,
    PrimaryImageUrl AS ThumbnailUrl,
    IsActive
FROM catalog.Products;
```

The Query module continues reading `ThumbnailUrl`. The database deployment order should normally be:

![](https://cdn.hashnode.com/uploads/covers/67c36038c69a4b7143c5fc49/fac839a7-0e97-4d22-a2ae-5c4b42a4b9c7.png align="center")

For zero-downtime deployments, use expand-and-contract changes. Add the new column first, update readers, migrate the data, and remove the old column in a later release. A view doesn’t eliminate compatibility planning. It gives that planning an explicit contract surface.

## Preventing circular dependencies

Business modules shouldn’t reference the Query module. The dependency direction should be:

```text
API host
  -> business modules
  -> Query module

Query module
  -> database read contracts
```

The Ordering command handlers should never call `GetOrderDetails.Handler`. A query response is shaped for presentation and may combine data with weaker consistency guarantees than a command requires. Similarly, domain entities should not appear in Query module responses. Returning an `Order` aggregate from a cross-module query would expose behaviour and persistence assumptions that don’t belong on the read side. Use plain records and DTOs designed for each slice.

## Observability

Cross-module queries deserve their own telemetry because they often become important user-facing paths.

Record:

*   Total query duration.
    
*   Database command duration.
    
*   Returned row count.
    
*   Cache hit or miss.
    
*   Query timeouts and cancellations.
    
*   Projection lag where materialised read models are used.
    

Avoid logging raw customer addresses, email addresses, payment details or unrestricted SQL parameters.

Use stable query names such as:

```text
Store.Queries.Orders.GetOrderDetails
Store.Queries.Checkout.GetCheckoutLookups
Store.Queries.Operations.SearchOrders
```

This makes it possible to identify the slow slice without depending on the exact SQL text.

## Testing the Query module

Unit tests provide limited confidence for query handlers whose behaviour mainly depends on SQL, mappings, views and indexes. Integration tests should run against the same database engine used in production. Migration tests should create the database from scratch and verify that every view compiles after all module migrations have run. Performance tests should use realistic row counts and data distribution. A million orders belonging evenly to customers behaves differently from a million orders where one large customer owns half of them.

## Query module rules

A small number of rules keep the design under control.

### Commands always use the owning module

The Query module never updates source data. It doesn’t reserve stock, approve payments, change customer addresses or alter shipments.

### Cross-module reads are purpose-built

A handler returns the response required by one use case. It doesn’t expose `IQueryable`, module DbContexts or generic table access.

### Authorisation is applied before data leaves the database

Customer, tenant and permission filters belong in the SQL or projection query. Sensitive rows aren’t retrieved and discarded later.

### Views and projections are contracts

Changes to published views and projection columns receive the same care as changes to an application API.

### Projections are disposable

A projection table is derived data. The application should be able to rebuild it from authoritative module data or retained events.

### Performance decides when to project

Start with a direct set-based query. Add a materialised read model when measurements justify the additional consistency and maintenance complexity.

## When module contract composition remains appropriate

A shared query isn’t always possible or desirable. Module contracts still make sense when data comes from an external provider, when obtaining the answer requires domain behaviour, or when the data is intentionally stored in a different system. For example, a live delivery estimate may require a request to a courier API owned by Shipping. A database join can retrieve the selected shipping method, but it cannot replace that live calculation. In those cases, the Query module can orchestrate the external result with database data. It should still batch or parallelise independent operations carefully and place timeouts around external calls. The Query module provides one place to perform the composition, but it cannot turn remote operations into a relational join.

## A practical adoption path

An existing modular monolith can introduce the Query module gradually. Begin with one expensive cross-module screen, such as order details or the fulfilment dashboard. Move its orchestration into one vertical query slice and replace sequential module calls with one set-based database operation. Add a read-only connection and database role. This prevents the new module from quietly gaining write access. Introduce published read views for modules whose schemas change frequently or whose internal columns shouldn’t be broadly visible.

Measure the new query under realistic load. Add indexes to the owning modules where the plan requires them. Create projection tables only for reads that remain expensive or need a substantially different shape. Over time, the architecture settles into a clear division:

![](https://cdn.hashnode.com/uploads/covers/67c36038c69a4b7143c5fc49/113153ce-a300-4ff1-8980-11be768579cc.png align="center")

Separate schemas give a modular monolith clear ownership boundaries, but they don’t require every read to become a chain of module calls. Commands should continue travelling through the owning module’s public contract. That is where invariants, authorisation and transactional behaviour belong. Cross-module reads can use a dedicated Query module that executes controlled, read-only SQL against several schemas. It can return purpose-built DTOs, combine lookup data in one round trip and use database views as published read contracts.

Where a direct join becomes too expensive, the same module can own denormalised projection tables maintained from module events. This approach preserves the benefits of vertical slices and CQRS without throwing away one of the main advantages of keeping the modules in a shared relational database. The boundaries remain strict where changes occur. The read path stays fast where the application needs combined data.

[Implementing reads and queries in a CQRS microservice](https://learn.microsoft.com/en-us/dotnet/architecture/microservices/microservice-ddd-cqrs-patterns/cqrs-microservice-reads)

[Designing the infrastructure persistence layer](https://learn.microsoft.com/en-us/dotnet/architecture/microservices/microservice-ddd-cqrs-patterns/infrastructure-persistence-layer-design)

[Applying CQRS and CQS approaches in a DDD microservice](https://learn.microsoft.com/en-us/dotnet/architecture/microservices/microservice-ddd-cqrs-patterns/eshoponcontainers-cqrs-ddd-microservice)

[CQRS pattern](https://learn.microsoft.com/en-us/azure/architecture/patterns/cqrs)

[Challenges and solutions for distributed data management](https://learn.microsoft.com/en-us/dotnet/architecture/microservices/architect-microservice-container-applications/distributed-data-management)

[Materialized View pattern](https://learn.microsoft.com/en-us/azure/architecture/patterns/materialized-view)

[EF Core keyless entity types](https://learn.microsoft.com/en-us/ef/core/modeling/keyless-entity-types)

[HybridCache library in ASP.NET Core](https://learn.microsoft.com/en-us/aspnet/core/performance/caching/hybrid?view=aspnetcore-10.0)

[CREATE VIEW for SQL Server](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql)

[GRANT object permissions for SQL Server](https://learn.microsoft.com/en-us/sql/t-sql/statements/grant-object-permissions-transact-sql)