---
title: "SQL-093: Retrieve BatchProviderAddress"
summary: Read operation against BatchProviderAddress via NHibernate ORM.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-09-24
library: SAIF.Services
reference_type: package
---

# 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](../database-tables.md#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`

```csharp
// 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.

```sql
-- 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](../operations-processes.md)
- AddressVerificationService.StatelessProcessProviderAddressesForUpdates — see [Operations and processes](../operations-processes.md)

## Sources
- `Services/SAIF.Services/AddressVerificationService/AddressVerificationService.cs (L122, L165)`