Skip to content

SQL-164: Retrieve DocumentRequestLog

This statement reads DocumentRequestLog rows two ways: GetRecentRequests lists a user's requests from the last three months in descending create-date order, and DeleteTemplateRequests finds every request tied to a given template so they can be removed.

Classification

Field Value
SQL ID SQL-164
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 DeleteTemplateRequests, GetRecentRequests

Parameters

Parameter Direction Type Max length Description
:currentUserIdUpper Input string N/A = currentUser.UserId.ToUpper(), client-side → column CREATE_USER
:threeMonthsAgo Input DateTime N/A = DateTime.Now.AddMonths(-3), client-side → column CREATE_DATE
:templateId Input long N/A a.MyDocTemplate.Id == templateId → column DOCUMENT_TEMPLATE_SEQ_NUM

Tables

Table Access Columns used
DOCUMENT_REQUEST_LOG SELECT all mapped columns; filters CREATE_USER, CREATE_DATE, DOCUMENT_TEMPLATE_SEQ_NUM

LINQ statement

Source: Services/SAIF.Services/Documents/TemplateSearchService.cs:183. Second fragment at Services/SAIF.Services/Documents/TemplateSearchService.cs:873.

// Services/SAIF.Services/Documents/TemplateSearchService.cs:183
IEnumerable<DocumentRequestLog> requests = unitOfWork.GetRepository<DocumentRequestLog>().Query()
    .Where(a => a.CreateUser.ToUpper() == currentUser.UserId.ToUpper() && a.CreateDate > System.DateTime.Now.AddMonths(-3))
    .OrderByDescending(a => a.CreateDate).ToList();   //this sort uses hardly any time.

// Services/SAIF.Services/Documents/TemplateSearchService.cs:873
IEnumerable<DocumentRequestLog> results = unitOfWork.GetRepository<DocumentRequestLog>().Query()
                                          .Where(a => a.MyDocTemplate.Id == 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
-- (1) TemplateSearchService.cs:183 — GetRecentRequests
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  UPPER(DRL.CREATE_USER) = :currentUserIdUpper
  AND  DRL.CREATE_DATE > :threeMonthsAgo
ORDER BY DRL.CREATE_DATE DESC;

-- (2) TemplateSearchService.cs:873 — DeleteTemplateRequests (enumerated by foreach at L878)
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_TEMPLATE_SEQ_NUM = :templateId;

Translation notes: - (1) is materialized by .ToList() at L185; the subsequent requests.Where(...) at L190/L192/L193 and the Select/Distinct/Take at L196-199 operate on IEnumerable in memory (they read x.MyDocTemplate.BusinessGroup/Subgroup/EffectiveDate/ExpirationDate/IsEnabled via lazy-loaded DOCUMENT_TEMPLATE proxies, not as joins on this statement). - System.DateTime.Now.AddMonths(-3) is evaluated client-side and bound as a parameter. - (2) IsNull() at L875 does not enumerate; the foreach at L878 does. a.MyDocTemplate.Id is the reference identifier → FK column DOCUMENT_TEMPLATE_SEQ_NUM, no join.

Operations and processes

Sources

  • Services/SAIF.Services/Documents/TemplateSearchService.cs (L183, L873)