---
title: "SQL-105: Retrieve Cancellation via GetPendingCancelDateForPolicy"
summary: Read operation against Cancellation via NHibernate ORM.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-09-24
library: SAIF.Services
reference_type: package
---

# SQL-105: Retrieve Cancellation via GetPendingCancelDateForPolicy

This ORM statement performs a read operation on `Cancellation` via `IUnitOfWork.GetRepository<T>()`, executed from `Services/SAIF.Services/Policies/Certificates/Information/CertificateInformationService.cs (L451)`.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-105 |
| **Operation type** | Read |
| **Execution method** | ORM (NHibernate LINQ) |
| **Database** | Oracle |
| **Schema** | [unqualified]** |
| **Command type** | N/A (ORM-generated) |
| **Business object** | `Cancellation` |
| **Criteria property** | N/A — ORM |
| **Lifecycle method** | `GetPendingCancelDateForPolicy` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| (none) | N/A | N/A | N/A | No inline filter — full-entity read (`Query().ToList()`/projection) or filters appended later |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [CANCELLATION](../database-tables.md#cancellation) | SELECT | `CANCELLATION_SEQ_NUM`, `POLICY_NUM`, `CANCEL_DATE` |

## LINQ statement

Source: `Services/SAIF.Services/Policies/Certificates/Information/CertificateInformationService.cs:450`

```csharp
// Services/SAIF.Services/Policies/Certificates/Information/CertificateInformationService.cs:450-451
            var cancellations =
                unitOfWork.GetRepository<Cancellation>().Query();

// Services/SAIF.Services/Policies/Certificates/Information/CertificateInformationService.cs:453
            var maxCancellation = cancellations.SingleOrDefault(c => c.Id == (cancellations.Where(x => x.PolicyNumber == policyNumber).Max(m => m.Id)));
```

## 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
-- Services/SAIF.Services/Policies/Certificates/Information/CertificateInformationService.cs:453
SELECT
  C.CANCELLATION_SEQ_NUM,
  C.POLICY_NUM,
  C.CANCEL_DATE
FROM CANCELLATION C
WHERE C.CANCELLATION_SEQ_NUM = (
    SELECT MAX(C2.CANCELLATION_SEQ_NUM)
    FROM CANCELLATION C2
    WHERE C2.POLICY_NUM = :policyNumber
  )
-- NHibernate expects at most one row
```

Translation notes:
- `cancellations` (L450-451) is an un-materialized `IQueryable`; the inner `cancellations.Where(...).Max(m => m.Id)` is part of the outer `SingleOrDefault` expression tree and is translated as a scalar subquery (one round trip). If an NHibernate version cannot translate the nested queryable it would instead execute the `MAX` first - same SQL, split into two statements.
- `expirationDate >= maxCancellation.CancelDate` (L454) runs in memory.

## Operations and processes
- CertificateInformationService.GetPendingCancelDateForPolicy — see [Operations and processes](../operations-processes.md)

## Sources
- `Services/SAIF.Services/Policies/Certificates/Information/CertificateInformationService.cs (L451)`