SQL-091: Retrieve BatchControl

This ORM statement performs a read operation on BatchControl via IUnitOfWork.GetRepository<T>(), executed from Services/SAIF.Services/Collections/CashReceiptsService.cs (L55, L148).

Classification

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

Parameters

Parameter	Direction	Type	Max length	Description
runDate	Input	**	N/A	x.BatchDate == runDate → column BATCH_DATE
"N"	Input	**	N/A	x.BatchBalancedFlag == "N" → column BATCH_BALANCED_FLAG
batchId	Input	**	N/A	x.Id == batchId → column LC_BATCH_CONTROL_SEQ_NUM

Tables

Table	Access	Columns used
LEGAL_COLLECTION_BATCH_CONTROL	SELECT	BATCH_DATE, BATCH_BALANCED_FLAG, LC_BATCH_CONTROL_SEQ_NUM, BATCH_ID, BATCH_COUNT, BATCH_AMOUNT, CREATE_DATE

LINQ statement

Source: Services/SAIF.Services/Collections/CashReceiptsService.cs:55

// Services/SAIF.Services/Collections/CashReceiptsService.cs:55-56
            var batch = UnitOfWork.GetRepository<DataModel.BatchControl>()
                .Query().Where(x => x.BatchDate == runDate && x.BatchBalancedFlag == "N");

// Services/SAIF.Services/Collections/CashReceiptsService.cs:57
            if (!batch.Any())

// Services/SAIF.Services/Collections/CashReceiptsService.cs:62
            if (Enumerable.Any(batch, batchControl => !ProcessBatch(batchControl.Id, controlLog, controlReport)))

// Services/SAIF.Services/Collections/CashReceiptsService.cs:148-149
            var batch = UnitOfWork.GetRepository<DataModel.BatchControl>()
                .Query().Single(x => x.Id == batchId);

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
-- Services/SAIF.Services/Collections/CashReceiptsService.cs:55 (Any, L57)
SELECT *
FROM (
  SELECT 1
  FROM LEGAL_COLLECTION_BATCH_CONTROL LCBC
  WHERE LCBC.BATCH_DATE = :runDate
    AND LCBC.BATCH_BALANCED_FLAG = 'N'
)
WHERE ROWNUM <= 1

-- Services/SAIF.Services/Collections/CashReceiptsService.cs:55 (enumeration, L62)
SELECT
  LCBC.LC_BATCH_CONTROL_SEQ_NUM,
  LCBC.BATCH_DATE,
  LCBC.BATCH_ID,
  LCBC.BATCH_COUNT,
  LCBC.BATCH_AMOUNT,
  LCBC.CREATE_DATE,
  LCBC.BATCH_BALANCED_FLAG
FROM LEGAL_COLLECTION_BATCH_CONTROL LCBC
WHERE LCBC.BATCH_DATE = :runDate
  AND LCBC.BATCH_BALANCED_FLAG = 'N'

-- Services/SAIF.Services/Collections/CashReceiptsService.cs:148
SELECT
  LCBC.LC_BATCH_CONTROL_SEQ_NUM,
  LCBC.BATCH_DATE,
  LCBC.BATCH_ID,
  LCBC.BATCH_COUNT,
  LCBC.BATCH_AMOUNT,
  LCBC.CREATE_DATE,
  LCBC.BATCH_BALANCED_FLAG
FROM LEGAL_COLLECTION_BATCH_CONTROL LCBC
WHERE LCBC.LC_BATCH_CONTROL_SEQ_NUM = :batchId
-- NHibernate expects at most one row

Translation notes: - The IQueryable built at L55-56 is executed twice: batch.Any() (L57) -> SELECT 1 ... ROWNUM <= 1; Enumerable.Any(batch, ...) (L62) is LINQ-to-Objects over the IQueryable, so it enumerates the full entity query (the predicate !ProcessBatch(...) runs in memory). - BatchBalancedFlag is a plain string Map (CollectionsCashReceipts.cs:37) -> literal 'N'.

Operations and processes

• CashReceiptsService.ApplyCashReceipts — see Operations and processes
• CashReceiptsService.ProcessBatch — see Operations and processes

Sources

• Services/SAIF.Services/Collections/CashReceiptsService.cs (L55, L148)