Skip to content

SQL-098: Retrieve BillServiceFee via GetBillServiceFee

This ORM statement performs a read operation on BillServiceFee via IUnitOfWork.GetRepository<T>(), executed from Services/SAIF.Services/Legal/Bills/BillRateService.cs (L207).

Classification

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

Parameters

Parameter Direction Type Max length Description
code Input ** N/A x.BillServiceCode == code → column BILL_SERVICE_CODE
fromDate Input ** N/A x.EffectiveDate <= … → column EFF_DATE
null Input ** N/A x.ExpirationDate == null → column EXP_DATE
fromDate Input ** N/A x.ExpirationDate >= … → column EXP_DATE
mcoPlanId Input ** N/A x.McoPlanId == mcoPlanId → column MCO_PLAN_ID
providerLocationId Input ** N/A x.ImeId == providerLocationId → column IME_PROVIDER_LOC_SEQ_NUM

Tables

Table Access Columns used
BILL_SERVICE_FEE SELECT BILL_SERVICE_FEE_SEQ_NUM, BILL_SERVICE_CODE, EFF_DATE, EXP_DATE, IME_PROVIDER_LOC_SEQ_NUM, MCO_PLAN_ID, INITIAL_UNIT_AMOUNT, ADDITIONAL_UNIT_AMOUNT, INITIAL_UNITS, ADDITIONAL_UNITS, MAXIMUM_AMOUNT, FLAT_RATE_AMOUNT

LINQ statement

Source: Services/SAIF.Services/Legal/Bills/BillRateService.cs:207

// Services/SAIF.Services/Legal/Bills/BillRateService.cs:207-216
            return _unitOfWork.GetRepository<BillData.BillServiceFee>()
                   .Query()
                   .FirstOrDefault(
                       x =>
                           (x.BillServiceCode == code &&
                            (x.EffectiveDate <= fromDate &&
                            (x.ExpirationDate == null || x.ExpirationDate >= fromDate) &&
                            (x.McoPlanId == mcoPlanId || mcoPlanId == string.Empty) &&
                            (x.ImeId == providerLocationId || providerLocationId == 0)
                            )));

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
-- Services/SAIF.Services/Legal/Bills/BillRateService.cs:207
SELECT *
FROM (
  SELECT
    BSF.BILL_SERVICE_FEE_SEQ_NUM,
    BSF.BILL_SERVICE_CODE,
    BSF.EFF_DATE,
    BSF.EXP_DATE,
    BSF.IME_PROVIDER_LOC_SEQ_NUM,
    BSF.MCO_PLAN_ID,
    BSF.INITIAL_UNIT_AMOUNT,
    BSF.ADDITIONAL_UNIT_AMOUNT,
    BSF.INITIAL_UNITS,
    BSF.ADDITIONAL_UNITS,
    BSF.MAXIMUM_AMOUNT,
    BSF.FLAT_RATE_AMOUNT
  FROM BILL_SERVICE_FEE BSF
  WHERE (BSF.BILL_SERVICE_CODE = :code
    AND (BSF.EFF_DATE <= :fromDate
    AND (BSF.EXP_DATE IS NULL OR BSF.EXP_DATE >= :fromDate)
    AND (BSF.MCO_PLAN_ID = :mcoPlanId OR :mcoPlanId = '')
    AND (BSF.IME_PROVIDER_LOC_SEQ_NUM = :providerLocationId OR :providerLocationId = 0)))
)
WHERE ROWNUM <= 1

Translation notes: - mcoPlanId == string.Empty and providerLocationId == 0 reference only method arguments; NHibernate's partial evaluator pre-computes them client-side into constants, so at runtime each OR group collapses to either the column comparison or TRUE. Rendered as parameter comparisons to show intent (in Oracle '' is NULL, so a literal = '' would never be true - the client-side evaluation is what makes the empty-plan branch work).

Operations and processes

Sources

  • Services/SAIF.Services/Legal/Bills/BillRateService.cs (L207)