Skip to content

SQL-207: ProviderQueryProvider.GetProviderAddresses

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

Classification

Field Value
SQL ID SQL-207
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
addressType Input string N/A Method argument of ProviderQueryProvider.GetProviderAddresses bound into the ORM expression; NHibernate generates the bind variable at runtime
includeExpired Input bool 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:89-94. 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:89-94
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1026:DefaultParametersShouldNotBeUsed")]
public IEnumerable<ProviderAddress> GetProviderAddresses(long providerId, string addressType, bool includeExpired = false)
{
    return _providerAddressRepository
        .Where(a =>
            a.ProviderLocation.Provider.Id == providerId
            && a.AddressType.Code == addressType
            && (includeExpired || 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.PROVIDER_ADDRESS_CODE = :addressType
  AND (:includeExpired = 1 OR PA.EXP_DATE IS NULL)

Translation notes: - (includeExpired || a.ExpirationDate == null): includeExpired is a captured C# bool with no lambda parameter. NHibernate evaluates or binds it as a constant, so the effective SQL is either PA.EXP_DATE IS NULL (when false) or no expiry filter (when true). Shown as the equivalent (:includeExpired = 1 OR PA.EXP_DATE IS NULL).

Operations and processes

Business rules

Sources

  • Data/SAIF.Data/Queries/Providers/ProviderQueryProvider.cs:89