Skip to content

SQL-020: AuditQueryProvider.GetOriginalBills

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

Classification

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

Parameters

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

Tables

Table Access Columns used
CBP_AUDITED_SERVICE SELECT BILL_ID, ORIG_BILL_ID
CBP_AUDIT JOIN BILL_ID

LINQ statement

Source: Data/SAIF.Data/Queries/AuditQueryProvider.cs:92-99.

return Factory.GetRepository<CbpAuditedService>().Query()
   .Select(x => new
    {
        BillId = x.Audit.Bill.Id,
        OriginalBillId = x.OriginalBillId
    })
   .Where(x => x.BillId == billId)
   .ToList().Distinct().Select(x => x.OriginalBillId.GetValueOrDefault()).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
  CA.BILL_ID AS BillId,
  CAS.ORIG_BILL_ID AS OriginalBillId
FROM CBP_AUDITED_SERVICE CAS
  INNER JOIN CBP_AUDIT CA
    ON CA.BILL_ID = CAS.BILL_ID
WHERE CA.BILL_ID = :billId

Translation notes: - x.Audit.Bill.Id short-circuits to CA.BILL_ID; CBP_BILL is not accessed as a separate table. - Where the Where lands: it follows the anonymous projection, and NHibernate pushes it down onto the projected expression. - In-memory steps: Distinct() and GetValueOrDefault() run after ToList(). The SQL has no DISTINCT, and NULL ORIG_BILL_ID values become 0 in memory.

Operations and processes

Business rules

Sources

  • Data/SAIF.Data/Queries/AuditQueryProvider.cs:92