Skip to content

SQL-228: Retrieve LegalNote by case ID, filters, or primary key

LegalNotesService.GetNotes composes a LegalNote query filtered by case ID with optional from-date, thru-date, and category-code conditions appended based on the supplied criteria. GetNoteData and DeleteNote each load a single LegalNote by primary key; DeleteNote then issues a separate ORM DELETE.

Classification

Field Value
SQL ID SQL-228
Operation type Read
Execution method ORM (NHibernate LINQ)
Database Oracle
Schema LEGAL
Command type N/A (ORM-generated)
Business object LegalNote
Criteria property N/A — ORM
Lifecycle method DeleteNote, GetNoteData, GetNotes

Parameters

Parameter Direction Type Max length Description
:caseId Input long N/A criteria.CaseId
:fromDate Input DateTime? N/A applied only when FromDate has a value and is not MinValue
:thruDate Input DateTime? N/A applied only when ThruDate has a value and is not MinValue
:cat0, :cat1, ... Input string N/A expanded from criteria.CategoryCodes; applied only when non-empty and not containing "ALL"
:noteId Input long N/A primary-key lookup for GetNoteData / DeleteNote

Tables

Table Access Columns used
LEGAL.CASE_NOTE SELECT all 8 mapped columns; filters LEGAL_CASE_SEQ_NUM, CREATE_DATE, NOTE_CATEGORY_CODE, CASE_NOTE_SEQ_NUM

LEGAL.LEGAL_CASE and NOTE_CATEGORY_CODE are not joined. x.Case.Id and x.Category.Code are FK identifiers on CASE_NOTE (LEGAL_CASE_SEQ_NUM, NOTE_CATEGORY_CODE). The Delete(note) at L191 is a separate ORM DELETE.

LINQ statement

Source: Services/SAIF.Services/Legal/Notes/LegalNotesService.cs:67, also :71, :74, :78, :122, :190.

// Services/SAIF.Services/Legal/Notes/LegalNotesService.cs:67
var query = UnitOfWork.GetRepository<LegalData.LegalNote>().Query()
    .Where(x => x.Case.Id == criteria.CaseId);

// Services/SAIF.Services/Legal/Notes/LegalNotesService.cs:71
query = query.Where(x => x.CreateDate.Date >= criteria.FromDate);

// Services/SAIF.Services/Legal/Notes/LegalNotesService.cs:74
query = query.Where(x => x.CreateDate.Date <= criteria.ThruDate);

// Services/SAIF.Services/Legal/Notes/LegalNotesService.cs:78
query = query.Where(x => criteria.CategoryCodes.Contains(x.Category.Code));

// Services/SAIF.Services/Legal/Notes/LegalNotesService.cs:122
var note = UnitOfWork.GetRepository<LegalData.LegalNote>().Query()
            .SingleOrDefault(x => x.Id == noteId);

// Services/SAIF.Services/Legal/Notes/LegalNotesService.cs:190
var note = UnitOfWork.GetRepository<LegalData.LegalNote>().Query().SingleOrDefault(x => x.Id == noteId);

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) L67-81 — GetNotes (composed; conditional predicates marked)
SELECT N.CASE_NOTE_SEQ_NUM,
       N.TEXT,
       N.CREATE_USER,
       N.CREATE_DATE,
       N.UPDATE_USER,
       N.UPDATE_DATE,
       N.NOTE_CATEGORY_CODE,
       N.LEGAL_CASE_SEQ_NUM
FROM   LEGAL.CASE_NOTE N
WHERE  N.LEGAL_CASE_SEQ_NUM = :caseId
  AND  TRUNC(N.CREATE_DATE) >= :fromDate            -- L71: only if FromDate has value and <> MinValue
  AND  TRUNC(N.CREATE_DATE) <= :thruDate             -- L74: only if ThruDate has value and <> MinValue
  AND  N.NOTE_CATEGORY_CODE IN (:cat0, :cat1, ...)   -- L78: only if CategoryCodes non-empty and not containing "ALL"

-- (2) L122 GetNoteData, (3) L190 DeleteNote
SELECT N.<all 8 mapped LEGAL.CASE_NOTE columns, as in (1)>
FROM   LEGAL.CASE_NOTE N
WHERE  N.CASE_NOTE_SEQ_NUM = :noteId

Translation notes: - SingleOrDefault/Single add no row limit; NHibernate throws if more than one row is returned. - x.CreateDate.Date → TRUNC(CREATE_DATE) (NHibernate Oracle dialect maps .Date). - The conditions at L70/L73 use the non-short-circuit &; both operands are evaluated client-side. - No ORDER BY is applied in SQL.

Operations and processes

Business rules

Sources

  • Services/SAIF.Services/Legal/Notes/LegalNotesService.cs (L67, L122, L190)