Skip to content

SQL-233: Retrieve Note by case ID with date range, or by primary key

CollectionsService.GetNotes filters Note by case ID within a from/thru date window. GetdataNote loads a single Note by primary key.

Classification

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

Parameters

Parameter Direction Type Max length Description
:caseId Input long N/A statement (1), filters LEGAL_COLLECTION_SEQ_NUM
:fromDate Input DateTime N/A filter.FromDate
:thruDateWithMaxTime Input DateTime N/A (filter.ThruDate ?? MaxValue.Date) + 23:59:59, computed client-side at L754-755
:noteId Input long N/A statement (2), filters LEGAL_COLLECTION_NOTE_SEQ_NUM

Tables

Table Access Columns used
LEGAL_COLLECTION_NOTE SELECT all 7 mapped columns; filters LEGAL_COLLECTION_SEQ_NUM, CREATE_DATE, LEGAL_COLLECTION_NOTE_SEQ_NUM

LINQ statement

Source: Services/SAIF.Services/Collections/CollectionsService.cs:757, also :780.

// Services/SAIF.Services/Collections/CollectionsService.cs:757
var query = UnitOfWork.GetRepository<DataModel.Note>().Query()
    .Where(n => n.CaseId == caseId && n.CreateDate >= filter.FromDate && n.CreateDate <= filterThruDateWithMaxTime).ToList();

// Services/SAIF.Services/Collections/CollectionsService.cs:780
return UnitOfWork.GetRepository<DataModel.Note>().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) L757 — GetNotes
SELECT N.LEGAL_COLLECTION_NOTE_SEQ_NUM,
       N.LEGAL_COLLECTION_SEQ_NUM,
       N.TEXT,
       N.CREATE_DATE,
       N.CREATE_USER,
       N.UPDATE_DATE,
       N.UPDATE_USER
FROM   LEGAL_COLLECTION_NOTE N
WHERE  N.LEGAL_COLLECTION_SEQ_NUM = :caseId
  AND  N.CREATE_DATE >= :fromDate
  AND  N.CREATE_DATE <= :thruDateWithMaxTime

-- (2) L780 — GetdataNote
SELECT N.<all 7 mapped LEGAL_COLLECTION_NOTE columns, as in (1)>
FROM   LEGAL_COLLECTION_NOTE N
WHERE  N.LEGAL_COLLECTION_NOTE_SEQ_NUM = :noteId

Translation notes: - SingleOrDefault/Single add no row limit; NHibernate throws if more than one row is returned. - In (1), OrderByDescending(x => x.CreateDate) at L760 is applied in memory after .ToList(), so it is not SQL ORDER BY.

Operations and processes

Sources

  • Services/SAIF.Services/Collections/CollectionsService.cs (L757, L780)