Skip to content

SQL-210: ProviderQueryProvider.GetBillProviderLocationIds

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

Classification

Field Value
SQL ID SQL-210
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 GetBillProviderLocationIds

Parameters

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

Tables

Table Access Columns used
CBP_BILL SELECT PROVIDER_LOCATION_SEQ_NUM (projection + filter), CURRENT_BILL_STATUS_CODE, CLAIM_ID (filters)

LINQ statement

Source: Data/SAIF.Data/Queries/Providers/ProviderQueryProvider.cs:161-164.

public IEnumerable<long> GetBillProviderLocationIds(long claimId)
{
    var providerLocationIds = Factory.GetRepository<CbpBill>()
        .Query()
        .Where(x => x.BillStatus.Code != "DIS" && x.ClaimId == claimId && x.BillingProvider != null)
        .Select(x => x.BillingProvider.Id).ToList();

    return providerLocationIds.Distinct();
}

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 CB.PROVIDER_LOCATION_SEQ_NUM
FROM CBP_BILL CB
WHERE CB.CURRENT_BILL_STATUS_CODE <> 'DIS'
  AND CB.CLAIM_ID = :claimId
  AND CB.PROVIDER_LOCATION_SEQ_NUM IS NOT NULL

Translation notes: - Select(x => x.BillingProvider.Id) → a scalar projection of the FK column, with no entity materialization. - Distinct() runs in memory after ToList() (line 166); it is not a SQL DISTINCT. - != "DIS" on a nullable column: some NHibernate versions render (CB.CURRENT_BILL_STATUS_CODE <> 'DIS' OR CB.CURRENT_BILL_STATUS_CODE IS NULL). The plain form is shown here (version-dependent).

Operations and processes

Business rules

Sources

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