SQL-343: Retrieve Transaction

This ORM statement performs a read operation on Transaction, either listing all transactions for a case ordered by date, or retrieving a single transaction by ID, via IUnitOfWork.GetRepository<T>(), executed from Services/SAIF.Services/Collections/CollectionsService.cs (L1568, L1609, L1632). The SQL below is inferred from the LINQ expressions and the Transaction NHibernate mapping (LEGAL_COLLECTION_TRANSACTION); it is not literal SQL text present in source. The x.Case.Id navigation short-circuits to the FK column on LEGAL_COLLECTION_TRANSACTION and never joins LEGAL_COLLECTION.

Classification

Field	Value
SQL ID	SQL-343
Operation type	Read
Execution method	ORM (NHibernate LINQ)
Database	Oracle
Schema	[unqualified]**
Command type	N/A (ORM-generated)
Business object	Transaction
Criteria property	N/A — ORM
Lifecycle method	GetTransaction, GetTransactionData, GetTransactions

Parameters

Parameter	Direction	Type	Max length	Description
:caseId	Input	long	N/A	Method argument of GetTransactions
:id	Input	long	N/A	Method argument id (1609) / transactionId (1632)

Tables

Table	Access	Columns used
LEGAL_COLLECTION_TRANSACTION	SELECT	LEGAL_COLL_TRAN_SEQ_NUM, UPDATE_NUM, TRANSACTION_DATE, REFERENCE_TEXT, TRANSACTION_AMT, OUTSTANDING_AMT, INTEREST_FLAG, SYSTEM_GENERATED_FLAG, UPDATE_USER, UPDATE_DATE, LEGAL_COLLECTION_SEQ_NUM, LEGAL_COLLECTION_TRAN_CODE, APPROVED_BY_USER

LINQ statement

Source: Services/SAIF.Services/Collections/CollectionsService.cs:1568

// Services/SAIF.Services/Collections/CollectionsService.cs:1568
var transactions = Mapper.Map<Transaction[]>(UnitOfWork.GetRepository<DataModel.Transaction>().Query()
      .Where(x => x.Case.Id == caseId)
      .OrderBy(x => x.TransactionDate)
      .ThenBy(x => x.Id)
      .ToList());

// Services/SAIF.Services/Collections/CollectionsService.cs:1609
return Mapper.Map<Transaction>(UnitOfWork.GetRepository<DataModel.Transaction>().Query()
    .SingleOrDefault(x => x.Id == id));

// Services/SAIF.Services/Collections/CollectionsService.cs:1632
return UnitOfWork.GetRepository<DataModel.Transaction>().Query()
      .SingleOrDefault(x => x.Id == transactionId);

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
-- CollectionsService.GetTransactions (line 1568)
SELECT
  LCT.LEGAL_COLL_TRAN_SEQ_NUM,
  LCT.UPDATE_NUM,
  LCT.TRANSACTION_DATE,
  LCT.REFERENCE_TEXT,
  LCT.TRANSACTION_AMT,
  LCT.OUTSTANDING_AMT,
  LCT.INTEREST_FLAG,
  LCT.SYSTEM_GENERATED_FLAG,
  LCT.UPDATE_USER,
  LCT.UPDATE_DATE,
  LCT.LEGAL_COLLECTION_SEQ_NUM,
  LCT.LEGAL_COLLECTION_TRAN_CODE,
  LCT.APPROVED_BY_USER
FROM LEGAL_COLLECTION_TRANSACTION LCT
WHERE LCT.LEGAL_COLLECTION_SEQ_NUM = :caseId
ORDER BY LCT.TRANSACTION_DATE, LCT.LEGAL_COLL_TRAN_SEQ_NUM

-- CollectionsService.GetTransaction (line 1609) and GetTransactionData (line 1632)
SELECT
  LCT.LEGAL_COLL_TRAN_SEQ_NUM,
  LCT.UPDATE_NUM,
  LCT.TRANSACTION_DATE,
  LCT.REFERENCE_TEXT,
  LCT.TRANSACTION_AMT,
  LCT.OUTSTANDING_AMT,
  LCT.INTEREST_FLAG,
  LCT.SYSTEM_GENERATED_FLAG,
  LCT.UPDATE_USER,
  LCT.UPDATE_DATE,
  LCT.LEGAL_COLLECTION_SEQ_NUM,
  LCT.LEGAL_COLLECTION_TRAN_CODE,
  LCT.APPROVED_BY_USER
FROM LEGAL_COLLECTION_TRANSACTION LCT
WHERE LCT.LEGAL_COLL_TRAN_SEQ_NUM = :id
-- NHibernate expects at most one row

Translation notes:

• Three call sites, two shapes; shown with label comments.
• INTEREST_FLAG and SYSTEM_GENERATED_FLAG are YesNoType.
• The ForEach block after ToList() (lines 1577-1602) runs in memory; its lazy navigation to TransactionCode.TransactionCategoryCode (line 1591) may trigger separate lazy-load selects that are not part of this statement.
• Data/SAIF.Data.NHibernate/Mappings/Collections/CollectionsMap.cs also declares a TransactionMap, but that file is not in SAIF.Data.NHibernate.csproj (only Collections\TransactionMap.cs, csproj line 471, is compiled), so it was ignored.

Operations and processes

• CollectionsService.GetTransaction — see Operations and processes
• CollectionsService.GetTransactionData — see Operations and processes
• CollectionsService.GetTransactions — see Operations and processes

Sources

• Services/SAIF.Services/Collections/CollectionsService.cs (L1568, L1609, L1632)