SQL-362: Delete BatchProviderAddress via StatelessProcessProviderAddressesForUpdates¶

This ORM statement re-loads a PROVIDER_ADDRESS row by identifier and then deletes it, as part of the manual-rollback path in batch provider-address updates, executed from Services/SAIF.Services/AddressVerificationService/AddressVerificationService.cs (L121). The SQL below is inferred from the IRepository<BatchProviderAddress>.Get/.Delete calls and the BatchProviderAddress NHibernate mapping, not literal SQL text in source.

Classification¶

Field	Value
SQL ID	SQL-362
Operation type	Delete
Execution method	ORM (IRepository.Delete)
Database	Oracle
Schema	[unqualified]**
Command type	N/A (ORM-generated)
Business object	`BatchProviderAddress`
Criteria property	N/A — ORM
Lifecycle method	`StatelessProcessProviderAddressesForUpdates`

Parameters¶

Parameter	Direction	Type	Max length	Description
`:Id`	Input	long	N/A	Identifier of the BatchProviderAddress being deleted (`PROVIDER_ADDRESS_SEQ_NUM`)
`:oldVersion`	Input	int	N/A	Current `UPDATE_NUM` value (optimistic-concurrency check)
`:newAddressId`	Input	long	N/A	Local `newAddressId` - value returned by `GetLastInsertId(...)` (line 108)

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`
PROVIDER_ADDRESS	DELETE	`PROVIDER_ADDRESS_SEQ_NUM`, `UPDATE_NUM`

LINQ statement¶

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

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

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
-- Get(newAddressId)
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 = :newAddressId

-- Delete
DELETE FROM PROVIDER_ADDRESS
WHERE PROVIDER_ADDRESS_SEQ_NUM = :Id
  AND UPDATE_NUM = :oldVersion

Translation notes:

Version(x => x.UpdateNumber).Column("UPDATE_NUM") adds the old-version predicate to the DELETE.
IsForeign (FOREIGN_ADDRESS_FLAG) is YesNoType ('Y'/'N').
Get(newAddressId) is evaluated inline as the argument to Delete, so the identifier SELECT is part of this call expression and is shown first.
If Get returns null (no row), Delete(null) throws; the enclosing catch (line 125) rethrows as "Failed to manually rollback legacy updates."

Operations and processes¶

AddressVerificationService.StatelessProcessProviderAddressesForUpdates — see Operations and processes

Sources¶

Services/SAIF.Services/AddressVerificationService/AddressVerificationService.cs (L121)