Skip to content

SQL-301: Retrieve StateCode via GetStateCodes

This statement retrieves StateCode rows that are either flagged as "other states" or equal to the Oregon code, ordered by description, via NHibernate LINQ executed from Services/SAIF.Services/Policies/Certificates/Information/CertificateInformationService.cs (L463). It backs GetStateCodes, which supplies the state selection list used on certificate-of-insurance forms. The StateCode entity maps to the ALL_STATES table, resolved through the Legends CLI because its ClassMap lives outside this repository (SAIF.Framework.Data.NHibernate); the column mappings themselves remain undocumented, so the SELECT list below cannot be reliably enumerated.

Classification

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

Parameters

Parameter Direction Type Max length Description
:true Input boolean N/A Boolean literal as rendered by the (unknown) mapped storage type for IsOtherStates
:OregonCode Input string N/A StateCode.OregonCode — constant not defined in this repository; its value is unresolved

Tables

Table Access Columns used
ALL_STATES** SELECT IsOtherStates -- unresolved mapping: StateCode.IsOtherStates, Code -- unresolved mapping: StateCode.Code, Description -- unresolved mapping: StateCode.Description (remaining columns also unresolved)

LINQ statement

Source: Services/SAIF.Services/Policies/Certificates/Information/CertificateInformationService.cs:463

// Services/SAIF.Services/Policies/Certificates/Information/CertificateInformationService.cs:463-465
var stateCodes = unitOfWork.GetRepository<Data.Model.Contacts.StateCode>().Query()
                    .Where(s => s.IsOtherStates || s.Code == StateCode.OregonCode);
return Mapper.Map<StateCode[]>(stateCodes.OrderBy(s => s.Description));

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
  /* column list not enumerable: StateCodeMap lives in SAIF.Framework.Data.NHibernate and its column mappings are not documented in Legends */
FROM ALL_STATES S
WHERE (S.IsOtherStates = :true -- unresolved mapping: StateCode.IsOtherStates (column name and boolean storage type unknown)
  OR S.Code = :OregonCode) -- unresolved mapping: StateCode.Code
ORDER BY S.Description -- unresolved mapping: StateCode.Description

Translation notes: - Unresolved mapping (columns). Table ALL_STATES confirmed via Legends; column names, the storage type of IsOtherStates, and the value of StateCode.OregonCode are external and not documented. Partial translation: FROM/WHERE structure/ORDER BY are reliable; column names and SELECT list cannot be reliably inferred. - L463 builds an unexecuted IQueryable; Mapper.Map<StateCode[]>(stateCodes.OrderBy(s => s.Description)) (L465) appends the ORDER BY and enumerates it.

Operations and processes

Sources

  • Services/SAIF.Services/Policies/Certificates/Information/CertificateInformationService.cs (L463)