SQL-045: PolicyOutputDocumentRepository.CheckDocumentExist¶

This statement is a read operation invoked through ORM (NHibernate LINQ). Executed by PolicyOutputDocumentRepository.CheckDocumentExist.

Classification¶

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

Parameters¶

Parameter	Direction	Type	Max length	Description
`:relationshipPolicyNumber`	Input	long	N/A	`document.Policy.RelationshipPolicy.PolicyNumber` (client-side)
`:policyPeriodEffectiveDate`	Input	DateTime	N/A	`document.PolicyPeriodEffectiveDate`
`:documentTypeCode`	Input	string	N/A	method argument `documentTypeCode`
`:releaseDate`	Input	DateTime?	N/A	`document.ReleaseDate`
`:documentDate`	Input	DateTime	N/A	`document.OutputDocument.DocumentDate`

Tables¶

Table	Access	Columns used
policy_output_document	SELECT	`output_document_seq_num`, `document_status_code`, `policy_num`, `policy_period_eff_date`, `release_date`
output_document	JOIN (inner, Where navigation)	`output_document_seq_num`, `output_document_type_code`, `document_create_date`

LINQ statement¶

Source: Data/SAIF.Data.NHibernate/Repositories/OutputDocuments/PolicyOutputDocumentRepository.cs:124.

public bool CheckDocumentExist(PolicyOutputDocument document, string documentTypeCode)
{
    return Query().Released()
        .Any(x => x.Policy.PolicyNumber == document.Policy.RelationshipPolicy.PolicyNumber &&
                  x.PolicyPeriodEffectiveDate == document.PolicyPeriodEffectiveDate &&
                  x.OutputDocument.DocumentType.Code == documentTypeCode &&
                  x.ReleaseDate == document.ReleaseDate &&
                  x.OutputDocument.DocumentDate == document.OutputDocument.DocumentDate);
}

public static IQueryable<PolicyOutputDocument> Released(this IQueryable<PolicyOutputDocument> query)
{
    return query.Where(p => p.DocumentStatus.Code == DocumentStatusCode.ReleasedDocumentStatusCode);
}

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
SELECT *
FROM (
  SELECT 1
  FROM policy_output_document POD
    INNER JOIN output_document OD
      ON OD.output_document_seq_num = POD.output_document_seq_num
  WHERE POD.document_status_code = 'RELE'
    AND POD.policy_num = :relationshipPolicyNumber
    AND POD.policy_period_eff_date = :policyPeriodEffectiveDate
    AND OD.output_document_type_code = :documentTypeCode
    AND POD.release_date = :releaseDate
    AND OD.document_create_date = :documentDate
)
WHERE ROWNUM <= 1
-- Any(): true when a row is returned

Translation notes: - Every right-hand operand comes from the document argument, not the lambda parameter, so NHibernate evaluates each one in .NET and binds the result. Reading document.Policy.RelationshipPolicy / document.OutputDocument can initialize lazy proxies on the argument entity, which issues by-id selects outside this statement. A null RelationshipPolicy throws NullReferenceException before any SQL runs. - Query() (no eager fetch). x.OutputDocument.DocumentType.Code and x.OutputDocument.DocumentDate both need the output_document row (non-identifier navigation of the constrained one-to-one), so there is one inner join. The final .DocumentType.Code short-circuits to OD.output_document_type_code; the type-code table is never joined. - p.Policy.PolicyNumber and document.Policy.RelationshipPolicy.PolicyNumber are both identifiers, so they short-circuit to POD.policy_num and the in-memory argument value respectively; the POLICY table is never joined. - Any() on the Oracle10 dialect is shown as an existence probe limited to one row (ROWNUM <= 1). NHibernate's exact projection text for Any may differ (for example a CASE WHEN EXISTS form). The logic is the same. - If document.ReleaseDate is null, NHibernate LINQ renders x.ReleaseDate == null as POD.release_date IS NULL. - ReleasedDocumentStatusCode = 'RELE' is inlined as a literal; document_status_code is never joined.

Operations and processes¶

Policy output documents

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

Sources¶

Data/SAIF.Data.NHibernate/Repositories/OutputDocuments/PolicyOutputDocumentRepository.cs:124