SQL-178: Retrieve Employee via HasSecurityOverride

This statement looks up the first Employee row matching an upper-cased user ID, which HasSecurityOverride then checks against a separate claim-security-override provider call. 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-178
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	HasSecurityOverride

Parameters

Parameter	Direction	Type	Max length	Description
:userIdUpper	Input	string	N/A	= userId.ToUpper(), client-side → column UserId (unresolved)

Tables

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

LINQ statement

Source: Services/SAIF.Services/Claims/Information/ClaimInformationService.cs:118

var employee = _uow.GetRepository<Employee>().Query()
  .FirstOrDefault(x => x.UserId == 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
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. - FirstOrDefault → Oracle10 dialect wraps the query in SELECT * FROM (...) WHERE ROWNUM <= 1. - HasClaimSecurityOverride at L122 is a separate provider query (not this statement).

Operations and processes

• ClaimInformationService.HasSecurityOverride — see Operations and processes

Sources

• Services/SAIF.Services/Claims/Information/ClaimInformationService.cs (L118)