SQL-166: Retrieve DocumentTemplate via TemplateQuery (property)¶

TemplateQuery exposes an IQueryable<DocumentTemplate> over DOCUMENT_TEMPLATE. Its only composing consumer looks up a single template by its identifier so DocumentRequestService can resolve the template referenced by a document request.

Classification¶

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

Parameters¶

Parameter	Direction	Type	Max length	Description
`:templateId`	Input	long	N/A	`a.Id == documentRequest.TemplateId` → column DOCUMENT_TEMPLATE_SEQ_NUM

Tables¶

Table	Access	Columns used
DOCUMENT_TEMPLATE	SELECT	all mapped columns; filter DOCUMENT_TEMPLATE_SEQ_NUM

LINQ statement¶

Source: Services/SAIF.Services/Documents/DocumentRequestService.cs:70. Composing consumer at Services/SAIF.Services/Documents/DocumentRequestService.cs:587.

// Services/SAIF.Services/Documents/DocumentRequestService.cs:70
return UnitOfWork.GetRepository<DocumentTemplate>().Query();

// Services/SAIF.Services/Documents/DocumentRequestService.cs:587
var template = TemplateQuery.SingleOrDefault(a => a.Id == documentRequest.TemplateId);

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
-- Base: TemplateQuery (DocumentRequestService.cs:70) — IQueryable only.
-- Consumer: DocumentRequestService.cs:587 — TemplateQuery.SingleOrDefault(a => a.Id == documentRequest.TemplateId)
SELECT DT.DOCUMENT_TEMPLATE_SEQ_NUM,
       DT.TEMPLATE_NAME,
       DT.TEMPLATE_TITLE,
       DT.RUN_TEMPLATE_NAME,
       DT.RUN_ROUTINE_NAME,
       DT.RUN_PARAMETER_FORM_NAME,
       DT.NEXT_DOC_TEMPLATE_SEQ_NUM,
       DT.ADDITIONAL_PAGE_NUM,
       DT.BUSINESS_GROUP_DESCRIPTION,
       DT.SUB_GROUP_DESCRIPTION,
       DT.ENABLED_FLAG,
       DT.EFFECTIVE_DATE,
       DT.EXPIRATION_DATE,
       DT.NOTE_TXT,
       DT.IMAGE_FILE_CODE,
       DT.IMAGE_CATEGORY_CODE,
       DT.IMAGE_DOCUMENT_TYPE_CODE,
       DT.IMAGE_DOCUMENT_NAME_CODE,
       DT.IMAGE_DOCUMENT_NAME,
       DT.IMAGE_REQUIRED_FLAG,
       DT.SHARED_MESSAGE_TXT,
       DT.MAIN_BANNER_PAGE_TXT,
       DT.ADDITIONAL_BANNER_PAGE_TXT,
       DT.UPDATE_NUM,
       DT.UPDATE_USER,
       DT.UPDATE_DATE,
       DT.CREATE_USER,
       DT.CREATE_DATE,
       DT.RUN_TYPE_CODE,
       DT.CONTEXT_CODE
FROM   DOCUMENT_TEMPLATE DT
WHERE  DT.DOCUMENT_TEMPLATE_SEQ_NUM = :templateId;
-- NHibernate expects at most one row

Translation notes: - L70 is a queryable-property definition; L587 is its only composing consumer in DocumentRequestService.cs. - SingleOrDefault/Single add no row limit; NHibernate throws if more than one row is returned. - Materialization includes FK columns RUN_TYPE_CODE and CONTEXT_CODE (References) but no joins.

Operations and processes¶

DocumentRequestService.TemplateQuery (property) — see Operations and processes

Sources¶

Services/SAIF.Services/Documents/DocumentRequestService.cs (L70)