Skip to content

SQL-202: Retrieve HearingLocation via ListOfHearingLocations (property)

This statement retrieves the active (non-expired), non-federal hearing locations used to populate the ListOfHearingLocations property in SubpoenaService. It is issued through NHibernate LINQ against the HearingLocation entity from Services/SAIF.Services/Legal/Subpoenas/SubpoenaService.cs:341.

Classification

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

Parameters

Parameters: None (literal). EXP_DATE is filtered IS NULL; HEARING_LOCATION_CODE is filtered <> 'FED'.

Tables

Table Access Columns used
HEARING_LOCATION SELECT HEARING_LOCATION_SEQ_NUM, LOCATION_NAME, ADDR1, ADDR2, ADDR3, CITY, STATE, ZIP, ZIP4, EFF_DATE, EXP_DATE, CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE, HEARING_LOCATION_CODE

LINQ statement

Source: Services/SAIF.Services/Legal/Subpoenas/SubpoenaService.cs:341

var listOfHearingLocations =
    Mapper.Map<IList<HearingLocation>>(_unitOfWork.GetRepository<LegalData.HearingLocation>()
        .Query()
        .Where(h => h.ExpirationDate == null && h.HearingLocationCode.Code != "FED")).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
SELECT HL.HEARING_LOCATION_SEQ_NUM,
       HL.LOCATION_NAME,
       HL.ADDR1,
       HL.ADDR2,
       HL.ADDR3,
       HL.CITY,
       HL.STATE,
       HL.ZIP,
       HL.ZIP4,
       HL.EFF_DATE,
       HL.EXP_DATE,
       HL.CREATE_USER,
       HL.CREATE_DATE,
       HL.UPDATE_USER,
       HL.UPDATE_DATE,
       HL.HEARING_LOCATION_CODE
FROM   HEARING_LOCATION HL
WHERE  HL.EXP_DATE IS NULL
  AND  HL.HEARING_LOCATION_CODE <> 'FED';

Translation notes: - Mapper.Map<IList<...>>(IQueryable) enumerates the query; OrderBy(FullLocationName).ThenBy(LocationName) at L345 runs in memory on the mapped contracts. - Depending on NHibernate version, != against a constant may be emitted as (col <> :p OR col IS NULL). - h.HearingLocationCode.Code is the referenced entity's identifier, so it short-circuits to the HEARING_LOCATION.HEARING_LOCATION_CODE FK column; the HEARING_LOCATION_CODE table is not joined.

Operations and processes

Sources

  • Services/SAIF.Services/Legal/Subpoenas/SubpoenaService.cs (L342)