Skip to content

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 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

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

-- 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

Sources

  • Services/SAIF.Services/SystemUsers/UserExtractService.cs (L34, L63)