Skip to main content

Notification Query Patterns

Summary​

Every query is inline LINQ against a DbSet. Read-only paths use no-tracking queries consistently; paths that intend to mutate deliberately do not.

Audience​

Engineers, architects, QA, support and DevOps engineers.

Reference Content​

The verified reference material for this topic is set out in the sections below.

Tracking discipline​

No-tracking is applied on the read paths: the idempotency existence check, template resolution, preference evaluation, the retry path's recipient lookup, and the single-message, attempts, failed-queue, processed-event, audit and template-list reads.

Tracking is intentionally retained where a mutation follows: the read-state lookup, the template update lookup, the recipient upsert lookup, and the retry path's message lookup. The distinction is applied correctly at every site reviewed.

Inbox​

A two-step query. First, recipient identifiers are resolved by tenant, external reference and optionally recipient type, projecting only the identifier. When that set is empty the handler returns an empty result without a second query. Otherwise messages are filtered to those identifiers and the in-app channel, optionally excluding rows already read, ordered by creation descending, and limited by a page size clamped to 1–500 with a default of 100.

The identifier set is materialised in memory and then used in a containment predicate, so a recipient with many rows produces a correspondingly large parameter list.

Templates​

Resolution runs up to two queries: the tenant-specific row filtered on tenant, key, channel and active, ordered by version descending, taking the first; then, only if that found nothing, the same query with a null tenant. Preferring a tenant override over the global default is therefore a query-ordering behavior, not a database construct.

The list endpoint applies four optional filters and orders by key, then channel, then version descending. Omitted filters are not applied at all, so an unfiltered call spans every tenant.

Preferences​

One query loads candidate rows for the tenant, recipient type, external reference and channel, then exact-versus-wildcard event matching is resolved in memory rather than in the database.

Statistics​

Eight independent count queries in one handler — see statistics storage.

Audit​

Three optional filters — event, correlation and message — applied only when supplied, ordered by occurrence descending, with a page size clamped to 1–1000 and a default of 200. An unfiltered call returns the most recent rows across all tenants and events.

Retry and diagnostics​

The retry path loads the message tracked, loads the recipient no-tracking, and falls back to the address stored on the message when no recipient row is found. The attempts query filters by message identifier and orders ascending by attempt number without confirming the message exists.

Failed notifications​

Filters messages on the failed status alone, orders by creation descending, clamps the page size to 1–500 with a default of 100. No tenant predicate and no index leading with status.

Paging​

Every collection query uses a clamped take with no skip, no continuation token and no total count. Callers cannot detect truncation, and there is no stable cursor for iterating beyond the first page.

Verified absence​

No raw SQL, no FromSqlRaw, no ExecuteUpdate, no ExecuteDelete, no stored procedure call, no compiled query, no split query and no explicit AsSplitQuery or Include anywhere in the reviewed source.

Classification​

Implemented.

Requires confirmation​

Whether cursor paging and tenant predicates on the administrative reads are planned requires confirmation.

See Also​

Keywords​

  • Notification database
  • Query Patterns
  • Draft database documentation

Source References​

  • microservices/src/notification-service/Api/NotificationEndpoints.cs
  • microservices/src/notification-service/Application/NotificationDispatcher.cs
  • microservices/src/notification-service/Application/TemplateEngine.cs
  • microservices/src/notification-service/Application/PreferenceEvaluator.cs
  • microservices/src/notification-service/Infrastructure/NotificationDbContext.cs

Revision Information​

  • Status: Draft
  • Last reviewed: 2026-07-21
  • Review cycle: Quarterly