---
title: "SQL-301: Retrieve StateCode via GetStateCodes"
summary: Read operation against StateCode via NHibernate ORM.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-09-24
library: SAIF.Services
reference_type: package
---

# 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`

```csharp
// 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.

```sql
-- 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
- CertificateInformationService.GetStateCodes — see [Operations and processes](../operations-processes.md)

## Sources
- `Services/SAIF.Services/Policies/Certificates/Information/CertificateInformationService.cs (L463)`