---
title: "SQL-235: Retrieve OnBaseNotificationRuleMatrix rules"
summary: List all notification rule-matrix rows for in-memory projection, or filter by document type via a two-hop join.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-09-24
library: SAIF.Services
reference_type: package
---

# 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](../database-tables.md#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](../database-tables.md#ob_notification_matrix) | JOIN | OB_NOTIFICATION_MATRIX_SEQ_NUM, OB_DOC_TYPE_SEQ_NUM (statement 2 only) |
| [ob_doc_type](../database-tables.md#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`.

```csharp
// 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.

```sql
-- 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](../operations-processes.md)

## Sources
- `Services/SAIF.Services/Notifications/NotificationService.cs (L23, L36)`