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	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.

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

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

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

Sources

• Services/SAIF.Services/Agencies/AgencyService.cs (L470)