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

# SQL-150: Retrieve CreditRepUser via GetCreditReps

This ORM statement performs a read operation on `CreditRepUser` via `IUnitOfWork.GetRepository<T>()`, executed from `Services/SAIF.Services/Collections/CollectionsService.cs (L1123)`.

## Classification

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

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `null` | Input | ** | N/A | `x.ExpirationDate == null` → column EXP_DATE |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [CREDIT_REP_USER](../database-tables.md#credit_rep_user) | SELECT | `USER_NAME`, `EFF_DATE`, `EXP_DATE`, `UPDATE_USER`, `UPDATE_DATE`, `UPDATE_NUM` |
| [ADP_EMPLOYEE](../database-tables.md#adp_employee)** | JOIN | `UserId`, `LastName` |

## LINQ statement

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

```csharp
// Services/SAIF.Services/Collections/CollectionsService.cs:1123-1127
            return Mapper.Map<CreditRepUser[]>(UnitOfWork.GetRepository<DataModel.CreditRepUser>()
                    .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
-- Services/SAIF.Services/Collections/CollectionsService.cs:1123
SELECT
  CRU.USER_NAME,
  CRU.EFF_DATE,
  CRU.EXP_DATE,
  CRU.UPDATE_USER,
  CRU.UPDATE_DATE,
  CRU.UPDATE_NUM
FROM CREDIT_REP_USER CRU
  INNER JOIN ADP_EMPLOYEE AE
    ON AE.UserId = CRU.USER_NAME -- unresolved mapping: Employee.UserId
WHERE CRU.EXP_DATE IS NULL
ORDER BY AE.LastName -- unresolved mapping: Employee.LastName
```

Translation notes:
- `Employee` is `HasOne(...).Constrained().PropertyRef("UserId")` (CreditRepUserMap.cs:19-23) - a one-to-one joined on `Employee.UserId` = the owner's identifier `USER_NAME`. `OrderBy(x => x.Employee.LastName)` needs a non-identifier column -> INNER JOIN the Employee table. `EmployeeMap` is external (SAIF.Framework.Data.NHibernate): Legends Q5 resolved table `ADP_EMPLOYEE` (identifier `EMPLID`) but not the `UserId` or `LastName` columns -> **unresolved mapping: Employee.UserId, Employee.LastName** (property names used in their place).
- Loading the `HasOne` association for each materialized row may issue additional Employee SELECTs (eager one-to-one) - not fabricated here.
- `Mapper.Map<CreditRepUser[]>(...)` wraps an already materialized `.ToList()`.

## Operations and processes
- CollectionsService.GetCreditReps — see [Operations and processes](../operations-processes.md)

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