Skip to content

SQL-321: Retrieve Task via RemoveExitingTasks

This statement retrieves TASK records created by the current user on the current date, used by GenerateTasksService.RemoveExitingTasks to find and remove tasks that already exist before regenerating them. The SQL is inferred from the NHibernate LINQ query and the TaskMap mapping shown below — it is not literal SQL text in the source code.

Classification

Field Value
SQL ID SQL-321
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 RemoveExitingTasks

Parameters

Parameter Direction Type Max length Description
:UserId Input string N/A ProfileService.GetCurrentUser().UserId, evaluated client-side by NHibernate's partial evaluator and bound as a parameter; matched against CREATE_USER.
:today Input DateTime N/A DateTime.Today, matched against CREATE_DATE.

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/GenerateTasksService.cs:64

// Services/SAIF.Services/Collections/GenerateTasksService.cs:64
IEnumerable<TaskDataModel.Task> exitstingTasks = UnitOfWork.GetRepository<TaskDataModel.Task>()
   .Query()
   .Where(x => x.CreateUser == ProfileService.GetCurrentUser().UserId
   && x.CreateDate == DateTime.Today);
controlLog.WriteTimedLine("Records Found: " + exitstingTasks.Count().ToString("####"));
UnitOfWork.Begin();
foreach (var taskData in exitstingTasks)

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
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.CREATE_USER = :UserId
  AND T.CREATE_DATE = :today

Translation notes:

  • The query is assigned to an IEnumerable<TaskDataModel.Task> variable, so exitstingTasks.Count() (line 68) is Enumerable.Count - it enumerates the query (executing this SELECT) and counts in memory; the foreach at line 70 enumerates it again, executing the same SELECT a second time. No COUNT(*) is issued.
  • DateTime.Today -> bind variable :today; NHibernate may evaluate it client-side or translate it to a server date function depending on version.
  • The loop marks each task deleted (lines 72-75) and UnitOfWork.Commit() (line 77) flushes the tracked changes as versioned UPDATE TASK statements; those are not fabricated here.

Operations and processes

Sources

  • Services/SAIF.Services/Collections/GenerateTasksService.cs (L64)