SQL-176: Retrieve Employee

This statement reads Employee rows three ways: GetPendingBills looks up the active employee matching a user ID (or the current user) so a pending legal bill can be attributed to them, and LegalSupportEmployeeList lists legal-support-department employees (plus development-team overrides outside production) as a projection ordered by last name. 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-176
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	GetPendingBills, LegalSupportEmployeeList

Parameters

Parameter	Direction	Type	Max length	Description
:userId	Input	string	N/A	x.UserId == userId → column UserId (unresolved)
:currentUser	Input	string	N/A	x.UserId == _currentUser → column UserId (unresolved)
:isProd	Input	bool	N/A	_environmentService.IsProduction, captured
:developmentTeam	Input	collection	N/A	captured collection, expanded to an IN list against UPPER(UserId)

Tables

Table	Access	Columns used
ADP_EMPLOYEE**	SELECT	UserId, EmployeeStatusCode, Department, JobTitle, Id, LastName, FirstName, PreferredName (+ full entity in statements 1/2) (C# property names — column mapping unresolved)

LINQ statement

Source: Services/SAIF.Services/Legal/Bills/BillService.cs:138. Additional fragments at :167, :312.

// Services/SAIF.Services/Legal/Bills/BillService.cs:138
var employee = _unitOfWork.GetRepository<EmployeeData.Employee>()
    .Query().FirstOrDefault(x => x.UserId == userId && x.EmployeeStatusCode == "A");

// Services/SAIF.Services/Legal/Bills/BillService.cs:167
var currentEmployee = _unitOfWork.GetRepository<EmployeeData.Employee>()
    .Query().FirstOrDefault(x => x.UserId == _currentUser);

// Services/SAIF.Services/Legal/Bills/BillService.cs:312
return _unitOfWork.GetRepository<EmployeeData.Employee>().Query()
    .Where(x => (x.Department == "005201"
                    && x.EmployeeStatusCode == "A"
                    && x.JobTitle.StartsWith("Legal "))
                || (
                    !isProd && (_developmentTeam.Contains(x.UserId.ToUpper()))
                   )
    )
    .OrderBy(x => x.LastName)
    .Select(
        x =>
            new LegalEmployee
            {
                Id = x.Id,
                UserId = x.UserId,
                Name =
                    x.LastName + ", " +
                    (x.PreferredName.IsNullOrWhiteSpace() ? x.FirstName : x.PreferredName),
                IsSelectedUser = (_currentUser == x.UserId ? true : false)
            }).ToList();

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) BillService.cs:138 — GetPendingBills
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 = :userId AND E.EmployeeStatusCode = 'A'   -- unresolved mapping: Employee.UserId, Employee.EmployeeStatusCode
) WHERE ROWNUM <= 1;

-- (2) BillService.cs:167 — GetPendingBills (current user)
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 = :currentUser                     -- unresolved mapping: Employee.UserId
) WHERE ROWNUM <= 1;

-- (3) BillService.cs:312 — LegalSupportEmployeeList (projection; see notes)
SELECT E.Id, E.UserId, E.LastName, E.PreferredName, E.FirstName   -- unresolved mapping: Employee.*
FROM   ADP_EMPLOYEE E
WHERE  (E.Department = '005201'                                   -- unresolved mapping: Employee.Department
        AND E.EmployeeStatusCode = 'A'
        AND E.JobTitle LIKE 'Legal %')                            -- unresolved mapping: Employee.JobTitle
   OR  (:isProd = 0 AND UPPER(E.UserId) IN (:developmentTeam))
ORDER BY E.LastName;

Translation notes: - ** = unresolved column mapping (C# property names shown). Table name ADP_EMPLOYEE is from Legends documentation of SAIF.Framework.Data.NHibernate. - FirstOrDefault → Oracle10 dialect wraps the query in SELECT * FROM (...) WHERE ROWNUM <= 1. - (3) StartsWith("Legal ") → LIKE 'Legal %'. The final Select uses the IsNullOrWhiteSpace() extension and a captured comparison, which NHibernate cannot translate; it evaluates the projection client-side, so SQL selects the referenced columns only and Name/IsSelectedUser are computed in memory. - !isProd && ... — the captured boolean is sent as a parameter; NHibernate may simplify the predicate when the parameter is constant-folded, so the exact form may differ. - The LegalBill query between (1) and (2) (SQL-219) is a separate statement against LEGAL_BILL and is not part of this page.

Operations and processes

• BillService.GetPendingBills — see Operations and processes
• BillService.LegalSupportEmployeeList — see Operations and processes

Business rules

• SVC-BR-LGL-013

Sources

• Services/SAIF.Services/Legal/Bills/BillService.cs (L138, L167, L312)