SQL-001: AddressVerificationQueryProvider.GetLocationVerificationRequests¶

This statement is a read operation invoked through ORM (NHibernate LINQ). Executed by AddressVerificationQueryProvider.GetLocationVerificationRequests.

Classification¶

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

Parameters¶

Parameter	Direction	Type	Max length	Description
`locationsSeqNum`	Input	long	N/A	Method argument of `AddressVerificationQueryProvider.GetLocationVerificationRequests` bound into the ORM expression; NHibernate generates the bind variable at runtime

Tables¶

Table	Access	Columns used
LOCATION_VERIFICATION_REQUEST	SELECT	LOC_SEQ_NUM, ADDR_VERIFICATION_REQ_SEQ_NUM, REQUEST_DATE, ADDRESS_TABLE_CODE, UPDATE_NUM, CANCEL_DATE, UPDATE_DATE, UPDATE_USER
ADDRESS_VERIFICATION_REQUEST	JOIN	ADDR_VERIFICATION_REQ_SEQ_NUM, VERIFIED_ADDRESS_SEQ_NUM

LINQ statement¶

Source: Data/SAIF.Data/Queries/AddressVerification/AddressVerificationQueryProvider.cs:34. The repository is a class field initialised from Factory.GetRepository<LocationVerificationRequest>().Query() at Data/SAIF.Data/Queries/AddressVerification/AddressVerificationQueryProvider.cs:18.

return locationVerificationRepository.SingleOrDefault(x => x.CancelDate == null
  && x.AddressTable.Code.Trim() == "PROV"
  && x.AddressVerificationRequest.VerifiedAddress != null
  && x.LocId == locationsSeqNum);

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
SELECT
  LVR.LOC_SEQ_NUM,
  LVR.ADDR_VERIFICATION_REQ_SEQ_NUM,
  LVR.REQUEST_DATE,
  LVR.ADDRESS_TABLE_CODE,
  LVR.UPDATE_NUM,
  LVR.CANCEL_DATE,
  LVR.UPDATE_DATE,
  LVR.UPDATE_USER
FROM LOCATION_VERIFICATION_REQUEST LVR
  INNER JOIN ADDRESS_VERIFICATION_REQUEST AVR
    ON AVR.ADDR_VERIFICATION_REQ_SEQ_NUM = LVR.ADDR_VERIFICATION_REQ_SEQ_NUM
WHERE LVR.CANCEL_DATE IS NULL
  AND TRIM(LVR.ADDRESS_TABLE_CODE) = 'PROV'
  AND AVR.VERIFIED_ADDRESS_SEQ_NUM IS NOT NULL
  AND LVR.LOC_SEQ_NUM = :locationsSeqNum
-- NHibernate expects at most one row

Translation notes: - x.AddressTable.Code is the identifier of the AddressTable key-reference, so it resolves to the owner column LVR.ADDRESS_TABLE_CODE with no join; ADDRESS_TABLE_CODE is not accessed as a separate table. - x.AddressVerificationRequest.VerifiedAddress != null resolves to the FK null check AVR.VERIFIED_ADDRESS_SEQ_NUM IS NOT NULL with no join; VERIFIED_ADDRESS is not accessed as a separate table.

Operations and processes¶

Other query providers

Sources¶

Data/SAIF.Data/Queries/AddressVerification/AddressVerificationQueryProvider.cs:34