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.

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

-- 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
PdfTrackingService.LoadTrackingToPurge — see Operations and processes
PdfTrackingService.LoadTrackingsToPurge — see Operations and processes
PdfTrackingService.PurgeOrphanedFolders — see Operations and processes

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