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

# SQL-253: Retrieve PDFTracking

`PdfTrackingService` reads `PDFTracking` rows through five LINQ query shapes: lookup by file id (`GetTrackingInfo(Guid)`), lookup by numeric id (`GetTrackingInfo(long)`), purge-candidate enumeration (`LoadTrackingsToPurge`, optionally filtered by application error code), single purge-candidate lookup by file id (`LoadTrackingToPurge`), and an existence check by file id (`PurgeOrphanedFolders`). The entity's `ClassMap` lives outside this repository (in `SAIF.Framework.Data.NHibernate`); Legends resolved its backing table (`pdf_tracking`) and its referenced tables, but no documented source enumerates its column mappings, so the SELECT lists below cannot be reliably inferred.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-253 |
| **Operation type** | Read |
| **Execution method** | ORM (NHibernate LINQ) |
| **Database** | Oracle |
| **Schema** | [unqualified]** |
| **Command type** | N/A (ORM-generated) |
| **Business object** | `PdfTrackingService` |
| **Criteria property** | N/A — ORM |
| **Lifecycle method** | `GetTrackingInfo(Guid)`, `GetTrackingInfo(long)`, `LoadTrackingsToPurge`, `LoadTrackingToPurge`, `PurgeOrphanedFolders` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:fileId` | Input | string | N/A | `fileId.ToString().ToLower()` (GetTrackingInfo(Guid)); `fileId.ToString()` (LoadTrackingToPurge); `fileId.ToLower()` (PurgeOrphanedFolders) |
| `:id` | Input | long | N/A | The tracking record's numeric id (GetTrackingInfo(long)) |
| `:cutoff` | Input | DateTime | N/A | Purge cutoff date |
| `:cutoffMinus30` | Input | DateTime | N/A | `cutoff.AddDays(-30)` |
| `:Code` | Input | string | N/A | `appType.Code.ToLower()` — applied only when `appType != null` (LoadTrackingsToPurge) |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| `pdf_tracking`** | SELECT | `FileId**`, `Id**`, `PurgeDate**`, `CreateDate**`, `ApplicationError` FK** (column names unresolved); remaining columns unresolved** |
| `pdf_app_error_code`** | JOIN (only when `appType != null`) | `identifier**`, `Code**` (column names unresolved) |

## LINQ statement

Source: `Services/SAIF.Services/Utilities/Pdf/PdfTrackingService.cs:32`. The repository is obtained on the same line at each call site listed below; `LoadTrackingsToPurge` materializes at `PdfTrackingService.cs:114`.

```csharp
// Services/SAIF.Services/Utilities/Pdf/PdfTrackingService.cs:32-33
var repo = _uow.GetRepository<SAIF.Data.Model.PDF.PDFTracking>();
var record = repo.Query().FirstOrDefault(x => x.FileId.ToLower() == fileId.ToString().ToLower());

// Services/SAIF.Services/Utilities/Pdf/PdfTrackingService.cs:40-41
var repo = _uow.GetRepository<SAIF.Data.Model.PDF.PDFTracking>();
var record = repo.Query().FirstOrDefault(x => x.Id == id);

// Services/SAIF.Services/Utilities/Pdf/PdfTrackingService.cs:77-89
IQueryable<SAIF.Data.Model.PDF.PDFTracking> records = null;
var repo = _uow.GetRepository<SAIF.Data.Model.PDF.PDFTracking>();

if (appType != null)
{
    records = repo.Query().Where(x => ((x.PurgeDate > DateTime.MinValue && x.PurgeDate < cutoff) || (x.CreateDate.HasValue && x.CreateDate.Value < cutoff.AddDays(-30))) && x.ApplicationError.Code.ToLower() == appType.Code.ToLower());
}
else
{
    records = repo.Query().Where(x => ((x.PurgeDate > DateTime.MinValue && x.PurgeDate < cutoff) || (x.CreateDate.HasValue && x.CreateDate.Value < cutoff.AddDays(-30))));
}

return records;

// Services/SAIF.Services/Utilities/Pdf/PdfTrackingService.cs:94-96
var repo = _uow.GetRepository<SAIF.Data.Model.PDF.PDFTracking>();

return repo.Query().FirstOrDefault(x => ((x.PurgeDate > DateTime.MinValue && x.PurgeDate < cutoff) || (x.CreateDate.HasValue && x.CreateDate.Value < cutoff.AddDays(-30))) && x.FileId == fileId.ToString());

// Services/SAIF.Services/Utilities/Pdf/PdfTrackingService.cs:198-199
var repo = _uow.GetRepository<SAIF.Data.Model.PDF.PDFTracking>();
var hasData = repo.Query().Any(x => x.FileId.ToLower() == fileId.ToLower());

