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

# SQL-318: Retrieve SystemUserRef

This statement retrieves all `SYSTEM_USER_XREF` records, used by `UserExtractService` in two shapes: an unfiltered enumeration during `UpdateUserAccess`, and an ordered, materialized list during `ExtractUsers`. The SQL is inferred from the NHibernate LINQ queries and the `SystemUserRefMap` mapping shown below — it is not literal SQL text in the source code.

## Classification

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

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [SYSTEM_USER_XREF](../database-tables.md#system_user_xref) | SELECT | `SYSTEM_USER_XREF_SEQ_NUM`, `USER_NAME`, `EMPLOYEE_ID`, `LAST_EXTRACT_DATE`, `POLICY_CENTER_FLAG`, `BILLING_CENTER_FLAG`, `CONTACT_MANAGER_FLAG`, `CREATE_USER`, `CREATE_DATE`, `UPDATE_USER`, `UPDATE_DATE` |

## LINQ statement

Source: `Services/SAIF.Services/SystemUsers/UserExtractService.cs:34`

```csharp
// Services/SAIF.Services/SystemUsers/UserExtractService.cs:34
var userExtracts = _unitOfWork.GetRepository<SystemUserRef>().Query();
foreach (var userExtract in userExtracts)

// Services/SAIF.Services/SystemUsers/UserExtractService.cs:62
var userList =
    _unitOfWork.GetRepository<SystemUserRef>()
        .Query()
        .OrderBy(su => su.Id)
        .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
-- UserExtractService.UpdateUserAccess (line 34): enumerated by foreach, no predicate
SELECT
  SUX.SYSTEM_USER_XREF_SEQ_NUM,
  SUX.USER_NAME,
  SUX.EMPLOYEE_ID,
  SUX.LAST_EXTRACT_DATE,
  SUX.POLICY_CENTER_FLAG,
  SUX.BILLING_CENTER_FLAG,
  SUX.CONTACT_MANAGER_FLAG,
  SUX.CREATE_USER,
  SUX.CREATE_DATE,
  SUX.UPDATE_USER,
  SUX.UPDATE_DATE
FROM SYSTEM_USER_XREF SUX

-- UserExtractService.ExtractUsers (line 63): ordered, materialized by ToList()
SELECT
  SUX.SYSTEM_USER_XREF_SEQ_NUM,
  SUX.USER_NAME,
  SUX.EMPLOYEE_ID,
  SUX.LAST_EXTRACT_DATE,
  SUX.POLICY_CENTER_FLAG,
  SUX.BILLING_CENTER_FLAG,
  SUX.CONTACT_MANAGER_FLAG,
  SUX.CREATE_USER,
  SUX.CREATE_DATE,
  SUX.UPDATE_USER,
  SUX.UPDATE_DATE
FROM SYSTEM_USER_XREF SUX
ORDER BY SUX.SYSTEM_USER_XREF_SEQ_NUM
```

Translation notes:

- Two call sites with different shapes share this SQL ID; both are shown, each preceded by a label comment.
- In `UpdateUserAccess` the loop mutates tracked entities (lines 44-48) and `_unitOfWork.Commit()` (line 52) flushes them - the stateful session emits `UPDATE SYSTEM_USER_XREF` statements for changed rows. Those dirty-check updates are not fabricated here.
- `POLICY_CENTER_FLAG`, `BILLING_CENTER_FLAG`, `CONTACT_MANAGER_FLAG` are `YesNoType`.

## Operations and processes
- UserExtractService.ExtractUsers — see [Operations and processes](../operations-processes.md)
- UserExtractService.UpdateUserAccess — see [Operations and processes](../operations-processes.md)

## Sources
- `Services/SAIF.Services/SystemUsers/UserExtractService.cs (L34, L63)`