SQL-246: Retrieve PaymentRef via GetTransactions¶
This ORM statement retrieves the payment-reference ids for a legal-collection case, via IUnitOfWork.GetRepository<T>(), executed from Services/SAIF.Services/Collections/CollectionsService.cs (L1566).
Classification¶
| Field | Value |
|---|---|
| SQL ID | SQL-246 |
| Operation type | Read |
| Execution method | ORM (NHibernate LINQ) |
| Database | Oracle |
| Schema | [unqualified]** |
| Command type | N/A (ORM-generated) |
| Business object | CollectionsService |
| Criteria property | N/A — ORM |
| Lifecycle method | GetTransactions |
Parameters¶
| Parameter | Direction | Type | Max length | Description |
|---|---|---|---|---|
:caseId |
Input | long | N/A | Matched against LEGAL_COLLECTION_SEQ_NUM via x.Case.Id |
Tables¶
| Table | Access | Columns used |
|---|---|---|
| LEGAL_COLLECTION_PAYMENT_REF | SELECT | PAYMENT_TRAN_SEQ_NUM, LEGAL_COLLECTION_SEQ_NUM |
LINQ statement¶
Source: Services/SAIF.Services/Collections/CollectionsService.cs:1566
// Services/SAIF.Services/Collections/CollectionsService.cs:1566
var payRefRepo = UnitOfWork.GetRepository<DataModel.PaymentRef>().Query().Where(x => x.Case.Id == caseId).Select(x => x.Id).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
LCPR.PAYMENT_TRAN_SEQ_NUM
FROM LEGAL_COLLECTION_PAYMENT_REF LCPR
WHERE LCPR.LEGAL_COLLECTION_SEQ_NUM = :caseId
Translation notes:
- Projection Select(x => x.Id) → only the identifier column is selected.
- x.Case.Id is the identifier of Case (CaseMap.cs:12, LEGAL_COLLECTION_SEQ_NUM) and short-circuits to the FK LCPR.LEGAL_COLLECTION_SEQ_NUM; the LEGAL_COLLECTION table is not joined.
- Mappings/Collections/CollectionsMap.cs also declares a PaymentReferenceMap/CaseMap, but that file is not compiled (SAIF.Data.NHibernate.csproj lines 449 and 465 include CaseMap.cs/PaymentReferenceMap.cs; CollectionsMap.cs has no Compile entry). The compiled maps were used.
Operations and processes¶
- CollectionsService.GetTransactions — see Operations and processes
Sources¶
Services/SAIF.Services/Collections/CollectionsService.cs (L1566)