Skip to content

SQL-179: Retrieve Employee

This ORM statement performs a read lookup on Employee via IUnitOfWork.GetRepository<T>(), executed twice from Services/SAIF.Services/Claims/SecurityOverride/SecurityOverrideService.cs — once keyed on Id (L65, SetAdditionalProperties) and once keyed on UserId (L103, CreateOverride). The Employee entity's ClassMap lives in the external SAIF.Framework.Data.NHibernate package; Legends resolved only the table name (ADP_EMPLOYEE) and the RPT_MGR_EMPLID self-reference, so the remaining columns used here are shown as C# property names.

Classification

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

Parameters

Parameter Direction Type Max length Description
:entryUserId Input Guid/string N/A = entry.UserId, used in x.Id == entry.UserId (L65)
:userIdUpper Input string N/A = securityOverrideEdit.UserId.ToUpper(), used in x.UserId == ... (L103)

Tables

Table Access Columns used
ADP_EMPLOYEE** SELECT Full entity; filter columns Id, UserId (C# property names — column mapping unresolved)

LINQ statement

Source: Services/SAIF.Services/Claims/SecurityOverride/SecurityOverrideService.cs:65. A second, separate statement is issued from the same file at L103.

// Services/SAIF.Services/Claims/SecurityOverride/SecurityOverrideService.cs:65
var employeeData = unitOfWork.GetRepository<Data.Model.Employees.Employee>().Query().FirstOrDefault(x => x.Id == entry.UserId);

// Services/SAIF.Services/Claims/SecurityOverride/SecurityOverrideService.cs:103
var employeeData = unitOfWork.GetRepository<Data.Model.Employees.Employee>().Query().FirstOrDefault(x => x.UserId == securityOverrideEdit.UserId.ToUpper());

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
-- (1) L65 — SetAdditionalProperties
SELECT * FROM (
    SELECT E.Id, E.UserId, E.EmployeeStatusCode, E.FirstName, E.LastName, E.PreferredName,
           E.EmailAddress, E.Phone, E.Department, E.JobTitle /* , ...remaining EmployeeMap columns */
           -- unresolved mapping: Employee.* (C# property names; physical columns not resolvable)
    FROM   ADP_EMPLOYEE E
    WHERE  E.Id = :entryUserId                        -- unresolved mapping: Employee.Id
) WHERE ROWNUM <= 1;

-- (2) L103 — CreateOverride
SELECT * FROM (
    SELECT E.Id, E.UserId, E.EmployeeStatusCode, E.FirstName, E.LastName, E.PreferredName,
           E.EmailAddress, E.Phone, E.Department, E.JobTitle /* , ...remaining EmployeeMap columns */
           -- unresolved mapping: Employee.* (C# property names; physical columns not resolvable)
    FROM   ADP_EMPLOYEE E
    WHERE  E.UserId = :userIdUpper                    -- unresolved mapping: Employee.UserId
) WHERE ROWNUM <= 1;

Translation notes: - ** = unresolved column mapping (C# property names shown). Table name ADP_EMPLOYEE is from Legends documentation of SAIF.Framework.Data.NHibernate; no ClassMap<Employee> exists in this repo. - FirstOrDefault → Oracle10 dialect wraps each query in SELECT * FROM (...) WHERE ROWNUM <= 1.

Operations and processes

Sources

  • Services/SAIF.Services/Claims/SecurityOverride/SecurityOverrideService.cs (L65, L103)