SQL-163: Retrieve DocumentRequestLog via RequestLogQuery (property)

RequestLogQuery exposes an IQueryable<DocumentRequestLog> over DOCUMENT_REQUEST_LOG. Its composing consumers look up a single request by ID, find the most recent "new" macro or non-macro request for a user, or list all pending requests, driving document-request workflow decisions in DocumentRequestService.

Classification

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

Parameters

Parameter	Direction	Type	Max length	Description
:requestLogId	Input	long	N/A	a.Id == requestLogId → column DOCUMENT_REQUEST_LOG_SEQ_NUM
:userIdLower	Input	string	N/A	= userId.ToLower(), evaluated client-side → column SAIF_MAIL_ID

Tables

Table	Access	Columns used
DOCUMENT_REQUEST_LOG	SELECT	all mapped columns; filters on DOCUMENT_REQUEST_LOG_SEQ_NUM, SAIF_MAIL_ID, DOC_REQUEST_LOG_STATUS_CODE; order CREATE_DATE
DOCUMENT_TEMPLATE	JOIN	DOCUMENT_TEMPLATE_SEQ_NUM (join), RUN_TYPE_CODE (filter) — macro/non-macro consumers only

LINQ statement

Source: Services/SAIF.Services/Documents/DocumentRequestService.cs:78. Composing consumers at Services/SAIF.Services/Documents/DocumentRequestService.cs:83, :191, :207, :218, :257.

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

// Services/SAIF.Services/Documents/DocumentRequestService.cs:83
var request = RequestLogQuery.SingleOrDefault(a => a.Id == requestLogId);

// Services/SAIF.Services/Documents/DocumentRequestService.cs:191
var request = RequestLogQuery.Where(a => a.UserId.ToLower() == userId.ToLower() &&
                                         a.DocRequestLogStatusCode == DocumentRequestLog.NewRequest &&
                                         a.MyDocTemplate.RunType.Code == "MACRO")
                             .OrderByDescending(a => a.CreateDate)
                             .FirstOrDefault();

// Services/SAIF.Services/Documents/DocumentRequestService.cs:207
var request = RequestLogQuery.Where(a => a.UserId.ToLower() == userId.ToLower() &&
                                         a.DocRequestLogStatusCode == DocumentRequestLog.NewRequest) // && a.MyDocTemplate.RunType.Code == "MACRO")
                             .OrderByDescending(a => a.CreateDate)
                             .FirstOrDefault();

// Services/SAIF.Services/Documents/DocumentRequestService.cs:218
var request = RequestLogQuery.Where(a => a.UserId.ToLower() == userId.ToLower() &&
                                         a.DocRequestLogStatusCode == DocumentRequestLog.NewRequest &&
                                         a.MyDocTemplate.RunType.Code != "MACRO")
                             .OrderByDescending(a => a.CreateDate)
                             .FirstOrDefault();

// Services/SAIF.Services/Documents/DocumentRequestService.cs:257
IEnumerable<DocumentRequestLog> requests = RequestLogQuery
    .Where(a => a.DocRequestLogStatusCode == DocumentRequestLog.PendingRequest);

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: RequestLogQuery (DocumentRequestService.cs:78) — IQueryable only; SQL is emitted by each consumer.

-- (a) L83 / L539 / L566 — SingleOrDefault(a => a.Id == requestLogId)
SELECT DRL.DOCUMENT_REQUEST_LOG_SEQ_NUM,
       DRL.DOC_REQUEST_LOG_STATUS_CODE,
       DRL.SAIF_MAIL_ID,
       DRL.CREATE_USER,
       DRL.CREATE_DATE,
       DRL.UPDATE_USER,
       DRL.UPDATE_DATE,
       DRL.UPDATE_NUM,
       DRL.CONTEXT_VALUE,
       DRL.DOCUMENT_TEMPLATE_SEQ_NUM
FROM   DOCUMENT_REQUEST_LOG DRL
WHERE  DRL.DOCUMENT_REQUEST_LOG_SEQ_NUM = :requestLogId;
-- NHibernate expects at most one row

