SQL-081: Retrieve Agent¶

This ORM statement performs a read operation on Agent via IUnitOfWork.GetRepository<T>(), executed from Services/SAIF.Services/Policies/SecurityServices/PolicySecurityService.cs (L219, L368).

Classification¶

Field	Value
SQL ID	SQL-081
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	`AgencyBookList (property), GetAgencyIdForProducer`

Parameters¶

Parameter	Direction	Type	Max length	Description
`agencyId`	Input	**	N/A	`ag.Agency.Id == agencyId` → column join via `Agency` (agency_seq_num) → `Agency.Id`
`null`	Input	**	N/A	`ag.ExpirationDate == null` → column agent_exp_date
`DateTime.Now`	Input	**	N/A	`ag.ExpirationDate > …` → column agent_exp_date
`agentNumber`	Input	**	N/A	`agnt.Id == agentNumber` → column agent_num

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/Policies/SecurityServices/PolicySecurityService.cs:219

// Services/SAIF.Services/Policies/SecurityServices/PolicySecurityService.cs:219-221
                    var dataProducers = unitOfWork.GetRepository<Agent>()
                        .Query().Where(ag => ag.Agency.Id == agencyId &&
                                             (ag.ExpirationDate == null || ag.ExpirationDate > DateTime.Now)).ToList();

// Services/SAIF.Services/Policies/SecurityServices/PolicySecurityService.cs:368
            var dataAgent = unitOfWork.GetRepository<Agent>().Query().SingleOrDefault(agnt => agnt.Id == agentNumber);

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
-- Services/SAIF.Services/Policies/SecurityServices/PolicySecurityService.cs:219
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.agency_seq_num = :agencyId
  AND (A.agent_exp_date IS NULL OR A.agent_exp_date > :now)

-- Services/SAIF.Services/Policies/SecurityServices/PolicySecurityService.cs:368
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 = :agentNumber
-- NHibernate expects at most one row

Translation notes: - DateTime.Now (L221) -> bind :now; NHibernate may evaluate it client-side or translate it to a server date function depending on version.

Operations and processes¶

PolicySecurityService.AgencyBookList (property) — see Operations and processes
PolicySecurityService.GetAgencyIdForProducer — see Operations and processes

Sources¶

Services/SAIF.Services/Policies/SecurityServices/PolicySecurityService.cs (L219, L368)