SQL-175: Retrieve Employee via GetApproverMailId

This statement looks up a single Employee row by ID to read its UserId, used as the mail ID of a legal bill's approver. When the recorded approver is the outside-counsel placeholder, the manager's employee ID (resolved separately via _legalQueryProvider.GetAttorneyProfile) is used instead of the recorded approver ID. The Employee entity's ClassMap lives in the external SAIF.Framework.Data.NHibernate package; Legends resolved only the table name (ADP_EMPLOYEE), so the columns used here are shown as C# property names.

Classification

Field	Value
SQL ID	SQL-175
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	GetApproverMailId

Parameters

Parameter	Direction	Type	Max length	Description
:managerEmployeeId	Input	string	N/A	Branch A: x.Id == _legalQueryProvider.GetAttorneyProfile(approvedByEmployeeId).ManagerEmployeeId → column Id (unresolved)
:approvedByEmployeeId	Input	string	N/A	Branch B: x.Id == approvedByEmployeeId → column Id (unresolved)

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/Bills/BillPaymentService.cs:476

return
    approvedByEmployeeId == "OUTSIDECNCL"
        ? _unitOfWork.GetRepository<Employee>()
            .Query()
            .Single(
                x =>
                    x.Id ==
                    _legalQueryProvider.GetAttorneyProfile(approvedByEmployeeId).ManagerEmployeeId)
            .UserId
        : _unitOfWork.GetRepository<Employee>()
            .Query()
            .Single(x => x.Id == approvedByEmployeeId).UserId;

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
-- Branch A (approvedByEmployeeId == "OUTSIDECNCL"): manager id resolved first by
--   _legalQueryProvider.GetAttorneyProfile(approvedByEmployeeId).ManagerEmployeeId (separate query, client-side capture)
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 = :managerEmployeeId                         -- unresolved mapping: Employee.Id
-- NHibernate expects at most one row

-- Branch B (otherwise)
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

Translation notes: - ** = unresolved column mapping (C# property names shown). Table name ADP_EMPLOYEE is from Legends documentation of SAIF.Framework.Data.NHibernate. - SingleOrDefault/Single add no row limit; NHibernate throws if more than one row is returned. Single (not SingleOrDefault) throws if no row. - .UserId is read from the materialized entity in memory. - The captured GetAttorneyProfile(...) call is evaluated client-side before the query is sent; its own SQL belongs to the legal query provider, not this statement.

Operations and processes

• BillPaymentService.GetApproverMailId — see Operations and processes

Sources

• Services/SAIF.Services/Legal/Bills/BillPaymentService.cs (L478, L485)