Skip to content

SQL-150: Retrieve CreditRepUser via GetCreditReps

This ORM statement performs a read operation on CreditRepUser via IUnitOfWork.GetRepository<T>(), executed from Services/SAIF.Services/Collections/CollectionsService.cs (L1123).

Classification

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

Parameters

Parameter Direction Type Max length Description
null Input ** N/A x.ExpirationDate == null → column EXP_DATE

Tables

Table Access Columns used
CREDIT_REP_USER SELECT USER_NAME, EFF_DATE, EXP_DATE, UPDATE_USER, UPDATE_DATE, UPDATE_NUM
ADP_EMPLOYEE** JOIN UserId, LastName

LINQ statement

Source: Services/SAIF.Services/Collections/CollectionsService.cs:1123

// Services/SAIF.Services/Collections/CollectionsService.cs:1123-1127
            return Mapper.Map<CreditRepUser[]>(UnitOfWork.GetRepository<DataModel.CreditRepUser>()
                    .Query()
                    .Where(x => x.ExpirationDate == null)
                    .OrderBy(x => x.Employee.LastName)
                    .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
-- Services/SAIF.Services/Collections/CollectionsService.cs:1123
SELECT
  CRU.USER_NAME,
  CRU.EFF_DATE,
  CRU.EXP_DATE,
  CRU.UPDATE_USER,
  CRU.UPDATE_DATE,
  CRU.UPDATE_NUM
FROM CREDIT_REP_USER CRU
  INNER JOIN ADP_EMPLOYEE AE
    ON AE.UserId = CRU.USER_NAME -- unresolved mapping: Employee.UserId
WHERE CRU.EXP_DATE IS NULL
ORDER BY AE.LastName -- unresolved mapping: Employee.LastName

Translation notes: - Employee is HasOne(...).Constrained().PropertyRef("UserId") (CreditRepUserMap.cs:19-23) - a one-to-one joined on Employee.UserId = the owner's identifier USER_NAME. OrderBy(x => x.Employee.LastName) needs a non-identifier column -> INNER JOIN the Employee table. EmployeeMap is external (SAIF.Framework.Data.NHibernate): Legends Q5 resolved table ADP_EMPLOYEE (identifier EMPLID) but not the UserId or LastName columns -> unresolved mapping: Employee.UserId, Employee.LastName (property names used in their place). - Loading the HasOne association for each materialized row may issue additional Employee SELECTs (eager one-to-one) - not fabricated here. - Mapper.Map<CreditRepUser[]>(...) wraps an already materialized .ToList().

Operations and processes

Sources

  • Services/SAIF.Services/Collections/CollectionsService.cs (L1123)