-- (b) L191 — GetLatestMacroRequestForUserOrDefault
SELECT * FROM (
    SELECT DRL.DOCUMENT_REQUEST_LOG_SEQ_NUM, DRL.DOC_REQUEST_LOG_STATUS_CODE, DRL.SAIF_MAIL_ID, DRL.CREATE_USER,
           DRL.CREATE_DATE, DRL.UPDATE_USER, DRL.UPDATE_DATE, DRL.UPDATE_NUM, DRL.CONTEXT_VALUE, DRL.DOCUMENT_TEMPLATE_SEQ_NUM
    FROM   DOCUMENT_REQUEST_LOG DRL
    INNER JOIN DOCUMENT_TEMPLATE DT ON DT.DOCUMENT_TEMPLATE_SEQ_NUM = DRL.DOCUMENT_TEMPLATE_SEQ_NUM
    WHERE  LOWER(DRL.SAIF_MAIL_ID) = :userIdLower
      AND  DRL.DOC_REQUEST_LOG_STATUS_CODE = 'NEW'      -- DocumentRequestLog.NewRequest
      AND  DT.RUN_TYPE_CODE = 'MACRO'
    ORDER BY DRL.CREATE_DATE DESC
) WHERE ROWNUM <= 1;

-- (c) L207 — GetLatestRequestForUserOrDefault (no template join; RunType predicate is commented out in source)
SELECT * FROM (
    SELECT DRL.DOCUMENT_REQUEST_LOG_SEQ_NUM, DRL.DOC_REQUEST_LOG_STATUS_CODE, DRL.SAIF_MAIL_ID, DRL.CREATE_USER,
           DRL.CREATE_DATE, DRL.UPDATE_USER, DRL.UPDATE_DATE, DRL.UPDATE_NUM, DRL.CONTEXT_VALUE, DRL.DOCUMENT_TEMPLATE_SEQ_NUM
    FROM   DOCUMENT_REQUEST_LOG DRL
    WHERE  LOWER(DRL.SAIF_MAIL_ID) = :userIdLower
      AND  DRL.DOC_REQUEST_LOG_STATUS_CODE = 'NEW'
    ORDER BY DRL.CREATE_DATE DESC
) WHERE ROWNUM <= 1;

-- (d) L218 — GetLatestXpRequestForUserOrDefault: same as (b) but DT.RUN_TYPE_CODE <> 'MACRO'

-- (e) L257-258 — GetPendingRequests (materialized later; L260 filter runs in memory, see notes)
SELECT DRL.DOCUMENT_REQUEST_LOG_SEQ_NUM, DRL.DOC_REQUEST_LOG_STATUS_CODE, DRL.SAIF_MAIL_ID, DRL.CREATE_USER,
       DRL.CREATE_DATE, DRL.UPDATE_USER, DRL.UPDATE_DATE, DRL.UPDATE_NUM, DRL.CONTEXT_VALUE, DRL.DOCUMENT_TEMPLATE_SEQ_NUM
FROM   DOCUMENT_REQUEST_LOG DRL
WHERE  DRL.DOC_REQUEST_LOG_STATUS_CODE = 'PEND';   -- DocumentRequestLog.PendingRequest

Translation notes: - L78 is a queryable-property definition; each consumer composes its own statement (consumers: L83, L191, L207, L218, L257, L539, L566). - DocumentRequestLog.NewRequest="NEW", PendingRequest="PEND". C# const values are shown as literals for readability; NHibernate LINQ sends them as bind parameters. - a.MyDocTemplate.RunType.Code is a two-hop path: MyDocTemplate needs an INNER JOIN to DOCUMENT_TEMPLATE (because RunType is not its identifier), but RunType.Code is the identifier of RunTypeCode so it resolves to DT.RUN_TYPE_CODE with no further join (the RUN_TYPE_CODE code table itself is never joined). - FirstOrDefault → Oracle10 dialect wraps the query in SELECT * FROM (...) WHERE ROWNUM <= 1. - SingleOrDefault/Single add no row limit; NHibernate throws if more than one row is returned. - (e): requests is declared IEnumerable<DocumentRequestLog>, so the L260 .Where(a => a.UserId.ToUpper() == overrideUserId || a.CreateUser.ToUpper() == overrideUserId) is LINQ-to-Objects applied after the L258 query is enumerated — it does not appear in SQL.

Operations and processes

• DocumentRequestService.RequestLogQuery (property) — see Operations and processes

Sources

• Services/SAIF.Services/Documents/DocumentRequestService.cs (L78)