---
title: "SQL-347: Retrieve WriteOffApprovalUser via GetWriteOffApprovalUsers"
summary: Read operation against WriteOffApprovalUser via NHibernate ORM.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-09-24
library: SAIF.Services
reference_type: package
---

# 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](../database-tables.md#writeoff_approval_user) | SELECT | `USER_NAME`, `EFF_DATE`, `EXP_DATE` |
| [ADP_EMPLOYEE**](../database-tables.md#adp_employee) | JOIN | `Employee.UserId` (join key), `Employee.LastName` (sort key) — unresolved |

## LINQ statement

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

```csharp
// 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.

```sql
-- 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
- CollectionsService.GetWriteOffApprovalUsers — see [Operations and processes](../operations-processes.md)

## Sources
- `Services/SAIF.Services/Collections/CollectionsService.cs (L1751)`