SQL-093: Retrieve BatchProviderAddress¶

This ORM statement performs a read operation on BatchProviderAddress via IUnitOfWork.GetRepository<T>(), executed from Services/SAIF.Services/AddressVerificationService/AddressVerificationService.cs (L122, L165).

Classification¶

Field	Value
SQL ID	SQL-093
Operation type	Read
Execution method	ORM (IRepository.Get) (L122); ORM (NHibernate LINQ) (L165)
Database	Oracle
Schema	[unqualified]**
Command type	N/A (ORM-generated)
Business object	`BatchProviderAddress`
Criteria property	N/A — ORM
Lifecycle method	`GetLastInsertId, StatelessProcessProviderAddressesForUpdates`

Parameters¶

Parameter	Direction	Type	Max length	Description
`newAddressId`	Input	**	N/A	identifier (`Get(id)`) → column PROVIDER_ADDRESS_SEQ_NUM
`providerLocationId`	Input	**	N/A	`a.ProviderLocationId == providerLocationId` → column PROVIDER_LOCATION_SEQ_NUM
`addressType`	Input	**	N/A	`a.AddressType == addressType` → column PROVIDER_ADDRESS_CODE
`null`	Input	**	N/A	`a.ExpirationDate == null` → column EXP_DATE

Tables¶

Table	Access	Columns used
PROVIDER_ADDRESS	SELECT	`PROVIDER_ADDRESS_SEQ_NUM`, `UPDATE_NUM`, `CITY_NAME`, `COUNTRY_CODE`, `EFF_DATE`, `EXP_DATE`, `ADDRESS_LINE1`, `ADDRESS_LINE2`, `ADDRESS_LINE3`, `ADDRESS_LINE4`, `ZIP_CODE_EXT`, `STATE_CODE`, `ZIP_CODE`, `UPDATE_DATE`, `UPDATE_USER`, `FOREIGN_ADDRESS_FLAG`, `PROVIDER_ADDRESS_CODE`, `PROVIDER_LOCATION_SEQ_NUM`

LINQ statement¶

Source: Services/SAIF.Services/AddressVerificationService/AddressVerificationService.cs:121

// Services/SAIF.Services/AddressVerificationService/AddressVerificationService.cs:121-122
                                _unitOfWork.GetRepository<BatchProviderAddress>().Delete(
                                    _unitOfWork.GetRepository<BatchProviderAddress>().Get(newAddressId));

// Services/SAIF.Services/AddressVerificationService/AddressVerificationService.cs:165-169
            var batchProviderAddresses = _unitOfWork.GetRepository<BatchProviderAddress>().Query()
                .Where(a => a.ProviderLocationId == providerLocationId
                    && a.AddressType == addressType
                    && a.ExpirationDate == null)
                .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
-- Services/SAIF.Services/AddressVerificationService/AddressVerificationService.cs:122
SELECT
  PA.PROVIDER_ADDRESS_SEQ_NUM,
  PA.UPDATE_NUM,
  PA.CITY_NAME,
  PA.COUNTRY_CODE,
  PA.EFF_DATE,
  PA.EXP_DATE,
  PA.ADDRESS_LINE1,
  PA.ADDRESS_LINE2,
  PA.ADDRESS_LINE3,
  PA.ADDRESS_LINE4,
  PA.ZIP_CODE_EXT,
  PA.STATE_CODE,
  PA.ZIP_CODE,
  PA.UPDATE_DATE,
  PA.UPDATE_USER,
  PA.FOREIGN_ADDRESS_FLAG,
  PA.PROVIDER_ADDRESS_CODE,
  PA.PROVIDER_LOCATION_SEQ_NUM
FROM PROVIDER_ADDRESS PA
WHERE PA.PROVIDER_ADDRESS_SEQ_NUM = :Id

-- Services/SAIF.Services/AddressVerificationService/AddressVerificationService.cs:165
SELECT
  PA.PROVIDER_ADDRESS_SEQ_NUM,
  PA.UPDATE_NUM,
  PA.CITY_NAME,
  PA.COUNTRY_CODE,
  PA.EFF_DATE,
  PA.EXP_DATE,
  PA.ADDRESS_LINE1,
  PA.ADDRESS_LINE2,
  PA.ADDRESS_LINE3,
  PA.ADDRESS_LINE4,
  PA.ZIP_CODE_EXT,
  PA.STATE_CODE,
  PA.ZIP_CODE,
  PA.UPDATE_DATE,
  PA.UPDATE_USER,
  PA.FOREIGN_ADDRESS_FLAG,
  PA.PROVIDER_ADDRESS_CODE,
  PA.PROVIDER_LOCATION_SEQ_NUM
FROM PROVIDER_ADDRESS PA
WHERE PA.PROVIDER_LOCATION_SEQ_NUM = :providerLocationId
  AND PA.PROVIDER_ADDRESS_CODE = :addressType
  AND PA.EXP_DATE IS NULL

Translation notes: - L122 Get(newAddressId) -> SELECT ... WHERE <ID> = :Id; if the entity is already in the session first-level cache no SELECT is issued. The enclosing Update(oldProviderAddress) (L120) and Delete(...) (L121) are separate CRUD statements not documented under this SQL ID. - ProviderLocationId is a scalar Map on PROVIDER_LOCATION_SEQ_NUM - no join to PROVIDER_LOCATION.

Operations and processes¶

AddressVerificationService.GetLastInsertId — see Operations and processes
AddressVerificationService.StatelessProcessProviderAddressesForUpdates — see Operations and processes

Sources¶

Services/SAIF.Services/AddressVerificationService/AddressVerificationService.cs (L122, L165)