---
title: "SQL-053: CertificateHolderQueryProvider.GetCertificateHolders"
summary: Read operation for `CertificateHolderQueryProvider.GetCertificateHolders`
status: draft
owner: SAIF.Core documentation team
last_reviewed: 2026-09-24
library: SAIF.Data
reference_type: package
---

# SQL-053: CertificateHolderQueryProvider.GetCertificateHolders

This statement is a read operation invoked through ORM (NHibernate LINQ). Executed by `CertificateHolderQueryProvider.GetCertificateHolders`.

## Classification

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

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `policyNumber` | Input | long | N/A | Method argument of `CertificateHolderQueryProvider.GetCertificateHolders` bound into the ORM expression; NHibernate generates the bind variable at runtime |
| `includeExpired` | Input | bool | N/A | Method argument of `CertificateHolderQueryProvider.GetCertificateHolders` bound into the ORM expression; NHibernate generates the bind variable at runtime |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [CERTIFICATE_HOLDER](../database-tables.md#certificate_holder) | SELECT | all `CertificateHolder` columns (see SQL-051); predicates on POLICY_NUM, SUPERSEDED_FLAG, EXPIRED_FLAG, CERT_HOLD_NAME1, CERT_HOLD_NAME2, CERT_HOLD_CITY, CERT_HOLD_STATE, CERT_HOLD_ADDR1, CERT_HOLD_ADDR2, CERT_HOLD_ADDR3 |

## LINQ statement

Source: `Data/SAIF.Data/Queries/Policies/Certificates/CertificateHolderProvider.cs:33-45`. The repository is a class field initialised from `Factory.GetRepository<CertificateHolder>().Query()` at `Data/SAIF.Data/Queries/Policies/Certificates/CertificateHolderProvider.cs:16`.

```csharp
var searchWords = Regex.Split(searchTerm.ToLower(), @"[^\w']+");

return searchWords.Aggregate(repository, (current, word) => current.Where(c => c.PolicyNumber == policyNumber &&
                                                                               !c.IsSuperseded &&
                                                               (includeExpired || c.IsExpired == includeExpired) &&
                                                        ((c.Name1 ?? "") + "," +
                                                         (c.Name2 ?? "") + "," +
                                                         (c.City ?? "") + "," +
                                                         (c.State ?? "") + "," +
                                                         (c.AddressLine1 ?? "") + "," +
                                                         (c.AddressLine2 ?? "") + "," +
                                                         (c.AddressLine3 ?? "")).ToLower().Contains(word))
                                                        ).ToList();
```

## 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
-- Shown for one search word; the bracketed block repeats per word
SELECT
  CH.CERT_HOLD_SEQ_NUM,
  CH.POLICY_NUM,
  CH.CERT_HOLD_NAME1,
  CH.CERT_HOLD_NAME2,
  CH.EXPIRED_FLAG,
  CH.SUPERSEDED_FLAG,
  CH.CERT_HOLD_ADDR1,
  CH.CERT_HOLD_ADDR2,
  CH.CERT_HOLD_ADDR3,
  CH.CERT_HOLD_CITY,
  CH.CERT_HOLD_STATE,
  CH.CERT_HOLD_COUNTRY,
  CH.CERT_HOLD_ZIP,
  CH.CERT_HOLD_ZIP4,
  CH.FOREIGN_ADDRESS_FLAG,
  CH.CERT_HOLD_PHONE,
  CH.CERT_HOLD_FAX,
  CH.CERT_HOLD_EMAIL,
  CH.CREATE_USER,
  CH.CREATE_DATE,
  CH.UPDATE_USER,
  CH.UPDATE_DATE,
  CH.SUPERSEDED_BY_CERT_HOLD_SEQNUM
FROM CERTIFICATE_HOLDER CH
WHERE (CH.POLICY_NUM = :policyNumber
       AND CH.SUPERSEDED_FLAG = 'N'
       AND (:includeExpired = 1 OR CH.EXPIRED_FLAG = :includeExpiredYN)
       AND LOWER(COALESCE(CH.CERT_HOLD_NAME1, '') || ',' ||
                 COALESCE(CH.CERT_HOLD_NAME2, '') || ',' ||
                 COALESCE(CH.CERT_HOLD_CITY, '') || ',' ||
                 COALESCE(CH.CERT_HOLD_STATE, '') || ',' ||
                 COALESCE(CH.CERT_HOLD_ADDR1, '') || ',' ||
                 COALESCE(CH.CERT_HOLD_ADDR2, '') || ',' ||
                 COALESCE(CH.CERT_HOLD_ADDR3, '')) LIKE '%' || :word1 || '%')
  -- AND (<same block with :word2>) ... AND (<same block with :wordN>)  -- one block per element of searchWords
```

Translation notes:
- **Client-side word split:** `Regex.Split(searchTerm.ToLower(), …)` runs in memory, and the number of `Where` clauses equals the number of words. `Aggregate` chains them as ANDed predicates, so the statement shape is dynamic per call.
- **Repeated predicates:** the policy, superseded and expired predicates are repeated in every block; they are redundant but harmless.
- **Empty tokens:** a leading or trailing delimiter yields an empty string token, producing `LIKE '%%'`, which matches everything.
- **Oracle NULL/empty equivalence:** `''` is `NULL` in Oracle, but `||` treats `NULL` as empty, so the concatenation behaves as the C# `?? ""` intends.
- **Index use:** this is a single round trip at `ToList()`, but no index can be used on the concatenated expression.

## Operations and processes

- [Other query providers](../operations/other-query-providers.md)

## Business rules

- [SD-BR-090 Superseded certificate holders excluded from search](../business-rules.md#sd-br-090-superseded-certificate-holders-excluded-from-search)

## Sources
- `Data/SAIF.Data/Queries/Policies/Certificates/CertificateHolderProvider.cs:35`