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

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.

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

Business rules¶

SD-BR-090 Superseded certificate holders excluded from search

Sources¶

Data/SAIF.Data/Queries/Policies/Certificates/CertificateHolderProvider.cs:35