Skip to content

SQL-347: Retrieve WriteOffApprovalUser via GetWriteOffApprovalUsers

This ORM statement performs a read operation on WriteOffApprovalUser, listing rows with no expiration date, ordered by the associated employee's last name, via IUnitOfWork.GetRepository<T>(), executed from Services/SAIF.Services/Collections/CollectionsService.cs (L1751). The SQL below is inferred from the LINQ expression and the WriteOffApprovalUser NHibernate mapping (WRITEOFF_APPROVAL_USER). The order-by navigates through the Employee one-to-one to ADP_EMPLOYEE; that entity's mapping is supplied externally by the SAIF.Framework.Data.NHibernate package, and only its table name could be resolved via Legends, not the UserId/LastName column mappings — those are shown using the C# property names pending resolution.

Classification

Field Value
SQL ID SQL-347
Operation type Read
Execution method ORM (NHibernate LINQ)
Database Oracle
Schema [unqualified]**
Command type N/A (ORM-generated)
Business object WriteOffApprovalUser
Criteria property N/A — ORM
Lifecycle method GetWriteOffApprovalUsers

Tables

Table Access Columns used
WRITEOFF_APPROVAL_USER SELECT USER_NAME, EFF_DATE, EXP_DATE
ADP_EMPLOYEE** JOIN Employee.UserId (join key), Employee.LastName (sort key) — unresolved

LINQ statement

Source: Services/SAIF.Services/Collections/CollectionsService.cs:1751

// Services/SAIF.Services/Collections/CollectionsService.cs:1751
return Mapper.Map<WriteOffApprovalUser[]>(UnitOfWork.GetRepository<DataModel.WriteOffApprovalUser>()
    .Query().Where(x => x.ExpirationDate == null).OrderBy(x => x.Employee.LastName).ToList());

Sample SQL

Inferred SQL: This SQL was inferred from the LINQ statement above and the NHibernate mappings. It is not concrete SQL from source code; NHibernate generates the executed statement at runtime, so aliases, column order, join syntax, and bind-parameter names may differ.

-- Inferred from LINQ (NHibernate mapping translation); not concrete SQL
SELECT
  WAU.USER_NAME,
  WAU.EFF_DATE,
  WAU.EXP_DATE
FROM WRITEOFF_APPROVAL_USER WAU
  INNER JOIN ADP_EMPLOYEE AE
    ON AE.UserId = WAU.USER_NAME -- unresolved mapping: Employee.UserId (HasOne ... PropertyRef("UserId"))
WHERE WAU.EXP_DATE IS NULL
ORDER BY AE.LastName -- unresolved mapping: Employee.LastName

Translation notes:

  • OrderBy(x => x.Employee.LastName) reads a non-identifier property through the HasOne(...).PropertyRef("UserId") one-to-one, so ADP_EMPLOYEE is genuinely joined (skill rule x.Ref.Prop -> INNER JOIN). NHibernate may render an order-by-only navigation as a LEFT OUTER JOIN; the skill's INNER JOIN form is used.
  • Unresolved mapping for Employee.UserId and Employee.LastName: Legends confirmed table ADP_EMPLOYEE / EmployeeMap but stated the column mappings are not in the indexed docs. Table marked **.
  • The constrained one-to-one Employee may additionally be fetched per row by a separate statement when materialized; not fabricated.
  • The prior page listed only WRITEOFF_APPROVAL_USER; ADP_EMPLOYEE** (JOIN) must be added.
  • CollectionsMap.cs duplicate WriteOffApprovalUserMap is not compiled; Collections/WriteOffApprovalUserMap.cs (csproj line 472) is authoritative.

Operations and processes

Sources

  • Services/SAIF.Services/Collections/CollectionsService.cs (L1751)