---
title: "SQL-075: Retrieve Agency via AgencyQuery (property)"
summary: Read operation against Agency via NHibernate ORM.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-09-24
library: SAIF.Services
reference_type: package
---

# SQL-075: Retrieve Agency via AgencyQuery (property)

This ORM statement performs a read operation on `Agency` via `AgencyQuery (property)`, executed from `Services/SAIF.Services/Agencies/AgencyService.cs` (L468-471).

## Classification

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

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:agencyId` | Input | `long` | N/A | Method argument (lines 68, 208) |
| `:take` | Input | `int` | N/A | Row limit from `FirstOrDefault()` (value 1) |
| `:searchTerm` | Input | `string` | N/A | Method argument of `GetAgencySearchResults` (line 451) |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [Agency](../database-tables.md#agency) | SELECT | agency_seq_num, agency_name, agency_eff_date, agency_exp_date, policy_holder_paperless_flag, agency_paperless_flag, agency_paperless_request_flag, update_date, update_user, agency_rep_user |

## LINQ statement

Source: `Services/SAIF.Services/Agencies/AgencyService.cs:468-471`.

```csharp
// Services/SAIF.Services/Agencies/AgencyService.cs:468
private IQueryable<agenciesDataModel.Agency> AgencyQuery
{
    get { return unitOfWork.GetRepository<agenciesDataModel.Agency>().Query(); }
}

// Services/SAIF.Services/Agencies/AgencyService.cs:63
return Mapper.Map<AgencySearchResult[]>(AgencyQuery.ToList());

// Services/SAIF.Services/Agencies/AgencyService.cs:68
var agency = AgencyQuery.FirstOrDefault(a => a.Id == agencyId);

// Services/SAIF.Services/Agencies/AgencyService.cs:90
var agencies = AgencyQuery.Where(x => x.IsPaperlessForAgencyCopyOfDocuments);
agencyIds.AddRange(from agency in agencies
                   select agency.Id);

// Services/SAIF.Services/Agencies/AgencyService.cs:208
var agency = AgencyQuery.FirstOrDefault(a => a.Id == agencyId);

// Services/SAIF.Services/Agencies/AgencyService.cs:451
IEnumerable<agenciesDataModel.Agency> agencies = AgencyQuery
    .Where(a => a != null && (a.Id.ToString() == searchTerm || a.Name.ToLower().Contains(searchTerm.ToLower())))
    .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.

```sql
-- Inferred from LINQ (NHibernate mapping translation); not concrete SQL
-- AgencyQuery base (AgencyService.cs:470); GetAgencySearchResults-all (line 63) executes it unchanged
SELECT
  A.agency_seq_num,
  A.agency_name,
  A.agency_eff_date,
  A.agency_exp_date,
  A.policy_holder_paperless_flag,
  A.agency_paperless_flag,
  A.agency_paperless_request_flag,
  A.update_date,
  A.update_user,
  A.agency_rep_user
FROM Agency A

-- line 68 / line 208: AgencyQuery.FirstOrDefault(a => a.Id == agencyId)
SELECT *
FROM (
  SELECT
    A.agency_seq_num,
    A.agency_name,
    A.agency_eff_date,
    A.agency_exp_date,
    A.policy_holder_paperless_flag,
    A.agency_paperless_flag,
    A.agency_paperless_request_flag,
    A.update_date,
    A.update_user,
    A.agency_rep_user
  FROM Agency A
  WHERE A.agency_seq_num = :agencyId)
WHERE ROWNUM <= :take

-- line 90-92: AgencyQuery.Where(x => x.IsPaperlessForAgencyCopyOfDocuments), projected to agency.Id
SELECT
  A.agency_seq_num
FROM Agency A
WHERE A.agency_paperless_flag = 'Y'

-- line 451-453: GetAgencySearchResults(searchTerm)
SELECT
  A.agency_seq_num,
  A.agency_name,
  A.agency_eff_date,
  A.agency_exp_date,
  A.policy_holder_paperless_flag,
  A.agency_paperless_flag,
  A.agency_paperless_request_flag,
  A.update_date,
  A.update_user,
  A.agency_rep_user
FROM Agency A
WHERE A.agency_seq_num IS NOT NULL
  AND (TO_CHAR(A.agency_seq_num) = :searchTerm
    OR LOWER(A.agency_name) LIKE '%' || LOWER(:searchTerm) || '%')
```

Translation notes:
- The cited line (470) is a property getter that returns a bare `Query()`; no SQL runs until a consumer composes and enumerates it. The base statement is shown first, followed by each composition found in `AgencyService.cs` (multi-call-site rule), each labelled with its line.
- `IsPaperlessForAgencyCopyOfDocuments` is `YesNoType` → `= 'Y'`. At line 91 `from agency in agencies select agency.Id` is still an `IQueryable` projection, so only `agency_seq_num` is selected.
- Line 452: `a != null` on the root entity translates to an identifier `IS NOT NULL` test; `a.Id.ToString()` → `TO_CHAR(...)` (NHibernate Oracle dialect `str()` → character cast; exact function is dialect-dependent). The subsequent `OrderBy`/`ThenBy`/`Take(maxHits)` (lines 460-466) run in memory after `.ToList()` at line 453 and `Mapper.Map`.
- `SAIFRepresentative` (`References` → `agency_rep_user`) is selected as an FK only; the `WcisEmployee` table is not joined. `HasMany` collections (`Contracts`, `Agents`, `TeamFunctions`, `DocumentNotifications`) are lazy and not selected.

## Operations and processes
- AgencyService.AgencyQuery (property) — see [Operations and processes](../operations-processes.md)

> **Note:** Statements marked with `**` could not be confirmed; treat as unknown until verified.

## Sources
- `Services/SAIF.Services/Agencies/AgencyService.cs (L470)`