Skip to content

SQL-229: Retrieve LegalNoteCategory by code, list, or code set

LegalNotesService reads LegalNoteCategory four ways: single-code lookups in UpdateNote and CreateNote (object initializer), a full ordered list in GetCategories, and a code-set filter in GetCategoryDesc.

Classification

Field Value
SQL ID SQL-229
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 CreateNote, GetCategories, GetCategoryDesc, UpdateNote

Parameters

Parameter Direction Type Max length Description
:categoryCode Input string N/A note.Category.Code, client-side (statements 1, 3)
:code0, :code1, ... Input string N/A expanded from categoryCodes (statement 4)

Tables

Table Access Columns used
NOTE_CATEGORY_CODE SELECT CODE, CODE_DESCR

LINQ statement

Source: Services/SAIF.Services/Legal/Notes/LegalNotesService.cs:154, also :197, :228, :303.

// Services/SAIF.Services/Legal/Notes/LegalNotesService.cs:154
noteData.Category = UnitOfWork.GetRepository<LegalData.LegalNoteCategory>()
    .Query().SingleOrDefault(x => x.Code == note.Category.Code);

// Services/SAIF.Services/Legal/Notes/LegalNotesService.cs:197
var query = UnitOfWork.GetRepository<LegalData.LegalNoteCategory>().Query().OrderBy(x => x.Description);

// Services/SAIF.Services/Legal/Notes/LegalNotesService.cs:228
Category = UnitOfWork.GetRepository<LegalData.LegalNoteCategory>()
        .Query().SingleOrDefault(x => x.Code == note.Category.Code),

// Services/SAIF.Services/Legal/Notes/LegalNotesService.cs:303
var catcodes = UnitOfWork.GetRepository<LegalData.LegalNoteCategory>()
    .Query().Where(x => categoryCodes.Contains(x.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) L154 UpdateNote, (3) L228 CreateNote — lookup by code
SELECT C.CODE,
       C.CODE_DESCR
FROM   NOTE_CATEGORY_CODE C
WHERE  C.CODE = :categoryCode

-- (2) L197 — GetCategories (deferred; executed by query.ToList() at L199)
SELECT C.CODE, C.CODE_DESCR
FROM   NOTE_CATEGORY_CODE C
ORDER BY C.CODE_DESCR

-- (4) L303 — GetCategoryDesc (deferred; executed by the foreach at L306)
SELECT C.CODE, C.CODE_DESCR
FROM   NOTE_CATEGORY_CODE C
WHERE  C.CODE IN (:code0, :code1, ...)

Translation notes: - SingleOrDefault/Single add no row limit; NHibernate throws if more than one row is returned. - (3) is an object-initializer member (L228-229).

Operations and processes

Sources

  • Services/SAIF.Services/Legal/Notes/LegalNotesService.cs (L154, L197, L228, L303)