Skip to content

SQL-230: Retrieve LegalNoteCategory list or by code

LegalTaskService.GetNoteCategories lists all LegalNoteCategory rows ordered by description. FillTaskData, UpdateTask, and CreateTaskNote each look up a single category by code, skipping the query entirely (via a C# ternary) when the source category reference is null.

Classification

Field Value
SQL ID SQL-230
Operation type Read
Execution method ORM (NHibernate LINQ)
Database Oracle
Schema [unqualified]**
Command type N/A (ORM-generated)
Business object LegalNoteCategory
Criteria property N/A — ORM
Lifecycle method CreateTaskNote, FillTaskData, GetNoteCategories, UpdateTask

Parameters

Parameter Direction Type Max length Description
:categoryCode Input string N/A task.NoteCategory.Code / taskCopy.NoteCategory.Code / taskData.LegalNoteCategory.Code, client-side

Tables

Table Access Columns used
NOTE_CATEGORY_CODE SELECT CODE, CODE_DESCR

LINQ statement

Source: Services/SAIF.Services/Legal/Tasks/LegalTaskService.cs:160, also :657, :729, :931.

// Services/SAIF.Services/Legal/Tasks/LegalTaskService.cs:160
var noteCategories = _unitOfWork.GetRepository<LegalNoteCategory>().Query().OrderBy(x => x.Description).ToList();

// Services/SAIF.Services/Legal/Tasks/LegalTaskService.cs:657
taskData.LegalNoteCategory = task.NoteCategory == null ? null : _unitOfWork.GetRepository<LegalNoteCategory>().Query().SingleOrDefault(x => x.Code == task.NoteCategory.Code);

// Services/SAIF.Services/Legal/Tasks/LegalTaskService.cs:729
taskData.LegalNoteCategory = taskCopy.NoteCategory == null ? null : _unitOfWork.GetRepository<LegalNoteCategory>().Query().SingleOrDefault(x => x.Code == taskCopy.NoteCategory.Code);

// Services/SAIF.Services/Legal/Tasks/LegalTaskService.cs:931
Category = _unitOfWork.GetRepository<LegalNoteCategory>()
    .Query()
    .SingleOrDefault(x => x.Code == taskData.LegalNoteCategory.Code),

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) L160 — GetNoteCategories
SELECT C.CODE,
       C.CODE_DESCR
FROM   NOTE_CATEGORY_CODE C
ORDER BY C.CODE_DESCR

-- (2) L657 FillTaskData, (3) L729 UpdateTask, (4) L931 CreateTaskNote — lookup by code
SELECT C.CODE, C.CODE_DESCR
FROM   NOTE_CATEGORY_CODE C
WHERE  C.CODE = :categoryCode

Translation notes: - SingleOrDefault/Single add no row limit; NHibernate throws if more than one row is returned. - In (2) and (3), a C# ternary skips the query when NoteCategory == null. - (1): the blank entry inserted at L161 is in memory only.

Operations and processes

Sources

  • Services/SAIF.Services/Legal/Tasks/LegalTaskService.cs (L160, L657, L729, L931)