SQL-018: AuditQueryProvider.GetNonDuplicateServiceCount¶

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

Classification¶

Field	Value
SQL ID	SQL-018
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	`GetNonDuplicateServiceCount`

Parameters¶

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

Tables¶

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

LINQ statement¶

Source: Data/SAIF.Data/Queries/AuditQueryProvider.cs:69. The repository is a class field initialised from Factory.GetRepository<CbpAuditedService>().Query() at Data/SAIF.Data/Queries/AuditQueryProvider.cs:24.

return serviceRepository.Count(x => x.Audit.Bill.Id == billId && x.OriginalBillCode.Code != "D");

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 COUNT(*)
FROM CBP_AUDITED_SERVICE CAS
  INNER JOIN CBP_AUDIT CA
    ON CA.BILL_ID = CAS.BILL_ID
WHERE CA.BILL_ID = :billId
  AND CAS.BILL_ORIG_CODE <> 'D'

Translation notes: - x.Audit.Bill.Id short-circuits to CA.BILL_ID; CBP_BILL is not accessed as a separate table. - x.OriginalBillCode.Code is the identifier of References(OriginalBillCode).Column("BILL_ORIG_CODE") (CbpOriginalBillCodeMap.cs:6), so it resolves to CAS.BILL_ORIG_CODE with no join; CBP_ORIGINAL_BILL_CODE is not accessed as a separate table. - NULL semantics: under Oracle, <> 'D' excludes rows where BILL_ORIG_CODE IS NULL. NHibernate does not add an OR IS NULL for reference-id comparisons.

SQL-018: AuditQueryProvider.GetNonDuplicateServiceCount¶

Classification¶

Parameters¶

Tables¶

LINQ statement¶

Sample SQL¶

Operations and processes¶

Business rules¶

Sources¶