Skip to content

SQL-320: Retrieve Task

This statement retrieves TASK records for CollectionsService, in two shapes: GetTaskData loads a single task by its sequence number, and GetTasks returns up to 300 collections-sourced tasks matching optional date-range and context filters, ordered by task date descending. The SQL is inferred from the NHibernate LINQ queries and the TaskMap mapping shown below — it is not literal SQL text in the source code.

Classification

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

Parameters

Parameter Direction Type Max length Description
:id Input long N/A Method argument of GetTaskData, matched against TASK.TASK_SEQ_NUM.
:FromDate Input DateTime N/A criteria.FromDate.Value.Date (evaluated client-side), lower bound on TASK_DATE.
:ThruDate Input DateTime N/A criteria.ThruDate.Value.Date (evaluated client-side), upper bound on TASK_DATE.
:CaseId Input long? N/A criteria.CaseId, matched against CONTEXT_NUM.
:ReferenceId Input string N/A criteria.ReferenceId.ToString() (evaluated client-side), matched against CONTEXT_KEY.

Tables

Table Access Columns used
TASK SELECT TASK_SEQ_NUM, UPDATE_SEQ_NUM, TASK_DATE, TASK_TEXT, TASK_USER, NOTEPAD_FLAG, DELETE_FLAG, IMAGE_SEQ_NUM, CONTEXT_CODE, CONTEXT_KEY, CONTEXT_CATEGORY, CONTEXT_TEXT, CONTEXT_DATE, CONTEXT_NUM, CREATE_DATE, CREATE_USER, UPDATE_DATE, UPDATE_USER, TASK_SECURITY, CLAIM_ID, POLICY_NUM, TASK_ACTION_CODE, TASK_CATEGORY_CODE, TASK_PRIORITY_CODE, TASK_SOURCE_CODE, CLAIM_TASK_GROUP_CODE, LEGAL_NOTE_CATEGORY_CODE

LINQ statement

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

// Services/SAIF.Services/Collections/CollectionsService.cs:1449
return UnitOfWork.GetRepository<TaskDataModel.Task>().Query()
   .SingleOrDefault(x => x.Id == id);

// Services/SAIF.Services/Collections/CollectionsService.cs:1456
var query = UnitOfWork.GetRepository<TaskDataModel.Task>().Query()
           .Where(n => n.TaskSource.Code == "COLL" && !n.IsDeleted);

if (criteria.FromDate.HasValue && criteria.FromDate != DateTime.MinValue)
    query = query.Where(x => x.TaskDate.Date >= criteria.FromDate.Value.Date);

if (criteria.ThruDate.HasValue && criteria.ThruDate != DateTime.MinValue)
    query = query.Where(x => x.TaskDate.Date <= criteria.ThruDate.Value.Date);

if (criteria.ReferenceId.HasValue && criteria.CaseId.HasValue)
    query = query.Where(x => x.ContextNumber == criteria.CaseId || x.ContextKey == criteria.ReferenceId.ToString());
else if (criteria.CaseId.HasValue)
    query = query.Where(x => x.ContextNumber == criteria.CaseId);
else if (criteria.ReferenceId.HasValue)
    query = query.Where(x => x.ContextKey == criteria.ReferenceId.ToString());

var taskData = query.OrderByDescending(x => x.TaskDate).Take(300).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.GetTaskData (line 1449)
SELECT
  T.TASK_SEQ_NUM,
  T.UPDATE_SEQ_NUM,
  T.TASK_DATE,
  T.TASK_TEXT,
  T.TASK_USER,
  T.NOTEPAD_FLAG,
  T.DELETE_FLAG,
  T.IMAGE_SEQ_NUM,
  T.CONTEXT_CODE,
  T.CONTEXT_KEY,
  T.CONTEXT_CATEGORY,
  T.CONTEXT_TEXT,
  T.CONTEXT_DATE,
  T.CONTEXT_NUM,
  T.CREATE_DATE,
  T.CREATE_USER,
  T.UPDATE_DATE,
  T.UPDATE_USER,
  T.TASK_SECURITY,
  T.CLAIM_ID,
  T.POLICY_NUM,
  T.TASK_ACTION_CODE,
  T.TASK_CATEGORY_CODE,
  T.TASK_PRIORITY_CODE,
  T.TASK_SOURCE_CODE,
  T.CLAIM_TASK_GROUP_CODE,
  T.LEGAL_NOTE_CATEGORY_CODE
FROM TASK T
WHERE T.TASK_SEQ_NUM = :id
-- NHibernate expects at most one row

-- CollectionsService.GetTasks (line 1456): composed query, materialized by ToList() at line 1472
SELECT *
FROM (
  SELECT
    T.TASK_SEQ_NUM,
    T.UPDATE_SEQ_NUM,
    T.TASK_DATE,
    T.TASK_TEXT,
    T.TASK_USER,
    T.NOTEPAD_FLAG,
    T.DELETE_FLAG,
    T.IMAGE_SEQ_NUM,
    T.CONTEXT_CODE,
    T.CONTEXT_KEY,
    T.CONTEXT_CATEGORY,
    T.CONTEXT_TEXT,
    T.CONTEXT_DATE,
    T.CONTEXT_NUM,
    T.CREATE_DATE,
    T.CREATE_USER,
    T.UPDATE_DATE,
    T.UPDATE_USER,
    T.TASK_SECURITY,
    T.CLAIM_ID,
    T.POLICY_NUM,
    T.TASK_ACTION_CODE,
    T.TASK_CATEGORY_CODE,
    T.TASK_PRIORITY_CODE,
    T.TASK_SOURCE_CODE,
    T.CLAIM_TASK_GROUP_CODE,
    T.LEGAL_NOTE_CATEGORY_CODE
  FROM TASK T
  WHERE T.TASK_SOURCE_CODE = 'COLL'
    AND NOT (T.DELETE_FLAG = 'Y')
    AND TRUNC(T.TASK_DATE) >= :FromDate -- applied only when criteria.FromDate has a value other than DateTime.MinValue
    AND TRUNC(T.TASK_DATE) <= :ThruDate -- applied only when criteria.ThruDate has a value other than DateTime.MinValue
    AND (T.CONTEXT_NUM = :CaseId OR T.CONTEXT_KEY = :ReferenceId) -- applied only when criteria.ReferenceId and criteria.CaseId both have values
    AND T.CONTEXT_NUM = :CaseId -- applied only when only criteria.CaseId has a value
    AND T.CONTEXT_KEY = :ReferenceId -- applied only when only criteria.ReferenceId has a value
  ORDER BY T.TASK_DATE DESC
)
WHERE ROWNUM <= 300

Translation notes:

  • Two call sites with different shapes share this SQL ID; both are shown with label comments.
  • x.TaskDate.Date (DateTime.Date) is not in the skill's operator table; translated to Oracle TRUNC(col) as the closest standard equivalent.
  • !n.IsDeleted -> NOT (T.DELETE_FLAG = 'Y') (YesNoType).
  • OrderByDescending(...).Take(300) -> Oracle10 ROWNUM <= 300 wrapper around the ordered query.
  • The three CONTEXT_* predicates come from an if / else if / else if chain (lines 1465-1470) - at most one of them is applied.
  • Everything after ToList() (Mapper, debtor-name lookup, lines 1474-1483) runs in memory.

Operations and processes

Sources

  • Services/SAIF.Services/Collections/CollectionsService.cs (L1449, L1456)