Skip to content

SQL-204: ProviderQueryProvider.GetProviderByTaxId

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

Classification

Field Value
SQL ID SQL-204
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 GetProviderByTaxId

Parameters

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

Tables

Table Access Columns used
PROVIDER SELECT PROVIDER_SEQ_NUM, UPDATE_NUM, PRIMARY_TAX_ID, UPDATE_DATE, UPDATE_USER (PRIMARY_TAX_ID also in COUNT filter)

LINQ statement

Source: Data/SAIF.Data/Queries/Providers/ProviderQueryProvider.cs:61, 66. The repository/queryable is initialised at Data/SAIF.Data/Queries/Providers/ProviderQueryProvider.cs:27.

// Data/SAIF.Data/Queries/Providers/ProviderQueryProvider.cs:27
_providerRepository = Factory.GetRepository<Provider>().Query();

// Data/SAIF.Data/Queries/Providers/ProviderQueryProvider.cs:61
public Provider GetProviderByTaxId(string taxId, bool includeNonPrimaryTaxIds = false)
{
    var provider = new Provider();
    if (includeNonPrimaryTaxIds)
    {
        var providerTaxId = _providerTaxIdRepository.FirstOrDefault(pr => pr.TaxId == taxId);
        if (providerTaxId != null)
        {
            provider = _providerRepository.SingleOrDefault(pr => pr.Id == Convert.ToInt64(providerTaxId.Provider.Id));
        }
    }
    else
    {
        provider = _providerRepository.Count(pr => pr.PrimaryTaxId == taxId).Equals(0) ? new Provider() : _providerRepository.SingleOrDefault(pr => pr.PrimaryTaxId == taxId);
    }

    return provider;
}

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
-- Branch includeNonPrimaryTaxIds = true (line 61), after SQL-203 returns a row
SELECT
  P.PROVIDER_SEQ_NUM,
  P.UPDATE_NUM,
  P.PRIMARY_TAX_ID,
  P.UPDATE_DATE,
  P.UPDATE_USER
FROM PROVIDER P
WHERE P.PROVIDER_SEQ_NUM = :providerId;
-- SingleOrDefault: NHibernate expects at most one row

-- Branch includeNonPrimaryTaxIds = false (line 66), statement 1: Count
SELECT COUNT(*)
FROM PROVIDER P
WHERE P.PRIMARY_TAX_ID = :taxId;

-- Branch includeNonPrimaryTaxIds = false (line 66), statement 2: only when COUNT <> 0
SELECT
  P.PROVIDER_SEQ_NUM,
  P.UPDATE_NUM,
  P.PRIMARY_TAX_ID,
  P.UPDATE_DATE,
  P.UPDATE_USER
FROM PROVIDER P
WHERE P.PRIMARY_TAX_ID = :taxId
-- SingleOrDefault: NHibernate expects at most one row

Translation notes: - Convert.ToInt64(providerTaxId.Provider.Id) touches only a materialized object (not the lambda parameter), so it is evaluated in memory and bound as :providerId. Reading providerTaxId.Provider.Id on the lazy proxy does not trigger a load, because the Id is known from the FK PROVIDER_SEQ_NUM. - The false branch makes two round trips. If more than one provider shares a PRIMARY_TAX_ID, SingleOrDefault throws.

Operations and processes

Business rules

Sources

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