SQL-206: ProviderQueryProvider.GetProviderAddresses¶

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

Classification¶

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

Parameters¶

Parameter	Direction	Type	Max length	Description
`providerId`	Input	long	N/A	Method argument of `ProviderQueryProvider.GetProviderAddresses` bound into the ORM expression; NHibernate generates the bind variable at runtime

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	PROVIDER_LOCATION_SEQ_NUM (join key), PROVIDER_SEQ_NUM (filter)

LINQ statement¶

Source: Data/SAIF.Data/Queries/Providers/ProviderQueryProvider.cs:79-83. The repository/queryable is initialised at Data/SAIF.Data/Queries/Providers/ProviderQueryProvider.cs:30.

// Data/SAIF.Data/Queries/Providers/ProviderQueryProvider.cs:30
_providerAddressRepository = Factory.GetRepository<ProviderAddress>().Query();

// Data/SAIF.Data/Queries/Providers/ProviderQueryProvider.cs:79-83
public IEnumerable<ProviderAddress> GetProviderAddresses(long providerId)
{
    return _providerAddressRepository
        .Where(a =>
            a.ProviderLocation.Provider.Id == providerId
              && 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
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

Translation notes: - Implicit member-access navigation a.ProviderLocation.Provider → INNER JOIN on References(x => x.ProviderLocation).Column("PROVIDER_LOCATION_SEQ_NUM") (ProviderAddressMap.cs:34). The trailing .Provider.Id short-circuits to PL.PROVIDER_SEQ_NUM.

SQL-206: ProviderQueryProvider.GetProviderAddresses¶

Classification¶

Parameters¶

Tables¶

LINQ statement¶

Sample SQL¶

Operations and processes¶

Business rules¶

Sources¶