// Services/SAIF.Services/Utilities/Pdf/PdfTrackingService.cs:114
var recs = LoadTrackingsToPurge(cutoffDate, appType).ToArray();
```

## 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
-- GetTrackingInfo(Guid fileId) (L33)
SELECT *
FROM (
  SELECT
  /* column list not enumerable: PDFTrackingMap lives in SAIF.Framework.Data.NHibernate and its column mappings are not documented in Legends */
  FROM pdf_tracking PT
  WHERE LOWER(PT.FileId) = :fileId -- unresolved mapping: PDFTracking.FileId
)
WHERE ROWNUM <= 1

-- GetTrackingInfo(long id) (L41)
SELECT *
FROM (
  SELECT
  /* column list not enumerable: PDFTrackingMap lives in SAIF.Framework.Data.NHibernate and its column mappings are not documented in Legends */
  FROM pdf_tracking PT
  WHERE PT.Id = :id -- unresolved mapping: PDFTracking.Id
)
WHERE ROWNUM <= 1

-- LoadTrackingsToPurge (L82 when appType != null, L86 otherwise), materialized by ToArray() at L114
SELECT
  /* column list not enumerable: PDFTrackingMap lives in SAIF.Framework.Data.NHibernate and its column mappings are not documented in Legends */
FROM pdf_tracking PT
  INNER JOIN pdf_app_error_code PAEC -- applied only when appType != null; unresolved mapping: PDFTracking.ApplicationError
    ON PAEC.Id = PT.ApplicationError -- unresolved mapping: PDFApplicationErrorCode identifier / PDFTracking.ApplicationError FK
WHERE ((PT.PurgeDate > TO_DATE('0001-01-01', 'YYYY-MM-DD') -- unresolved mapping: PDFTracking.PurgeDate
    AND PT.PurgeDate < :cutoff)
  OR (PT.CreateDate IS NOT NULL -- unresolved mapping: PDFTracking.CreateDate
    AND PT.CreateDate < :cutoffMinus30))
  AND LOWER(PAEC.Code) = :Code -- applied only when appType != null; unresolved mapping: PDFApplicationErrorCode.Code

-- LoadTrackingToPurge (L96)
SELECT *
FROM (
  SELECT
  /* column list not enumerable: PDFTrackingMap lives in SAIF.Framework.Data.NHibernate and its column mappings are not documented in Legends */
  FROM pdf_tracking PT
  WHERE ((PT.PurgeDate > TO_DATE('0001-01-01', 'YYYY-MM-DD') -- unresolved mapping: PDFTracking.PurgeDate
      AND PT.PurgeDate < :cutoff)
    OR (PT.CreateDate IS NOT NULL -- unresolved mapping: PDFTracking.CreateDate
      AND PT.CreateDate < :cutoffMinus30))
    AND PT.FileId = :fileId -- unresolved mapping: PDFTracking.FileId
)
WHERE ROWNUM <= 1

-- PurgeOrphanedFolders (L199)
SELECT *
FROM (
  SELECT 1
  FROM pdf_tracking PT
  WHERE LOWER(PT.FileId) = :fileId -- unresolved mapping: PDFTracking.FileId
)
WHERE ROWNUM <= 1
```

Translation notes:
- **Unresolved mapping (columns).** Table `pdf_tracking` confirmed via Legends; column names are unresolved, so every column reference uses the C# property name with an `-- unresolved mapping` marker and the SELECT list is not fabricated. Partial translation.
- Expressions that do not reference the lambda parameter (for example `x.Trim().ToLower()` on a method argument, `ToString()`, `AddDays(...)`, `.Date` on a captured value) are evaluated client-side by NHibernate's partial evaluator and bound as a single parameter value. `cutoff.AddDays(-30)` is bound as `:cutoffMinus30`.
- `DateTime.MinValue` is inlined as the literal `0001-01-01`; `x.CreateDate.HasValue` → `IS NOT NULL`, `x.CreateDate.Value` → the column.
- `LoadTrackingsToPurge` returns `IQueryable`; the query executes at `.ToArray()` (`PdfTrackingService.cs:114`). The two branches (L82/L86) differ only by the join and the `Code` predicate, shown as conditional lines.
- `pdf_cache_status_code` and `pdf_tracking_code` are referenced by the `pdf_tracking` mapping (per Legends) but never navigated in these LINQ expressions, so they are not accessed and are excluded from Tables. `pdf_app_error_code` is listed as a conditional JOIN because `x.ApplicationError.Code` is navigated; if `Code` is the identifier of `PDFApplicationErrorCode`, the navigation short-circuits to the FK on `pdf_tracking` and the join (and this table) would drop out — this cannot be confirmed because the map is unresolved.
- Six call-site lines under one SQL ID; five query shapes shown.

## Operations and processes
- PdfTrackingService.GetTrackingInfo — see [Operations and processes](../operations-processes.md)
- PdfTrackingService.LoadTrackingToPurge — see [Operations and processes](../operations-processes.md)
- PdfTrackingService.LoadTrackingsToPurge — see [Operations and processes](../operations-processes.md)
- PdfTrackingService.PurgeOrphanedFolders — see [Operations and processes](../operations-processes.md)

> **Note:** Statements marked with `**` could not be confirmed; treat as unknown until verified.

## Sources
- `Services/SAIF.Services/Utilities/Pdf/PdfTrackingService.cs:32-33`
- `Services/SAIF.Services/Utilities/Pdf/PdfTrackingService.cs:40-41`
- `Services/SAIF.Services/Utilities/Pdf/PdfTrackingService.cs:77-89`
- `Services/SAIF.Services/Utilities/Pdf/PdfTrackingService.cs:94-96`
- `Services/SAIF.Services/Utilities/Pdf/PdfTrackingService.cs:198-199`