Skip to content

SQL-160: Retrieve DocumentNotification via DocumentNotificationQuery (property)

DocumentNotificationQuery exposes an IQueryable<DocumentNotification> over EXT_USER_DOC_NOTIFY; its only composing consumer looks up the notification row for a given agency and external-user pairing so the caller can decide whether to add or remove the recipient.

Classification

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

Parameters

Parameter Direction Type Max length Description
:agencyId Input long N/A x.AgencyId == agencyId → column AGENCY_SEQ_NUM
:userSeqNum Input long N/A x.ExtUserSeqNumber == userSeqNum → column EXT_USER_TYPE_SEQ_NUM

Tables

Table Access Columns used
EXT_USER_DOC_NOTIFY SELECT EXT_USER_DOC_NOTIFY_SEQ, EXT_USER_TYPE_SEQ_NUM, AGENCY_SEQ_NUM

LINQ statement

Source: Services/SAIF.Services/Agencies/AgencyService.cs:490. Composing consumer at Services/SAIF.Services/Agencies/AgencyService.cs:257.

// Services/SAIF.Services/Agencies/AgencyService.cs:490
get { return unitOfWork.GetRepository<agenciesDataModel.DocumentNotification>().Query(); }

// Services/SAIF.Services/Agencies/AgencyService.cs:257
var notification = DocumentNotificationQuery.FirstOrDefault(x => x.AgencyId == agencyId && x.ExtUserSeqNumber == userSeqNum);

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
-- Base: DocumentNotificationQuery (AgencyService.cs:490) — IQueryable only; no SQL until composed/enumerated
-- Consumer: AgencyService.cs:257 — DocumentNotificationQuery.FirstOrDefault(x => x.AgencyId == agencyId && x.ExtUserSeqNumber == userSeqNum)
SELECT * FROM (
    SELECT DN.EXT_USER_DOC_NOTIFY_SEQ,
       DN.EXT_USER_TYPE_SEQ_NUM,
       DN.AGENCY_SEQ_NUM
    FROM   EXT_USER_DOC_NOTIFY DN
    WHERE  DN.AGENCY_SEQ_NUM = :agencyId
      AND  DN.EXT_USER_TYPE_SEQ_NUM = :userSeqNum
) WHERE ROWNUM <= 1;

Translation notes: - L490 is a queryable-property definition (IQueryable<DocumentNotification>); the only composing consumer in AgencyService.cs is L257. - FirstOrDefault → Oracle10 dialect wraps the query in SELECT * FROM (...) WHERE ROWNUM <= 1. - DocumentRepository (L495) is used for Add/Delete after the lookup; those DML statements are not part of this read.

Operations and processes

Sources

  • Services/SAIF.Services/Agencies/AgencyService.cs (L490)