SQL-181: Retrieve Employee¶

This statement retrieves a single Employee record with NHibernate LINQ FirstOrDefault, called five times from SubpoenaDocumentService — filtering by the current user's UserId (SetCurrentUserValues, and the secretary lookup at L641) or by subpoena.ApprovedByEmployeeId / subpoena.RequestedByEmployeeId (GenerateRecipientCashRequest, SetAttorneyInfo, and the secretary lookup at L646). 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-181
Operation type	Read
Execution method	ORM (NHibernate LINQ)
Database	Oracle
Schema	[unqualified]**
Command type	N/A (ORM-generated)
Business object	`SubpoenaDocumentService`
Criteria property	N/A — ORM
Lifecycle method	`GenerateRecipientCashRequest, SetAttorneyInfo, SetCurrentUserValues`

Parameters¶

Parameter	Direction	Type	Max length	Description
`:currentUserIdUpper`	Input	String	N/A	`_userProfileService.GetCurrentUser().UserId.ToUpper()`, client-side — filters `Employee.UserId` (unresolved mapping)
`:approvedByEmployeeId`	Input	String	N/A	`subpoena.ApprovedByEmployeeId` — filters `Employee.Id` (unresolved mapping)
`:requestedByEmployeeId`	Input	String	N/A	`subpoena.RequestedByEmployeeId` — filters `Employee.Id` (unresolved mapping)

Tables¶

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

LINQ statement¶

Source: Services/SAIF.Services/Legal/Subpoenas/SubpoenaDocumentService.cs:287, with further fragments at lines 394, 612, 641, and 646.

// Services/SAIF.Services/Legal/Subpoenas/SubpoenaDocumentService.cs:287
var currentUser = _unitOfWork.GetRepository<Employee>().Query()
    .FirstOrDefault(x => x.UserId == _userProfileService.GetCurrentUser().UserId.ToUpper());

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

// Services/SAIF.Services/Legal/Subpoenas/SubpoenaDocumentService.cs:612
var attorney = _unitOfWork.GetRepository<Employee>().Query()
    .FirstOrDefault(x => x.Id == subpoena.RequestedByEmployeeId);

// Services/SAIF.Services/Legal/Subpoenas/SubpoenaDocumentService.cs:641
secretary = _unitOfWork.GetRepository<Employee>().Query()
    .FirstOrDefault(x => x.UserId == _userProfileService.GetCurrentUser().UserId.ToUpper());

// Services/SAIF.Services/Legal/Subpoenas/SubpoenaDocumentService.cs:646
secretary = _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) L287 SetCurrentUserValues and (4) L641 — by current user id
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 = :currentUserIdUpper              -- unresolved mapping: Employee.UserId
) WHERE ROWNUM <= 1;

-- (2) L395 GenerateRecipientCashRequest and (5) L646 — by subpoena.ApprovedByEmployeeId
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;

-- (3) L612 SetAttorneyInfo — by subpoena.RequestedByEmployeeId
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 = :requestedByEmployeeId               -- 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. - FirstOrDefault uses the Oracle10 dialect wrapper SELECT * FROM (...) WHERE ROWNUM <= 1. - EmailAddress, Phone, PreferredName, FirstName, and LastName are read from the materialized entity in memory. - HEARING_LOCATION (L617) and AttorneyProfile (L615) are separate statements documented on their own pages (see SQL-199) and are not accessed by this statement.

Operations and processes¶

SubpoenaDocumentService.GenerateRecipientCashRequest — see Operations and processes
SubpoenaDocumentService.SetAttorneyInfo — see Operations and processes
SubpoenaDocumentService.SetCurrentUserValues — see Operations and processes

Sources¶

Services/SAIF.Services/Legal/Subpoenas/SubpoenaDocumentService.cs (L287, L395, L612, L641, L646)