Skip to content

SQL-032: BillQueryProvider.GetBillAmounts

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

Classification

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

Parameters

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

Tables

Table Access Columns used
CBP_BILL** SELECT (cross join) BILL_ID, CURRENT_BILL_STATUS_CODE; claim predicate unresolved (Claim)
CBP_AUDITED_SERVICE SELECT (cross join) BILL_ID, ALLOWED_AMT, BILL_PAYMENT_CATEGORY_CODE
CBP_AUDIT JOIN BILL_ID

LINQ statement

Source: Data/SAIF.Data/Queries/Claims/BillQueryProvider.cs:78. The query is defined at :79-85 and executed by the first Sum() at :87.

var billtype = billTypes.ToList();
var reviewedBills = from bil in Factory.GetRepository<CbpBill>().Query()
                    from asv in Factory.GetRepository<CbpAuditedService>().Query()
                    where bil.Id == asv.Audit.Bill.Id
                          && bil.Claim.Id == claimId
                          && billtype.Contains(bil.BillStatus.Code)
                          && asv.PaymentCategoryCode.Code == paymentCategory
                    select asv.AllowedAmount;

return (reviewedBills.Sum() != null)
    ? (decimal)reviewedBills.Sum()
    : 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
SELECT SUM(CAS.ALLOWED_AMT)
FROM CBP_BILL CB,
     CBP_AUDITED_SERVICE CAS
  INNER JOIN CBP_AUDIT CA
    ON CA.BILL_ID = CAS.BILL_ID
WHERE CB.BILL_ID = CA.BILL_ID
  AND CB.Claim.Id = :claimId -- unresolved mapping: CbpBill.Claim
  AND CB.CURRENT_BILL_STATUS_CODE IN (:billtype0, :billtype1, :billtypeN)
  AND CAS.BILL_PAYMENT_CATEGORY_CODE = :paymentCategory

Translation notes: - Cross join: the two from clauses produce a theta-style cross join filtered by bil.Id == asv.Audit.Bill.Id. - CBP_AUDIT join: asv.Audit.Bill follows the two-hop navigation rule; NHibernate may elide the join. - bil.BillStatus.Code is the identifier of the referenced code, so it resolves to CB.CURRENT_BILL_STATUS_CODE with no join; CBP_BILL_STATUS_CODE is not accessed as a separate table. - asv.PaymentCategoryCode.Code is the identifier of the referenced code, so it resolves to CAS.BILL_PAYMENT_CATEGORY_CODE with no join; CL_PAYMENT_CATEGORY_CODE is not accessed as a separate table. - asv.Audit.Bill.Id short-circuits on the Bill hop, so a second CBP_BILL instance is not accessed. - CL_INJURY_CLAIM is unmapped, so it is not included. - Two round trips: Sum() is invoked twice (:87 and :88), so the identical statement executes twice; SUM returns NULL when no rows match, which yields 0 client-side. - Unresolved mapping CbpBill.Claim: CbpBillMap has no References(x => x.Claim). This query is expected to fail at runtime with an NHibernate QueryException. The likely intended predicate is CB.CLAIM_ID = :claimId.

Operations and processes

Business rules

Sources

  • Data/SAIF.Data/Queries/Claims/BillQueryProvider.cs:79