SQL-235: Retrieve OnBaseNotificationRuleMatrix rules

NotificationService.GetNotificationMatrixRules() (no arguments) selects every OnBaseNotificationRuleMatrix row and projects each through a private ParseRuleMatrix method that NHibernate cannot translate, so the whole entity is selected and the projection runs in memory; the navigations it reads there (NotificationMatrix, DocType, DocSubType, NotificationRule) lazy-load in separate per-row statements — an N+1 pattern. The overload that takes docType/docSubtype instead filters by document type through a two-hop join (NotificationMatrix → DocType), because DocumentType is a non-identifier property two levels deep.

Classification

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

Parameters

Parameter	Direction	Type	Max length	Description
:docTypeUpper	Input	string	N/A	docType.ToUpper(), evaluated client-side (statement 2 only); statement (1) has no inline filter

Tables

Table	Access	Columns used
OB_NOTIFY_RULE_MATRX_X	SELECT	OB_NOTIFY_RULE_MATRX_X_SEQ_NUM, CREATE_DATE, CREATE_USER, UPDATE_DATE, UPDATE_USER, OB_NOTIFICATION_RULE_SEQ_NUM, OB_NOTIFICATION_MATRIX_SEQ_NUM
OB_NOTIFICATION_MATRIX	JOIN	OB_NOTIFICATION_MATRIX_SEQ_NUM, OB_DOC_TYPE_SEQ_NUM (statement 2 only)
ob_doc_type	JOIN	ob_doc_type_seq_num, ob_doc_type (statement 2 only)

In statement (1), .Select(x => ParseRuleMatrix(x)) calls a private C# method that NHibernate cannot translate, so the whole entity is selected and projected in memory; the navigations it reads (NotificationMatrix, DocType, DocSubType, NotificationRule; L57-71) lazy-load OB_NOTIFICATION_MATRIX, ob_doc_type, ob_doc_sub_type, and OB_NOTIFICATION_RULE in separate statements, and none of those tables are joined in (1). In statement (2), the DocSubType filter (L41-43) and ParseRuleMatrix (L45-46) run in memory; ob_doc_sub_type and OB_NOTIFICATION_RULE are lazy-loaded and not joined.

LINQ statement

Source: Services/SAIF.Services/Notifications/NotificationService.cs:23, also :36.

// Services/SAIF.Services/Notifications/NotificationService.cs:23
return repo
    .Query()
    .Select(x => ParseRuleMatrix(x))
    .ToArray();

// Services/SAIF.Services/Notifications/NotificationService.cs:36
var rules = repo
    .Query()
    .Where(x => x.NotificationMatrix.DocType.DocumentType.ToUpper() == docType.ToUpper())
    .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
-- (1) L23-26 — GetNotificationMatrixRules()
SELECT RM.OB_NOTIFY_RULE_MATRX_X_SEQ_NUM,
       RM.CREATE_DATE,
       RM.CREATE_USER,
       RM.UPDATE_DATE,
       RM.UPDATE_USER,
       RM.OB_NOTIFICATION_RULE_SEQ_NUM,
       RM.OB_NOTIFICATION_MATRIX_SEQ_NUM
FROM   OB_NOTIFY_RULE_MATRX_X RM

-- (2) L36-39 — GetNotificationMatrixRules(docType, docSubtype)
SELECT RM.<all 7 mapped OB_NOTIFY_RULE_MATRX_X columns, as in (1)>
FROM   OB_NOTIFY_RULE_MATRX_X RM
  INNER JOIN OB_NOTIFICATION_MATRIX NM ON NM.OB_NOTIFICATION_MATRIX_SEQ_NUM = RM.OB_NOTIFICATION_MATRIX_SEQ_NUM
  INNER JOIN ob_doc_type DT ON DT.ob_doc_type_seq_num = NM.OB_DOC_TYPE_SEQ_NUM
WHERE  UPPER(DT.ob_doc_type) = :docTypeUpper

Translation notes: - (2): DocumentType is a non-identifier property two levels deep, so both NotificationMatrix and DocType require joins (implicit inner joins). - Per-row lazy loads cause N+1 behavior in both variants — statement (1)'s entity-materializing SELECT is followed by separate per-row lazy-load statements for each navigation ParseRuleMatrix reads, and statement (2) similarly lazy-loads DocSubType and NotificationRule per row.

Operations and processes

• NotificationService.GetNotificationMatrixRules — see Operations and processes

Sources

• Services/SAIF.Services/Notifications/NotificationService.cs (L23, L36)