---
title: "SQL-228: Retrieve LegalNote by case ID, filters, or primary key"
summary: Query LEGAL.CASE_NOTE by case ID with optional date and category filters, or load/delete a single note by primary key.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-09-24
library: SAIF.Services
reference_type: package
---

# 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](../database-tables.md#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`.

```csharp
// 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.

```sql
-- 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
- LegalNotesService.DeleteNote — see [Operations and processes](../operations-processes.md)
- LegalNotesService.GetNoteData — see [Operations and processes](../operations-processes.md)
- LegalNotesService.GetNotes — see [Operations and processes](../operations-processes.md)

## Business rules
- [SVC-BR-LGL-070 Notes query requires a case id](../business-rules.md#svc-br-lgl-070-notes-query-requires-a-case-id)

## Sources
- `Services/SAIF.Services/Legal/Notes/LegalNotesService.cs (L67, L122, L190)`