Skip to content

SQL-182: Retrieve Employee

This statement retrieves a single Employee record by Id, called from SubpoenaLabelService.GetApproverEmailAddress (via Single, then reads EmailAddress in memory) and SubpoenaLabelService.InitializeLabel (via FirstOrDefault). The Employee entity's ClassMap lives in the external SAIF.Framework.Data.NHibernate package; the backing table resolves to ADP_EMPLOYEE, but its column mappings are unresolved beyond the RPT_MGR_EMPLID self-reference.

Classification

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

Parameters

Parameter Direction Type Max length Description
:approvedByEmployeeId Input String N/A approvedByEmployeeId method argument (statement 1) or subpoena.ApprovedByEmployeeId (statement 2) — filters Employee.Id (unresolved mapping)

Tables

Table Access Columns used
ADP_EMPLOYEE** SELECT full entity; filter Id — C# property names, column mapping unresolved

LINQ statement

Source: Services/SAIF.Services/Legal/Subpoenas/SubpoenaLabelService.cs:197, with a second fragment at line 347.

// Services/SAIF.Services/Legal/Subpoenas/SubpoenaLabelService.cs:197
return _unitOfWork.GetRepository<Employee>()
            .Query()
            .Single(x => x.Id == approvedByEmployeeId).EmailAddress;

// Services/SAIF.Services/Legal/Subpoenas/SubpoenaLabelService.cs:347
var requestUser =
    _unitOfWork.GetRepository<Employee>().Query().FirstOrDefault(x => (x.Id == subpoena.ApprovedByEmployeeId));

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) L197 — GetApproverEmailAddress
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 = :approvedByEmployeeId                -- unresolved mapping: Employee.Id
-- NHibernate expects at most one row

-- (2) L348 — InitializeLabel
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 = :approvedByEmployeeId                -- unresolved mapping: Employee.Id
) WHERE ROWNUM <= 1;

Translation notes: - ** = unresolved column mapping (C# property names shown). Table name ADP_EMPLOYEE is resolved from Legends documentation of SAIF.Framework.Data.NHibernate. - SingleOrDefault/Single add no row limit; NHibernate throws if more than one row is returned. - FirstOrDefault uses the Oracle10 dialect wrapper SELECT * FROM (...) WHERE ROWNUM <= 1. - .EmailAddress is read in memory after Single in statement (1). - HEARING_LOCATION (L346) is a separate statement documented on its own page (SQL-200) and is not accessed by this statement.

Operations and processes

Sources

  • Services/SAIF.Services/Legal/Subpoenas/SubpoenaLabelService.cs (L197, L348)