---
title: "SQL-343: Retrieve Transaction"
summary: Read operation against Transaction via NHibernate ORM.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-09-24
library: SAIF.Services
reference_type: package
---

# 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](../database-tables.md#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`

```csharp
// 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.

```sql
-- 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](../operations-processes.md)
- CollectionsService.GetTransactionData — see [Operations and processes](../operations-processes.md)
- CollectionsService.GetTransactions — see [Operations and processes](../operations-processes.md)

## Sources
- `Services/SAIF.Services/Collections/CollectionsService.cs (L1568, L1609, L1632)`