SQL-079: Retrieve Agent via ProducerQuery (property)

This ORM statement performs a read operation on Agent via ProducerQuery (property), executed from Services/SAIF.Services/Agencies/AgencyService.cs (L478-481).

Classification

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

Parameters

Parameter	Direction	Type	Max length	Description
:producerId	Input	long	N/A	Method argument (line 113)
:take	Input	int	N/A	Row limit from FirstOrDefault() (value 1)

Tables

Table	Access	Columns used
Agent	SELECT	agent_num, agent_name, agent_exp_date, agency_seq_num, mail_addr_loc_seq_num

LINQ statement

Source: Services/SAIF.Services/Agencies/AgencyService.cs:478-481.

// Services/SAIF.Services/Agencies/AgencyService.cs:478
private IQueryable<agenciesDataModel.Agent> ProducerQuery
{
    get { return unitOfWork.GetRepository<agenciesDataModel.Agent>().Query(); }
}

// Services/SAIF.Services/Agencies/AgencyService.cs:113
var producer = ProducerQuery.FirstOrDefault(a => a.Id == producerId);

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
-- ProducerQuery base (AgencyService.cs:480)
SELECT
  A.agent_num,
  A.agent_name,
  A.agent_exp_date,
  A.agency_seq_num,
  A.mail_addr_loc_seq_num
FROM Agent A

-- line 113: ProducerQuery.FirstOrDefault(a => a.Id == producerId)
SELECT *
FROM (
  SELECT
    A.agent_num,
    A.agent_name,
    A.agent_exp_date,
    A.agency_seq_num,
    A.mail_addr_loc_seq_num
  FROM Agent A
  WHERE A.agent_num = :producerId)
WHERE ROWNUM <= :take

Translation notes: - The cited line (480) is a property getter returning a bare Query(); the only composition in AgencyService.cs (line 113) is shown after the base statement. - Agency (agency_seq_num) and MailingLocation (mail_addr_loc_seq_num) references are selected as FK columns only; the Agency and LOCATION tables are not joined.

Operations and processes

• AgencyService.ProducerQuery (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 (L480)