Skip to content

SQL-317: Retrieve SystemUser via ExtractUsers

This statement retrieves a single SYSTEM_USER record whose SYSTEM_USER_ID matches the username being extracted, used by UserExtractService.ExtractUsers to look up an existing system user before processing an extract. The SQL is inferred from the NHibernate LINQ query and the SystemUserMap mapping shown below — it is not literal SQL text in the source code.

Classification

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

Parameters

Parameter Direction Type Max length Description
:UserName Input string N/A Captured member userExtract.UserName (SystemUserRef.UserName), compared to the identifier SystemUser.UserName, which maps to column SYSTEM_USER_ID.

Tables

Table Access Columns used
SYSTEM_USER SELECT SYSTEM_USER_ID, EMAIL_ADDRESS, FIRST_NAME, LAST_NAME, MIDDLE_NAME, POSITION_TITLE, PHONE_NUM, PHONE_EXTENSION_NUM, UPDATE_USER, UPDATE_DATE, UPDATE_NUM, SYSTEM_USER_TYPE_CODE

LINQ statement

Source: Services/SAIF.Services/SystemUsers/UserExtractService.cs:102

// Services/SAIF.Services/SystemUsers/UserExtractService.cs:101
var systemUser =
    _unitOfWork.GetRepository<SystemUser>()
        .Query()
        .FirstOrDefault(x => x.UserName == userExtract.UserName);

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 *
FROM (
  SELECT
    SU.SYSTEM_USER_ID,
    SU.EMAIL_ADDRESS,
    SU.FIRST_NAME,
    SU.LAST_NAME,
    SU.MIDDLE_NAME,
    SU.POSITION_TITLE,
    SU.PHONE_NUM,
    SU.PHONE_EXTENSION_NUM,
    SU.UPDATE_USER,
    SU.UPDATE_DATE,
    SU.UPDATE_NUM,
    SU.SYSTEM_USER_TYPE_CODE
  FROM SYSTEM_USER SU
  WHERE SU.SYSTEM_USER_ID = :UserName
)
WHERE ROWNUM <= 1

Translation notes:

  • FirstOrDefault -> Oracle10 ROWNUM <= 1 wrapper (dialect OracleDataClientConfiguration.Oracle10, resolved via Legends).
  • References(SystemUserType).Column("SYSTEM_USER_TYPE_CODE") contributes the FK column only; SYSTEM_USER_TYPE_CODE table is not joined.

Operations and processes

Sources

  • Services/SAIF.Services/SystemUsers/UserExtractService.cs (L102)