SQL-078: Retrieve AgencyPaperless via AgencyPaperlessQuery (property)

This ORM statement performs a read operation on AgencyPaperless via AgencyPaperlessQuery (property), executed from Services/SAIF.Services/Agencies/AgencyService.cs (L473-476).

Classification

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

Parameters

Parameter	Direction	Type	Max length	Description
:agencyId	Input	long	N/A	Method argument (lines 281, 307)
:take	Input	int	N/A	Row limit from FirstOrDefault() (value 1)

Tables

Table	Access	Columns used
AGENCY_PAPERLESS_UPDATE_VIEW	SELECT	agency_seq_num, policy_holder_paperless_flag, agency_paperless_flag, agency_paperless_request_flag, update_date, update_user

LINQ statement

Source: Services/SAIF.Services/Agencies/AgencyService.cs:473-476.

// Services/SAIF.Services/Agencies/AgencyService.cs:473
private IQueryable<agenciesDataModel.AgencyPaperless> AgencyPaperlessQuery
{
    get { return unitOfWork.GetRepository<agenciesDataModel.AgencyPaperless>().Query(); }
}

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

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

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
-- AgencyPaperlessQuery base (AgencyService.cs:475)
SELECT
  APUV.agency_seq_num,
  APUV.policy_holder_paperless_flag,
  APUV.agency_paperless_flag,
  APUV.agency_paperless_request_flag,
  APUV.update_date,
  APUV.update_user
FROM AGENCY_PAPERLESS_UPDATE_VIEW APUV

-- line 281 / line 307: AgencyPaperlessQuery.FirstOrDefault(a => a.Id == agencyId)
SELECT *
FROM (
  SELECT
    APUV.agency_seq_num,
    APUV.policy_holder_paperless_flag,
    APUV.agency_paperless_flag,
    APUV.agency_paperless_request_flag,
    APUV.update_date,
    APUV.update_user
  FROM AGENCY_PAPERLESS_UPDATE_VIEW APUV
  WHERE APUV.agency_seq_num = :agencyId)
WHERE ROWNUM <= :take

Translation notes: - The cited line (475) is a property getter returning a bare Query(); the compositions found in AgencyService.cs (lines 281 and 307) are shown after the base statement. - AGENCY_PAPERLESS_UPDATE_VIEW is a database view per its name; the mapping treats it as a table. The underlying base table is not visible from the mapping and is not listed.

Operations and processes

• AgencyService.AgencyPaperlessQuery (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 (L475)