Skip to content

SQL-346: Retrieve WcisEmployee via ExtractUsers

This ORM statement performs a read operation, retrieving the first WcisEmployee row whose identifier matches a supplied user name, via IUnitOfWork.GetRepository<T>(), executed from Services/SAIF.Services/SystemUsers/UserExtractService.cs (L108). The SQL below is inferred from the LINQ expression and the WcisEmployee entity mapping. The entity's ClassMap<WcisEmployee> is supplied externally by the SAIF.Framework.Data.NHibernate package rather than by any file in this repository; only its backing table name (WCIS_EMPLOYEE) could be resolved via Legends, not its column mappings — columns and the join key below are shown using the C# property names pending resolution.

Classification

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

Parameters

Parameter Direction Type Max length Description
:UserName Input string N/A Captured member userExtract.UserName, compared to the WcisEmployee.Id identifier

Tables

Table Access Columns used
WCIS_EMPLOYEE** SELECT Id, FirstName, LastName, MiddleInitial, PhoneNumber, PhoneExtension, Title, Status

LINQ statement

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

// Services/SAIF.Services/SystemUsers/UserExtractService.cs:107
var wcisUser =
    _unitOfWork.GetRepository<WcisEmployee>()
        .Query()
        .FirstOrDefault(su => su.Id == 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
    WE.Id, -- unresolved mapping: WcisEmployee.Id
    WE.FirstName, -- unresolved mapping: WcisEmployee.FirstName
    WE.LastName, -- unresolved mapping: WcisEmployee.LastName
    WE.MiddleInitial, -- unresolved mapping: WcisEmployee.MiddleInitial
    WE.PhoneNumber, -- unresolved mapping: WcisEmployee.PhoneNumber
    WE.PhoneExtension, -- unresolved mapping: WcisEmployee.PhoneExtension
    WE.Title, -- unresolved mapping: WcisEmployee.Title
    WE.Status -- unresolved mapping: WcisEmployee.Status (many-to-one to EmployeeStatusCode; FK column unknown)
  FROM WCIS_EMPLOYEE WE
  WHERE WE.Id = :UserName -- unresolved mapping: WcisEmployee.Id
)
WHERE ROWNUM <= 1

Translation notes:

  • Unresolved mapping. Legends resolved the table (WCIS_EMPLOYEE, catalogued by SAIF.Framework.Data.NHibernate database-tables.md) and the mapping file name, but explicitly could not return column names ("the actual per-property column mappings are not shown"). Per the skill, C# property names are used in column positions with -- unresolved mapping markers; the table is marked **.
  • FirstOrDefault -> Oracle10 ROWNUM <= 1 wrapper.
  • The prior page listed no table; WCIS_EMPLOYEE** must be added.

Operations and processes

Sources

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