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	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	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.

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.

-- 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

Business rules¶

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

Sources¶

Data/SAIF.Data.NHibernate/Repositories/ProvidersRepository.cs:35