Instruction file imported from aribhw1892/EnterpriseThreadOS (.cursor/rules/ef-core-query-projection-ordering.mdc). Copyright stays with the author.

EF Core Query Projection Ordering

When writing EF Core queries for API list endpoints or frontend card data, order and filter on entity or anonymous-type fields before projecting into response DTOs.

Do not call OrderBy, ThenBy, Where, or similar operators on properties of a freshly constructed response record inside an IQueryable. PostgreSQL/Npgsql can fail at runtime with "could not be translated", even if in-memory tests pass.

Prefer this pattern:

return dbContext.PolicyVersions
    .Where(policy => policy.TenantId == tenantId)
    .Join(
        dbContext.ClassificationSchemeVersions,
        policy => policy.ClassificationSchemeVersionId,
        schemeVersion => schemeVersion.Id,
        (policy, schemeVersion) => new { policy, schemeVersion })
    .OrderByDescending(pair => pair.policy.CreatedAt)
    .Select(pair => new PolicyVersionResponse(/* fields */));

Avoid this pattern:

return QueryThatProjectsToResponse()
    .OrderByDescending(response => response.CreatedAt);

For complex list projections, materialize intentionally with ToListAsync before client-side ordering only when the result set is already safely bounded and tenant-filtered.