SQL-220: Sum LegalBillService paid amount

BillPaymentService.GenerateCheckTransactionsAsync sums PaidAmount across LegalBillService rows for a single bill to decide whether the bill is a zero-pay letter. This statement is issued once per approved bill inside a foreach loop (BillPaymentService.cs:181-185) — an N+1 pattern, not a single aggregate query across all bills.

Classification

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

Parameters

Parameter	Direction	Type	Max length	Description
:billId	Input	long	N/A	bill.Id, the current loop-iteration bill's identifier

Tables

Table	Access	Columns used
LEGAL_BILL_SERVICE	SELECT	LEGAL_BILL_SEQ_NUM, PAID_AMOUNT (aggregate)

LEGAL_BILL is not joined: s.LegalBill.Id is the FK identifier LEGAL_BILL_SERVICE.LEGAL_BILL_SEQ_NUM.

LINQ statement

Source: Services/SAIF.Services/Legal/Bills/BillPaymentService.cs:185

if (_unitOfWork.GetRepository<BillData.LegalBillService>().Query().Where(s => s.LegalBill.Id == bill.Id).Sum(s => s.PaidAmount) <= 0)
{
    controlStats.IdentifiedZeroPayLetters++;

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
-- Executed once per approved bill inside a foreach loop (N+1 pattern)
SELECT SUM(S.PAID_AMOUNT)
FROM   LEGAL_BILL_SERVICE S
WHERE  S.LEGAL_BILL_SEQ_NUM = :billId

Translation notes: - Executed once per approved bill inside the foreach (L181-185), which is an N+1 pattern: one SELECT SUM(...) statement per bill, not one query for all bills. - <= 0 is compared in C#. If a bill has no service lines, SUM returns NULL; NHibernate's Sum over a non-nullable decimal then throws (or yields 0, depending on version) — flag this for runtime verification.

Operations and processes

• BillPaymentService.GenerateCheckTransactionsAsync — see Operations and processes

Sources

• Services/SAIF.Services/Legal/Bills/BillPaymentService.cs (L185)