Asset Query Patterns

Summary

Source-backed inventory of the EF Core read patterns the Asset Service uses — how each query filters by tenant, what it eager-loads, how it orders and caps results, what it computes in memory, and the indexing and pagination gaps these patterns expose.

Audience

Backend engineers, database reviewers, architects, QA, support, operations, security reviewers, and product owners.

Overview

Asset reads are served by application queries (AssetQueries), the compatibility service (AssetCompatService), and the repository (Domain/Repositories.cs). All tenant-scoped reads inherit the DbContext global query filter; results are loaded as full tracked entities and mapped to DTOs in memory. There is no DB-side projection and no offset/keyset pagination — reads are bounded only by Take caps.

Confirmed persistence behavior

Verified query patterns

Query	Tenant filter	Includes / eager load	Ordering	Filtering	Bounding	In-memory processing
Asset list (ListAsync)	DbContext filter	None	OrderByDescending(CreatedOnUtc)	Optional status / type / assignedToUserId / laborId Where	Take(1000) cap	Load entities, map to DTO
Detail (GetAsync)	DbContext filter	Include History + Documents	—	FirstOrDefault by Id	Single row	Map to DTO
History (GetHistoryAsync)	DbContext filter	Include History	Ordered by OccurredOnUtc in memory	By asset	Bounded by asset	Ordering done client-side
Open assets (GetOpenAssetsAsync)	DbContext filter	—	OrderBy(AssignedOnUtc)	Assignee.LaborId == laborId AND Status in BlockingStatuses (Issued, Damaged, Lost, Repair)	Not capped	Load then map
Clearance (GetClearanceStatusAsync)	DbContext filter	—	—	Reuses open-assets filter	—	CanClear = open.Count == 0 (client-derived)
Code existence (CodeExistsAsync)	DbContext filter	—	—	AnyAsync(x => x.Code.Value == code)	Boolean	None (existence only)
Compat workspace (GetWorkspaceAsync)	DbContext filter	—	OrderBy(Code.Value)	Where(Assignee.UserId != null)	Take(500) cap	Other workspace sections returned empty
Compat detail	DbContext filter	Include History + Documents	—	GetByCompatIdAsync	Single row	Map to DTO
Outbox diagnostics	Not filtered (messaging)	—	Store-defined	By status	Store batch	Status counts
Document-reference reads	DbContext filter	Loaded via aggregate children	—	Over loaded children	Bounded by aggregate	In-memory over loaded collection

Cross-cutting characteristics verified in source:

• Tenant filtering: every domain read inherits the global query filter (IsSuperAdmin || TenantId == CurrentTenantId). The outbox diagnostics read is unfiltered because the outbox set is messaging infrastructure.
• No-tracking: not used. Reads are tracked entity loads, not AsNoTracking projections.
• Projection: none at the database. Queries load full entities and map to DTOs in application code.
• Include / eager loading: detail, history, and compat-detail reads Include the History and Document children; list and open-asset reads do not.
• Ordering: list orders by CreatedOnUtc descending at the database; history orders by OccurredOnUtc in memory; open assets order by AssignedOnUtc; compat workspace orders by code.
• Filtering: list filters are optional predicates; open-assets/clearance filter on the owned Assignee.LaborId plus a status set; code existence uses AnyAsync on the owned code value.
• Pagination: none. Take(1000) (list) and Take(500) (compat workspace) are caps, not paged windows; open assets and history are not capped beyond their natural scope.
• Aggregation: outbox diagnostics count by status; no other query aggregates at the database.
• In-memory processing: DTO mapping, history ordering, and the clearance boolean are all computed client-side after load.

Gaps identified (evidence-based)

• Unpaginated reads: the list (Take(1000)) and compat workspace (Take(500)) caps silently truncate rather than page; there is no offset or keyset continuation.
• Client-derived metric: CanClear is computed in memory from the loaded open-asset count, not by a database query.
• Full collection loading: detail and compat-detail reads eager-load entire History and Documents collections with no sub-limit.
• Missing DB projection: every read materializes full tracked entities before mapping; there is no server-side projection to the shape returned.
• Missing query-specific indexes: the indexes present are CompatId (unique), AssetCode (non-unique), TenantId+Status, TenantId+AssetType, and WorkflowInstance. There is no assignee-only index (owned Assignee.UserId/LaborId filters are unindexed) and no status-only index (a status filter uses the composite TenantId+Status).

These are structural observations from the source; no runtime performance measurements are claimed.

Classification

Implemented tenant-scoped read patterns with capped, in-memory-mapped results; pagination and DB projection are Transitional gaps.

Requires confirmation

Whether the Take caps meet expected data volumes, and whether assignee-scoped or status-scoped queries need dedicated indexes, require confirmation against production data profiles.

Related Articles

• Asset database documentation
• DbContext and Object Sets
• Schema Reference

See Also

• Asset Storage
• Persistence Architecture
• Data Integrity

Keywords

• Asset persistence
• Query Patterns
• Draft database documentation

Source References

• microservices/src/asset-service/Application/Queries/AssetQueries.cs
• microservices/src/asset-service/Application/Compatibility/AssetCompatService.cs
• microservices/src/asset-service/Domain/Repositories.cs
• microservices/src/asset-service/Infrastructure/AssetDbContext.cs

Revision Information

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