SQL-345: Retrieve TransactionCode¶

This ORM statement performs a read operation on TransactionCode, either retrieving the single row matching a code, or listing all codes ordered by description, via IUnitOfWork.GetRepository<T>(), executed from Services/SAIF.Services/Collections/CollectionsService.cs (L1697, L1743). The SQL below is inferred from the LINQ expressions and the TransactionCode NHibernate mapping (LEGAL_COLLECTION_TRAN_CODE); it is not literal SQL text present in source.

Classification¶

Field	Value
SQL ID	SQL-345
Operation type	Read
Execution method	ORM (NHibernate LINQ)
Database	Oracle
Schema	[unqualified]**
Command type	N/A (ORM-generated)
Business object	`TransactionCode`
Criteria property	N/A — ORM
Lifecycle method	`GetTransactionCodes, InsertTransaction`

Parameters¶

Parameter	Direction	Type	Max length	Description
`:Code`	Input	string	N/A	Captured member `transEdit.TransactionCode.Code`

Tables¶

Table	Access	Columns used
LEGAL_COLLECTION_TRAN_CODE	SELECT	`Code`, `CODE_DESCR`, `APPROVAL_REQUIRED_FLAG`, `LC_TRAN_CATEGORY_CODE`

LINQ statement¶

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

// Services/SAIF.Services/Collections/CollectionsService.cs:1697
var categoryCode = UnitOfWork.GetRepository<DataModel.TransactionCode>().Query()
                    .Single(x => x.Code == transEdit.TransactionCode.Code).TransactionCategoryCode;

// Services/SAIF.Services/Collections/CollectionsService.cs:1743
var transactionCodes = Mapper.Map<TransactionCode[]>(UnitOfWork.GetRepository<DataModel.TransactionCode>().Query().OrderBy(x => x.Description).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
-- CollectionsService.InsertTransaction (line 1697)
SELECT
  LCTC.Code,
  LCTC.CODE_DESCR,
  LCTC.APPROVAL_REQUIRED_FLAG,
  LCTC.LC_TRAN_CATEGORY_CODE
FROM LEGAL_COLLECTION_TRAN_CODE LCTC
WHERE LCTC.Code = :Code
-- NHibernate expects at most one row

-- CollectionsService.GetTransactionCodes (line 1743)
SELECT
  LCTC.Code,
  LCTC.CODE_DESCR,
  LCTC.APPROVAL_REQUIRED_FLAG,
  LCTC.LC_TRAN_CATEGORY_CODE
FROM LEGAL_COLLECTION_TRAN_CODE LCTC
ORDER BY LCTC.CODE_DESCR

Translation notes:

Two shapes; shown with label comments.
.TransactionCategoryCode (line 1698) is read in memory after Single; it may lazy-load LC_TRAN_CATEGORY_CODE in a separate statement (not part of this SQL).
APPROVAL_REQUIRED_FLAG is YesNoType.

Operations and processes¶

CollectionsService.GetTransactionCodes — see Operations and processes
CollectionsService.InsertTransaction — see Operations and processes

Sources¶

Services/SAIF.Services/Collections/CollectionsService.cs (L1697, L1743)