Skip to content

SQL-083: Retrieve AttorneyProfile via GetAttorneysWithPendingLegalBills

This ORM statement performs a read operation on AttorneyProfile via IUnitOfWork.GetRepository<T>(), executed from Services/SAIF.Services/Legal/Bills/BillPaymentService.cs (L140).

Classification

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

Parameters

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

Tables

Table Access Columns used
ATTORNEY_PROFILE SELECT PROFILE_EMPLID, SECRETARY_EMPLID, MANAGER_EMPLID, OREGON_STATE_BAR_NUMBER, EFF_DATE, EXP_DATE, CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE

LINQ statement

Source: Services/SAIF.Services/Legal/Bills/BillPaymentService.cs:140

// Services/SAIF.Services/Legal/Bills/BillPaymentService.cs:140-141
            var attorneys = Mapper.Map<IList<AttorneyProfile>>(_unitOfWork.GetRepository<AttorneyProfile>().Query()
                .Where(x => (x.ExpirationDate == null || x.ExpirationDate > DateTime.Today))).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/Legal/Bills/BillPaymentService.cs:140
SELECT
  AP.PROFILE_EMPLID,
  AP.SECRETARY_EMPLID,
  AP.MANAGER_EMPLID,
  AP.OREGON_STATE_BAR_NUMBER,
  AP.EFF_DATE,
  AP.EXP_DATE,
  AP.CREATE_USER,
  AP.CREATE_DATE,
  AP.UPDATE_USER,
  AP.UPDATE_DATE
FROM ATTORNEY_PROFILE AP
WHERE (AP.EXP_DATE IS NULL OR AP.EXP_DATE > :today)

Translation notes: - Mapper.Map<IList<AttorneyProfile>>(...) enumerates the IQueryable (materialization point); the trailing .ToList() runs in memory. - DateTime.Today -> bind :today; may be evaluated client-side depending on NHibernate version. - PROFILE_EMPLID is mapped both as identifier and as the AttorneyEmployee FK -> selected once.

Operations and processes

Sources

  • Services/SAIF.Services/Legal/Bills/BillPaymentService.cs (L140)