---
title: "SQL-049: ProvidersRepository.GetProviderAddresses"
summary: Read operation for ProvidersRepository
status: draft
owner: SAIF.Core documentation team
last_reviewed: 2026-09-24
library: SAIF.Data.NHibernate
reference_type: package
---


# SQL-049: ProvidersRepository.GetProviderAddresses

This statement is a read operation invoked through ORM (NHibernate LINQ). Executed by `ProvidersRepository.GetProviderAddresses`.

## Classification

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

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:providerId` | Input | long | N/A | Method argument bound into the LINQ expression |
| `:addressType` | Input | string | N/A | Method argument bound into the LINQ expression; the wrapper methods pass `'PAY'` and `'PHY'` |

## 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` |
| [PROVIDER_LOCATION](../database-tables.md#provider_location) | JOIN (inner, Where navigation) | `PROVIDER_LOCATION_SEQ_NUM`, `PROVIDER_SEQ_NUM` |

## LINQ statement

Source: `Data/SAIF.Data.NHibernate/Repositories/ProvidersRepository.cs:35`, with the `PAY`/`PHY` wrapper methods at `:45` and `:50`.

```csharp
public IEnumerable<ProviderAddress> GetProviderAddresses(long providerId, string addressType)
{
    return
        unitOfWork.GetRepository<ProviderAddress>()
            .Query()
            .Where(pa =>
                pa.ProviderLocation.Provider.Id == providerId && pa.ExpirationDate == null &&
                pa.AddressType.Code == addressType).ToList();
}

public IEnumerable<ProviderAddress> GetProviderPaymentAddresses(long providerId)
{
    return GetProviderAddresses(providerId, "PAY");
}

public IEnumerable<ProviderAddress> GetProviderPhysicalAddresses(long providerId)
{
    return GetProviderAddresses(providerId, "PHY");
}
```

## 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
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
  INNER JOIN PROVIDER_LOCATION PL
    ON PL.PROVIDER_LOCATION_SEQ_NUM = PA.PROVIDER_LOCATION_SEQ_NUM
WHERE PL.PROVIDER_SEQ_NUM = :providerId
  AND PA.EXP_DATE IS NULL
  AND PA.PROVIDER_ADDRESS_CODE = :addressType
```

Translation notes:
- `pa.ProviderLocation.Provider.Id`: the first hop, `ProviderLocation`, is followed by a non-identifier navigation (`.Provider`), so `PROVIDER_LOCATION` must be joined (inner, implicit). The final `.Id` short-circuits to `PL.PROVIDER_SEQ_NUM`; `PROVIDER` itself is **never joined**.
- `pa.AddressType.Code` is the identifier of `ProviderAddressCode`, so it short-circuits to the FK `PA.PROVIDER_ADDRESS_CODE`; `PROVIDER_ADDRESS_CODE` is **never joined**.
- **Dialect caveat:** `ProvidersRepository` has no DI registration or `new ProvidersRepository(...)` usage found anywhere in the repository, so the call-site dialect cannot be proven from wiring. The only NHibernate configuration factory available to this assembly is the Oracle one, so Oracle is assumed rather than confirmed.

## Operations and processes

- [Provider address retrieval](../operations/provider-address-retrieval.md)

## Business rules

- [SDN-BR-030](../business-rules.md#sdn-br-030-providersrepository-requires-a-non-null-unit-of-work)
- [SDN-BR-031](../business-rules.md#sdn-br-031-provider-payment-address-type-%3D-pay%2C-physical-%3D-phy)

> **Note:** Statements marked with `**` could not be confirmed; treat
> as unknown until verified.

## Sources
- `Data/SAIF.Data.NHibernate/Repositories/ProvidersRepository.cs:35`